Analysis of main parameters of mysqldump

Mysqldump is a logical backup that is more suitable for libraries with small amounts of backup data. Today we'll take a look at some of the more common important parameters and analyze what they have done and need to open the General_log.

Set global General_log = 1;+------------------+--------------------------------------+| Variable_name    | Value                                |+------------------+--------------------------------------+| general_log      | On                                   | | general_log_file |/data/mysql/mysql3317/data/bogon.log |+------------------+--------------------------- -----------+

The table structure is as follows:

1. Row ***************************       table:zhangshuocreate table:create Table ' Zhangshuo ' (  ' id ' int (one) ' DEFAULT NULL,  ' name ' varchar ($) default null) Engine=innodb default charset=utf81 row in Set (0.00 sec)

1. Do not add parameter backup Zhangshuo table:

[[email protected] ~]#/usr/local/mysql/bin/mysqldump-s/tmp/ Mysql3317.sock Zhangshuo Zhangshuo >/tmp/test.sql 

                    4 Init DB Zhangshuo 4 Query SHOW TABLES like ' Zhangshuo ' 4 Query LOCK TABLES ' Zhangshuo ' READ/*!32311 LOCAL */4 Query Show Table status like ' Zhangshuo ' 4 query set sql_quote_show_create=1 4 query set SESSION character_set_resu LTS = ' binary ' 4 query show create TABLE ' Zhangshuo ' 4 query SET SESSION cha racter_set_results = ' UTF8 ' 4 query show fields from ' Zhangshuo ' 4 query SEL ECT/*!40001 Sql_no_cache */* from ' Zhangshuo ' 4 Query SET SESSION character_set_results = ' binary                    ' 4 query use ' Zhangshuo ' 4 query SELECT @ @collation_database                    4 query SHOW TRIGGERS like ' Zhangshuo ' 4 Query SET SESSION character_set_results = ' UTF8 ' 4 QueRy UNLOCK TABLES 4 Quit 

2. Use the--single-transaction parameter:

                    5 Query/*!40100 SET @ @SQL_MODE = ' * * 5 Query/*!40103 set time_zone= ' +00:0     0 ' */5 query SET SESSION TRANSACTION Isolation level repeatable READ 5 Query START TRANSACTION/*!40100 with consistent SNAPSHOT */5 Query SHOW VARIABLES like ' ndbinfo\_vers                    Ion ' 5 Init DB Zhangshuo 5 Query SHOW TABLES like ' Zhangshuo '     5 Query SavePoint SP 5 Query Show Table status like ' Zhangshuo ' 5 Query                    Set sql_quote_show_create=1 5 Query set SESSION character_set_results = ' binary '                    5 Query Show create TABLE ' Zhangshuo ' 5 Query SET SESSION character_set_results = ' UTF8 '  5 query Show fields from ' Zhangshuo ' 5 Query SELECT/*!40001 Sql_no_cache */*       From ' Zhangshuo '             5 query SET SESSION character_set_results = ' binary ' 5 Query use ' Zhangshuo '                    5 Query SELECT @ @collation_database 5 query SHOW TRIGGERS like ' Zhangshuo ' 5 query SET SESSION character_set_results = ' UTF8 ' 5 Query ROLLBACK to SavePoint s P 5 Query RELEASE savepoint SP 5 Quit

We see the thing isolation level changed to RR, using InnoDB's thing isolation to prevent phantom reading from occurring.

3. Use the--master-data=2--single-transaction parameter:

[Email protected] ~]#/usr/local/mysql/bin/mysqldump--single-transaction--master-data=2-s/tmp/mysql3317.sock Zhangshuo Zhangshuo >/tmp/test2.sql

6 Connect [email protected] on 6 Query/*!40100 SET @ @SQL_MODE = ' */6 Qu                    ery/*!40103 SET time_zone= ' +00:00 ' */6 Query FLUSH/*!40101 LOCAL */TABLES 6 query FLUSH TABLES with READ LOCK 6 Query SET SESSION TRANSACTION Isolation Level REPEATABL     E READ 6 Query START TRANSACTION/*!40100 with consistent SNAPSHOT */6 query  Show VARIABLES like ' gtid\_mode ' 6 query SHOW MASTER STATUS 6 Query UNLOCK                    TABLES 6 Query SHOW VARIABLES like ' ndbinfo\_version ' 6 Init DB Zhangshuo 6 query SHOW TABLES like ' Zhangshuo ' 6 Query savepoint SP 6                    Query Show table status like ' Zhangshuo ' 6 Query SET sql_quote_show_create=1 6 Query SET SESSION Character_set_results = ' binary ' 6 query show create TABLE ' Zhangshuo ' 6 query                    SET SESSION character_set_results = ' UTF8 ' 6 Query show fields from ' Zhangshuo ' 6 Query SELECT/*!40001 sql_no_cache */* from ' Zhangshuo ' 6 Query SET SESSION character_se T_results = ' binary ' 6 query use ' Zhangshuo ' 6 query select @ @collation_data Base 6 query SHOW TRIGGERS like ' Zhangshuo ' 6 Query SET SESSION character_se t_results = ' UTF8 ' 6 query ROLLBACK to savepoint SP 6 query RELEASE Savepoin T SP

First, flush table with read lock (closes the open table) after global read lock changes the Thing isolation level to RR and executes start TRANSACTION/*!40100 with consistent SNAPSHOT */ Create a one-time snapshot and see if Gtid is turned on to get the Gtid execution location. Define breakpoints at Data backup SavePoint SP, finish back to this position and start the next table, and finally release the breakpoint.

After using--master-data=2 we look at the backup SQL, can see Binlog execution location, very convenient to build master-slave.

[Email protected] tmp]# grep "Change Master to"/tmp/test2.sql – Change Master to master_log_file= ' mysql-bin.000006 ', MA ster_log_pos=667;

Summarize:

The recommended parameters for backing up the InnoDB table are as follows:

[Email protected] tmp]#/usr/local/mysql/bin/mysqldump--single-transaction--master-data=2-s/tmp/mysql3317.sock Zhangshuo Zhangshuo >/tmp/test2.sql

　　

　

　　

Analysis of main parameters of mysqldump