MySQL backup and recovery mechanism

This article mainly discusses the actual operation scheme of the MySQL backup and recovery mechanism, and how to correctly maintain the data table in actual operations. The two most important types of tables are: myISAM and Innodb. MySQL version 5.0.22 is designed in the following articles. Currently, MySQL supports the following free backup tools: mysqldump and mysqlhotco.

This article mainly discusses the actual operation scheme of the MySQL backup and recovery mechanism, and how to correctly maintain the data table in actual operations. The two most important types of tables are: myISAM and Innodb. MySQL version 5.0.22 is designed in the following articles. Currently, MySQL supports the following free backup tools: mysqldump and mysqlhotco.

This article mainly discusses the actual operation scheme of the MySQL backup and recovery mechanism, and how to correctly maintain the data table in actual operations. The two most important types of tables are: myISAM and Innodb. MySQL version 5.0.22 is designed in the following articles.

Currently, MySQL supports the following free BACKUP tools: mysqldump and mysqlhotcopy. You can also use SQL syntax to back up: BACKUP TABLE or SELECT INTO OUTFILE, or back up binary logs (binlog ), you can also directly copy data files and related configuration files.

MyISAM tables are saved as files, so they are relatively easy to back up. The methods mentioned above can be used. All Innodb tables are stored in the same data file ibdata1 (multiple files or independent tablespace files), which is relatively difficult for MySQL backup, the free solution can be copying data files, backing up binlogs, or using mysqldump.

1. mysqldump

1.1 backup

Mysqldump adopts the SQL-level MySQL backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method.
Here are some main parameters of mysqldump:

 
 

  
  
compatible=name 
 
 

It tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.

 
 

  
  
complete-insert，-c 
 
 

The exported data adopts the complete INSERT method containing the field name, that is, all values are written in one row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.

 
 

  
  
default-character-set=charset 
 
 

Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.

 
 

  
  
disable-keys 
 
 

Tell mysql to add/* at the beginning and end of the INSERT statement for backup /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.

 
 

  
  
extended-insert = true|false 
 
 

By default, mysqldump enables the complete-insert mode, so if you do not want to use it, use this option to set its value to false.

 
 

  
  
hex-blob 
 
 

Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.

 
 

  
  
lock-all-tables，-x 
 
 

Before starting the export, submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the single-transaction and lock-tables options are automatically disabled.

 
 

  
  
lock-tables 
 
 

It is similar to lock-all-tables, but instead of locking all tables in the database. This option is only applicable to MyISAM tables. For Innodb tables, you can use the single-transaction option.

 
 

  
  
no-create-info，-t 
 
 

Only export data without adding the create table statement.

 
 

  
  
no-data，-d 
 
 

Only the database table structure is exported without exporting any data.

 
 

  
  
opt 
 
 

This is just a shortcut option. It is 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 quickly and export data back quickly.

This option is enabled by default, but can be disabled with skip-opt. Note: If the quick or opt option is not specified for running mysql backup, the entire result set is stored in the memory. If you export a large database, problems may occur.

 
 

  
  
quick，-q 
 
 

This option is useful when exporting large tables. It forces mysqldump to retrieve records from the Server query and output them directly instead of caching all records into the memory.

 
 

  
  
routines，-R 
 
 

Export stored procedures and custom