Mysqldump things to think about before using [reprint]

Reproduced in: http://blog.itpub.net/29254281/viewspace-1392757/

Mysqldump are often used to migrate data and backups.

The following is the creation of experimental data, two databases and several tables
Create Database db1;
Use DB1;
CREATE TABLE t1 (ID int primary key);
INSERT into T1 VALUES (1), (2), (3);
CREATE TABLE t2 (ID int primary key);
INSERT into T2 values (1), (2), (3);

Create database DB2;
Use DB2;
CREATE table t3 (ID int primary key);
INSERT into T3 values (1), (2), (3);
CREATE table t4 (ID int primary key);
INSERT into T4 values (1), (2), (3);
Commit

The common parameters of mysqldump are as follows
1. Export the specified database (--databases)
Mysqldump-uroot--databases db1 DB2 > Test.sql

2. Export the structure of the specified database (-D)
Mysqldump-uroot--databases-d db1 DB2 > Test.sql

3. Refresh log before exporting (-f)

4. Setting the character set (--default-character-set)

5. Set extension Insert (-e--skip-extended-insert disable extended insert)

6. Lock Table (--lock-tables)

7. Lock all tables for all databases (--lock-all-tables)

8. Consistent read, only valid for InnoDB (--single-transaction)

9. Get the Binlog position (--master-data 1 writes the binlog position in body 2 to write the binlog position to the comment)

Common usage
1. Migrating data
Export the DB1 DB2 database through mysqldump. Then import the target database through a pipeline
Mysqldump-uroot--single-transaction--databases DB1 DB2 | Mysql-uroot-p123456-h 172.16.1.25

2. Export data backup or create slave
Mysqldump-uroot--single-transaction--master-data--databases db1 DB2 > Test.sql

3. Generate table structure and data, respectively
The SELECT INTO OutFile is for a single table. Use the--TAB option to export multiple tables
Mysqldump-uroot--single-transaction--tab=f:\ DB1


Important parameter Resolution (MySQL 5.6.14)
Turn on MySQL general_log, and then use the mysqldump operation to view the resulting logs.

1.--lock-tables
Execute command
Mysqldump-uroot--lock-tables--databases db1 DB2 > Test.sql
When exporting db1, it will lock all the tables in the DB1 and release the lock after the export is finished. Then export the DB2 again.
That is, when the DB1 is exported, the DB2 data may still be changing.


2.--lock-all-tables
Mysqldump-uroot--lock-all-tables--databases db1 DB2 > Test.sql
It will lock all the tables in the database at the outset,Note that it will use the flush TABLES


3.--single-transaction
Mysqldump-uroot--single-transaction--databases db1 DB2 > Test.sql
You can see that it sets the entire export process to a transaction. Avoid locks


4.--master-data
It locks all tables on all databases and queries the location of the Binlog.Note that it will use the flush TABLES


5.--master-data +--single-transaction
Mysqldump-uroot--master-data--single-transaction--databases db1 DB2 > Test.sql
This combination will lock all tables in all databases first, and then release the lock immediately after reading the Binlog information, which is a very short process.
Then the entire export process is in one transaction.
Note that it will use the flush TABLES



mysqldump things to think about before you use them

If flush tables is required during mysqldump execution, and a slow SQL is running at this time, Mysqldump will be blocked (waiting for table flush),
And all other connections to the table (or even queries) are blocked. The system is hung.

This problem also exists in the Xtrabackup backup.

If it is done manually, be sure to turn on another connection, monitor show processlist, and see if it is blocked.
If it is scheduled to execute, spell the character.

In fact, optimizing slow SQL is the right path.

In addition, in the mysqldump export process, do not have any DDL operation, or else will also cause metadata lock's serial blocking.

Reference:
HTTP://BLOG.ITPUB.NET/29254281/VIEWSPACE-1157701/(Waiting for Table blocking query problem)
http://blog.itpub.net/29254281/viewspace-1383193/

Http://imysql.com/2008_10_24_deep_into_mysqldump_options
http://hidba.org/?p=421

Mysqldump things to think about before using [reprint]