MySQL Learning note 16 (Database maintenance)

Data backup data loss reason: storage media applause, user operation error (delete the entire database by mistake), the server completely paralyzed 1, the file replication needs to stop the Server service, before stopping, execution Flushtables writes all data to the data file, stops, copies the data file to the other The place is only suitable for MyISAM storage engines, not suitable for other engines. 2, mysqldump mysqldump the table structure and data contents containing the data are saved in the corresponding text file. When executing, first check the table structure of the backup data and generate the CREATE statement in the corresponding text file. Then examine the data contents and generate an INSERT INTO statement in the corresponding text file. In the future, when a restore is required, you only need to perform create and insert into three forms in a text file: Back up a database mysqldump-u username-p dname tabl E1,table2,... >backupname.sql do not specify table will back up the entire database back up multiple databases Mysqldump-u username -P--databases dbname1,dbname2,... >backupname.sql back up all databases Mysqldump-u username-p--all-dat Abases>backupname.sql data restore 1, copy backup file Restore Database This method must ensure that the major version number of the two MySQL database is consistent, because only the major version number is the same, to ensure that two MySQL database file type is the same only Valid major version numbers for tables of type MyISAM, MySQL 5.5.21 and MySQL 5.5.01 The major version number is the same, the first number is the major version number 2, mysqldump mysqldump-u username-p [db Name]<backup.sql specify dbname, the table under the database is restored, not specified, and all the databases in the backup file are exported through the import and export operations of the tables in the database, and the MySQL database server can beEasy mobile data export between database servers (SQL Server, Oracle): Copying data from the MySQL data table into a text file import load data from a text file into MySQL database table 1, Select...into outfile SELECT [FileName] FROM table_name [where condition] to outfile ' filename ' [option] two parts: Normal query statement (query to Exported data) and export location option: Fileds terminated by "string" is used to set the field's Delimiter string object (String), which defaults to a tab fie LDS enclosed by "char" is used to set the character symbol that encloses the field value, and the default does not use any symbols fields optionally enclosed by ' char ' are used to enclose Char,varchar and text, such as the character symbol of a field, by default does not use any symbol fieles escaped by ' char ' to set the character symbol of the escape character, by default using the "\" character lines starting by ' C        Har ' Sets the character symbol at the beginning of each line, and by default does not use any symbol lines terminated by ' string ' sets the string symbol at the end of each line, by default using ' \ n ' 2, mysqldump Mysqldump-u root-p-T file_directory dbname tablename[option] option value:--FILEDS-TERMINATED-BY=STR ing--fileds-enclosed-by=char--fileds-optionally-enclosed-by=char--lines-terminated-by= String import 1, load datainfile load data[local] infile filename into table table_name[option] Local: Specifies to find a text file on the local computer; filename : Used to specify the path and name of the text file; tablename: Used to specify the name of the table oprion the value fileds terminated by "string" to set the field's Delimiter string object (string), the default is a tab field enclosed by "char" to set the character symbol that encloses the values of the fields, and the default does not use any of the symbols for the optionally enclosed            By ' char ' is used to enclose the character symbol for fields such as Char,varchar and text, and by default does not use any symbol fieles escaped by ' char ' to set the character symbol of the escape character, using the "\" character by default Lines starting by ' char ' sets the character symbol at the beginning of each line, and by default does not use any symbols lines terminated by ' string ' to set the end of each line of string characters            The first n rows of records (field list) implemented by default using ' \ n ' ignore n lines to load records based on fields and order in field list Set COLUMN=EXPR is used to set the transformation condition of a column, that is, the specified column will be loaded after the corresponding conversion 2, Mysqlimport mysqlimport-u root-p [--local] Dbn            Ame file_name[oiption] option value:--fileds-terminated-by=string--fileds-enclosed-by=char --fileds-optionaLly-enclosed-by=char--lines-terminated-by=string--ignrs-lines=n Note: The command does not specify which table to import into. According to the meaning of the book, look for the table by file name, and then import it. Database migration 1, migration between MySQL databases in the same version mysqldump-h hostname-u root-password=password1--all-databases |mysql-h H        Ostname2-u Root-password=password2 Backup and restore operations simultaneously.               For migrations between MySQL databases in the same version, first use mysqldump for backup, and then use the MySQL command to restore the backup files to the new MySQL database.        2, the migration between different versions of MySQL database between the lower version and the higher version is most easy to implement, because the high version compatible with the lower version of the MyISAM table uses the way the file is copied directly or Mysqlhotcopy For InnoDB tables using mysqldump backup, use the MySQL command to restore 3, migrate between different databases Mysql->sql server: Migrate Mysql-&gt with MYODBC Oracle: Manually modify the Create statement in SQL by first exporting the SQL file via the mysqldump command

MySQL Learning note 16 (Database maintenance)