Tool introduction

"mysqldump" is a self-contained client tool of MySQL, and it is mainly used in the logic backup of MySQL, meeting the needs for database/table/row-level data backup and database table structure backup. Application scenarios include but are not limited to the following cases:

• Carry out data backup before executing SQL of DML/DDL type (used in meeting rollback needs)
• Data migration between databases of different vendors.
• Data migration during database version upgrading
• Export some data into a test environment for feature and performance tests.

Common usages

Important parameters

• --add-drop-table

  [Note]Be enabled by default. The SQL file exported from mysqldump contains DROP statement: DROP TABLE IF EXISTS 'XXX';

  [Notice]Avoid "drop" of the original datasheet. Lose the data when importing the SQL file directly into an online instance. Avoid exporting a DROP statement using the parameter "--skip-add-drop-table".

• --add-locks

  [Note]Be enabled by default, add "LOCK TABLES 'XXX' WRITE" and "UNLOCK TABLES" respectively before and after the "INSERT" statement.

  [Notice]Avoid locking the table and blocking write-in of other connections when importing SQL into an instance. Resolve this problem using the parameter "--skip-add-locks".

• --lock-tables

  [Note]Be enabled by default. Add a read lock (LOCK TABLES xxx READ) to tables in the specified database for blocking write-in and ensuring consistency of backup sites.

  [Notice]As for the InnoDB engine table, you are advised to use "-single-transaction" rather than "--lock-tables". You can avoid this problem using the parameter "--skip-lock-tables".

• --lock-tables

  [Note]disabled by default, lock all database tables by adding read lock ahead of the backup cycle.

  [Notice]this parameter automatically disables the parameter options "--single-transaction" and "--lock-tables".

• --single-transaction

  [Note]"mysqldump" automatically enables a transaction for "REPEATABLE READ", and then backs up the consistent data snapshot. It is only applicable to transaction tables like a table of the InnoDB engine.

  [Notice]If the database only has tables of InnoDB engine, recommend you to enable this parameter when exporting the "mysqldump". The specific execution process is as follows. Before the back-up, set the transaction isolation level as "REPEATABLE READ", and send the server "START TRANSACTION" statement. In the MySQL's general log, you can see the following contents:

  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
  START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
  SAVEPOINT SP
  ... ...
  ROLLBACK TO SAVEPOINT SP
  RELEASE SAVEPOINT SP

• --set-gtid-purged

  [Note]Be enabled by default. Check if the exported SQL file contains "GTID_PURGED" information.

  When "ON/AUTO", exported SQL file contains "SET @@GLOBAL.GTID_PURGED='xxx:1-xxx';"

  When "OFF", exported SQL file does not contain "SET @@GLOBAL.GTID_PURGED='xxx:1-xxx';"

  [Notice]You need to set this parameter when enabling the GTID instance.

• --master-data

  [Note]Be used for recording the master database's "binlog" site when the master database backup is ongoing.

  Value 1: In the exported SQL file there is no comment "CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX";

  Value 2: In the exported SQL file there is a comment "CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX";

  [Notice]You need to set this parameter when "binlog" is enabled, but the "GTID" is disabled.

• --dump-slave

  [Note]Be used for the backup on the slave, and record the corresponding master's "binlog" site at the backup moment.

  Value 1: in the exported SQL file there is no comment "CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX";

  Value 2: in the exported SQL file there is the comment "CHANGE MASTER TO MASTER_LOG_FILE='XXX', MASTER_LOG_POS=XXX";

  [Notice]When executing the "mysqldump" on the slave, the command "flush table with read lock" is first enabled by default for acquiring the backup consistency snapshot. If there is no non-transaction table in the instance, recommend you to use "--single-transaction" together, preventing master-slave synchronous thread write-in from being blocked during the backup process.

Notices for data import

1. Delete data by mistake when importing data

   When "--add-drop-table=FALSE" is not specified, there is a "drop table" statement in the exported SQL file. When the backup data is recovered on line, table re-creation comes after "drop table", during which there might be error prompt "the table does not exist and original table data is lost".

2. Lock table when importing data

   When "--add-locks=FALSE" is not specified, there is a "lock table write" statement in the exported SQL file. When the backup data is recovered on line, the table is locked, during which write-in of the current table's other connections are blocked, and several connections might rise.

3. When 5.7 enables GTID, there is "SET @@SESSION.SQL_LOG_BIN= 0" statement in SQL that is exported by using "--set-gtid-purged" parameter. When importing, the data do not record "binlog". Thus, the slave does not synchronize data. To ensure consistency of master-slave data, you need to remove the following statements in files from "mysqldump":

   SET @@SESSION.SQL_LOG_BIN= 0;

4. In the exported SQL file, there are both database table data and some variable setting statements. Special attention must be given in online importing.