MySQL Backup Recovery Summary

MySQL backup (mysqldump)

mysqldump
SQL-level backup mechanism is used to export data tables into SQL script files, which is suitable for upgrading between different MySQL versions. This is also the most common backup method.
Let's talk about it now.mysqldump
Some of the main parameters:

• -- Compatible = Name
  

  It tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The value can beansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options
  And so on. Use commas to separate them. Of course, it does not guarantee full compatibility, but is as compatible as possible.
  

• -- Complete-insert,-C
  

  The exported data is complete with the field name.INSERT
  Method, that is, to write all the values in a row. This can improve the insert efficiency, but maymax_allowed_packet
  The insertion fails due to the influence of parameters. 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 defaultlatin1
  If the character set is used, this option must be specified during the export. Otherwise, garbled characters will occur after the data is imported again.

• -- Disable-keys
  

  Tellmysqldump
  InINSERT
  The beginning and end of the statement are added./*!40000 ALTER TABLE table DISABLE KEYS */;
  And/*!40000 ALTER TABLE table ENABLE KEYS */;
  Statement, which can greatly improve the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicableMyISAM
  Table.

• -- Extended-insert = true | false
  

  By default,mysqldump
  Enable--complete-insert
  If you do not want to use it, use this option to set its valuefalse
  You can.
  

• -- Hex-blob
  

  Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include:BINARY、VARBINARY、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 is automatically disabled.--single-transaction
  And--lock-tables
  .

• -- Lock-tables
  

  It and--lock-all-tables
  Similarly, it is used to lock the currently exported data table, rather than locking the tables in all databases at once. This option applies onlyMyISAM
  Table.Innodb
  Table can be used --single-transaction
  .

• -- No-create-Info,-T
  

  Only export data without addingCREATE TABLE
  Statement.

• -- No-data,-d
  

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

• -- OPT
  

  This is just a quick option, equivalent to adding--add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset
  . This option enablesmysqldump
  Export data quickly, and export data can be quickly imported back. This option is enabled by default, but can be used--skip-opt
  Disable. Note: If you runmysqldump
  Not Specified--quick
  Or--opt
  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 forcesmysqldump
  Retrieve records from the Server query directly, instead of getting all records, and then cache them to the memory.

• -- Routines,-R
  

  Export stored procedures and user-defined functions.

• -- Single-transaction
  

  This option submitsBEGIN
  SQL statement,BEGIN
  It does not block any applications and ensures Database Consistency during export. It only applies to transaction tables, suchInnoDB
  AndBDB
  .
  This option and--lock-tables
  The options are mutually exclusive, becauseLOCK TABLES
  It causes any pending transactions to be committed implicitly.
  To export a large table, use--quick
  .
  

• -- Triggers
  

  Export the trigger at the same time. This option is enabled by default.--skip-triggers
  Disable it.

BackupMyISAM
Common table parameters:

mysqldump -u userName -p --default-character-set=utf8 --opt --extended-insert=false 
--triggers -R --hex-blob -x db_name > db_name.sql

BackupInnodb
Table:

mysqldump -u userName -p --default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction db_name > db_name.sql

Online backup

Available--master-data
Parameters are as follows:

mysqldump -u userName -p --default-character-set=utf8 --opt --master-data=1 
--single-transaction --flush-logs db_name > db_name.sql

It only requests the lock table at the beginning, refresh the BINLOG, and then add it to the exported file.CHANGE MASTER
Statement to specify the BINLOG location of the current backup. If you want to restore the file to the slave, you can use this method.

 

 

Restore

MySQL command:

Mysql-u username-P db_name <db_name. SQL

Source command:
Source db_name. SQL;
Log on to MySQL and select a database before using this command.