Mysql Study Notes sixteen (database maintenance), mysql Study Notes

Mysql Study Notes sixteen (database maintenance), mysql Study Notes

Data backup data loss cause: storage media applaud, user operation error (accidental deletion of the entire database), server paralysis 1. File Replication needs to stop the server service first, before stopping, execute flushtables to write all the data to the data file. After the data file is stopped, it is only suitable for the MyISAM storage engine to copy the data file to other places, not suitable for other engines 2. mysqldump stores the table structure and data content containing data in the corresponding text file. During Specific execution, first check the structure of the backup data table and generate the create statement in the corresponding text file. Then, check the data content and generate the insert into statement in the corresponding text file. To restore a database in the future, you only need to execute the create and insert into methods in the text file: Back up a database named mysqldump-u username-p dname table1, table2,...> backupname. if SQL does not specify a table, the entire database will be backed up ">" with spaces on both sides. Example: C: \ Users \ 208-1> mysqldump-u root-p123456 leetcode t_scores t_employee> D: \ AppServ \ MySQL \ backupleetcode. SQL C: \ Users \ 208-1> mysqldump-u root-p123456 leetcode> D: \ AppServ \ MySQL \ backupleetcode1. SQL mysqldump cannot be followed by mysql>, back up multiple databases in the dos window: mysqldump-u username-p -- databases dbname1 dbname2...> backupname. SQL databases cannot be separated by commas (,) to back up all databases. mysqldump-u username-p -- all-databases> backupnam E. SQL all-databases is connected together. Data restoration 1. Copy the backup file to restore the database. This method must ensure that the master version numbers of the two mysql databases are the same, because only the master version numbers are the same, only when the file types of the two mysql databases are the same and the version numbers are valid for the MyISAM tables, MySQL 5.5.21 and MySQL 5.5.01 are the same, the first digit is the major version number 2, mysql-u username-p [dbname] <backup. if the SQL statement specifies a dbname, the tables in the database will be restored. If this parameter is not specified, all databases in the backup file will be restored and exported through the import and export operations of tables in the database, you can easily move data export between the mysql database server and other database servers (SQL server and Oracle: copy data from mysql Data Table to Text File Import load data from text file to mysql database table 1. select... into outfile select [filename] from t Able_name [where condition] into outfile 'filename' [option]: Common query statement (query data to be exported) and export position option value: fileds terminated by "string" is used to set the delimiter string object (string) of a field. The default Delimiter is fields enclosed by "char", which is used to set the character symbols that include the field values, fields optionally enclosed by 'Char 'is not used by default to include character symbols for fields such as char, varchar, and text, by default, fieles escaped by 'Char 'is not used to set character symbols for escape characters. by default, "\" character lines starting by 'Char' is used to set the character symbols starting with each line, by default, lines terminated is not used. By 'string', you can set the end character of each line. The default value is '\ n' 2. mysqldump-u root-p-T file_directory dbname tablename [option] option: -- fileds-terminated-by = string -- fileds-enclosed-by = char -- fileds-optionally-enclosed-by = char -- lines-terminated-by = string import 1, load data infile load data [local] infile filename into table table_name [option] local: specify to search for a text file on the local computer; filename: used to specify the path and name of the text file; tablename: used to specify the table name oprio The value of n is fileds terminated by "string", which is used to set the delimiter string object (string) of the field. The default value is fields enclosed by "char", which is used to set the character symbols that include the field value, fields optionally enclosed by 'Char 'is not used by default to include character symbols for fields such as char, varchar, and text, by default, fieles escaped by 'Char 'is not used to set character symbols for escape characters. by default, "\" character lines starting by 'Char' is used to set the character symbols starting with each line, by default, lines terminated by 'string' is not used to set the string symbol ending in each line. by default, '\ n' ignore n lines are used to ignore the first n rows of records (Field List) of the file) load the record se according to the field and sequence in the Field List T column = expr is used to set column conversion conditions, that is, the specified column is converted and loaded. 2. The values of mysqlimport-u root-p [-- local] dbname file_name [oiption] option are as follows: -- fileds-terminated-by = string -- fileds-enclosed-by = char -- fileds-optionally-enclosed-by = char -- lines-terminated-by = string -- ignrs-lines = n note: command does not specify which table to import. Search for a table by file name according to the meaning in the book, and import the table. Database migration 1. Migration between mysql databases of the same version mysqldump-h hostname-u root-password = password1 -- all-databases | mysql-h hostname2-u root-password = password2 backup and Restore operations at the same time. For migration between mysql databases of the same version, use mysqldump for backup, and then use the mysql command to restore the backup file to the new mysql database. 2. Migration between mysql Databases of different versions-> higher versions are the easiest way to achieve migration, because the high version is compatible with the low version, MyISAM tables are directly copied using files, or mysqlhotcopy uses mysqldump backup for InnoDB tables, use the mysql command to restore 3. migrate mysql between different databases-> SQL server: migrate mysql through MyODBC-> oracle: first use the mysqldump command to export the SQL file and manually modify the create statement in SQL