mysql备份和恢复MySQLbackupandrecovery.docx

1、mysql备份和恢复MySQLbackupandrecoverymysql 备份和恢复（MySQL backup and recovery）mysql 备份和恢复(MySQL backup and recovery) MySQL backup and recovery This article discusses the backup and recovery mechanisms for MySQL and how to maintain data tables, including the two main table types: MyISAM and Innodb, and the M

2、ySQL version of the article designed for 5.0.22. Free backup tools currently supported by MySQL: mysqldump, mysqlhotcopy, also can make a backup using SQL syntax: BACKUP TABLE or SELECT INTO OUTFILE, or binary log backup (binlog), can also be directly copy the data files and related configuration fi

3、les. The MyISAM table is saved in a file form, so its relatively easy to backup, and several of the methods mentioned above can be used. Innodb all the tables are stored in the same data in the ibdata1 file (possibly multiple files, or file table space independent), relatively good solutions can be

4、free backup, copy a data file, backup binlog, or mysqldump. 1, mysqldump 1.1 backup Mysqldump uses the SQL level backup mechanism, which leads the data table into SQL script file, which is relatively suitable for upgrading between different versions of MySQL, and this is the most commonly used backu

5、p method. Now lets talk about some of the main parameters of mysqldump: -compatible=name, it tells mysqldump that the exported data will be compatible with either the database or the older version of the MySQL server. Values can be ANSI, mysql323, mysql40, PostgreSQL, Oracle, MSSQL, DB2, maxdb, no_k

6、ey_options, no_tables_options, no_field_options, etc. several values should be used and separated by commas. Of course, it is not guaranteed to be fully compatible, but compatible as much as possible. The data exported by -complete-insert and -c uses a full INSERT that contains field names, that is,

7、 write all values in one line. Doing so improves the insertion efficiency, but may be affected by the max_allowed_packet parameter causing the insertion to fail. Therefore, you need to be careful with this parameter, at least I dont recommend it. -default-character-set=charset specifies the export d

8、ata with what character set, if the data table is not the default Latin1 character set, then export this option must be specified, otherwise again import data will produce garbled. -disable-keys told mysqldump at the beginning and end of the INSERT statement added 40000 ALTER TABLE table DISABLE / *

9、! * / / * KEYS; and 40000 ALTER TABLE table ENABLE KEYS! * /; this statement can greatly improve the speed of the insert statement, because it is in the insert all the data after the reconstruction of the index. This option is only suitable for MyISAM tables. -extended-insert = true|false, by defaul

10、t, mysqldump opens -complete-insert mode, so if you dont want to use it, use this option and set it to false. -hex-blob exports binary string fields using the sixteen decimal format. If you have binary data, you must use this option. The field types that affect are BINARY, VARBINARY, and BLOB. -lock

11、-all-tables, -x, before you start exporting, submit requests to lock all tables in all databases to ensure data consistency. This is a global read lock, and automatically closes the -single-transaction and -lock-tables options. -lock-tables is similar to -lock-all-tables, but locks the currently exp

12、orted data table instead of locking all the tables under the library at once. This option applies only to the MyISAM table, and if it is an Innodb table, you can use the -single-transaction option. -no-create-info, -t only exports data without adding the CREATE TABLE statement. -no-data, -d does not

13、 export any data, only export the database table structure. -opt, this is just a shortcut option, equivalent to adding the -add-drop-tables -add-locking -create-option -disable-keys -extended-insert -lock-tables -quick -set-charset option at the same time. This option allows mysqldump to export data

14、 quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled by -skip-opt. Note that if you run mysqldump without specifying the -quick or -opt option, you will put the entire result set in memory. Problems can arise if you export large databases.

15、 -quick, -q this option is useful when exporting large tables, which force mysqldump to retrieve records directly from the server query, and cache them directly into memory without obtaining all records. -routines, -R export stored procedures, and custom functions. -single-transaction this option pr

16、ovides a BEGIN SQL statement before exporting data, and BEGIN does not block any application and guarantees the consistency of the database when exporting. It applies only to transaction tables, such as InnoDB and BDB. This option and the -lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be implicitly committed. If you want to export large tables, you should comb