mysql解析dump_mysqldump原理分析

今天学习了下mysqldump原理，具体的结论如下：

1、mysqldump在不加任何参数进行备份的时候：

如果单独备份一个表会对备份表上读锁，直到备份结束unlock，如果备份的整个库，那么会同时lock 这个库下的所有的表，最后在unlock tables，如果备份的是整个实例(加参数--all-databases 或者-A会备份除了performance_schema和performance_schema这俩库之外的所有的库)，那么是一个库一库的去备份，也就是说先备份库A，把库A下的所有的表上读锁，备份完库A，unlock tables,然后再备份库B,把库B下的所有的表上读锁，备份完库B，unlock tables，那么所以在使用mysqldump备份某个表或者某个库的时候，没有加任何的参数，会上读锁，并且备份出来的数据是一致性的，但是如果备份的是整个实例，那么库和库之间的数据的一致性就不能保证了；

2、参数--single-transaction ；

针对innodb的引擎，可以加上参数 --single-transaction来保证备份的一致性，并且是借助的修改隔离级别为REPEATABLE READ+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */开启快照读事务共同来保证一致性的，所以不需要加read lock;注意该参数仅仅对innodb引擎起作用，对于myisam引擎，虽然添加了--single-transaction参数的myisam表备份处理过程和innodb的过程完全一致，但是因为myisam不支持事务，在整个dump过程中无法保证可重复读，无法得到一致性的备份。

3、参数--master-data；

--master-data指定为2指的是会在备份文件中生成CHANGE MASTER的注释。如下所示：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587;

如果该值设置为1，则生成的是CHANGE MASTER的命令，而不是注释。如下所示：

CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587;

当加上这个参数的时候，为了得到准确的binlog的位置状态信息，会通过FLUSH TABLES WITH READLOCK来保证，备份开始到结束，是不允许别的事务修改的，同时也就保证了一致性；

4、参数--single-transaction和参数参数--master-data一起使用；

也会执行 FLUSH TABLES WITH READ LOCK，但是在还没有开始备份时，也就是在 SHOW MASTER STATUS显示了主库的binlog状态之后就unlock tables了，也会 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，之后和单独加--single-transaction过程是一样的。

5、参数--lock-all-tables，通过给整个实例所有表都加read lock来保证一致性备份；

加上--lock-all-tables和不加上--lock-all-tables得区别就在于前者是FLUSH TABLES WITH READ LOCK对整个实例所有的表都上读锁，后者只针对要备份的表加读锁(LOCK TABLES `liu` READ)；并且前者并没有显现的unlock tables，因为整个过程中数据库是不能写的，并且FLUSH TABLES WITH READ LOCK这个命令一旦这个会话结束，相应的读锁也就不存在了，而后者只是锁了一个表，显现的unlock解锁了，其实后者也是会话结束就释放对表的读锁了，也可以不加unlock，

6、START TRANSACTION和START TRANSACTION WITH CONSISTENT SNAPSHOT并不一样;

START TRANSACTION WITH CONSISTENT SNAPSHOT相当于在执行完START TRANSACTION后对每个Innodb表执行了SELECT操作，在隔离级别为REPEATABLE READ时，并不是当start transaction 就能保证之后的查询内容是一样，而是当你发出第一个query的时候，才会开启快照读取，之后再有相同的sql查出来的结果是一样的。

在mysqldump加上参数--single-transaction的时候使用的是START TRANSACTION WITH CONSISTENT SNAPSHOT，而不是START TRANSACTION来保证一致性的，是因为每个表的备份时间并不相同，如果使用START TRANSACTION，在对第一张表进行备份的期间，别的事务对第二个表进行了insert数据A，那么在开始对第二张表备份时,是可以看到数据A的，那么第一个表和第二个表就不是一致性的了，所以START TRANSACTION无法实现当一个库下有多个表的时候的一致性。.

综上所述：在使用mysqldump进行数据备份的时候，尽量在业务量比较小的时候执行，并且根据是不是innodb引擎来选择不同的参数，如果是innodb的引擎可以使用--single-transaction参数来保证一致性，并且还不用上read lock；但是如果想保证整个实例的一致性(既有innodb又有myisam的表)最好还是使用参数--lock-all-tables，当然为了实现point to point恢复或者不停master服务来添加slave的目的，最好还是加上参数--master-data，同时也就能保证一致性，因为加上参数--master-data会执行FLUSH TABLES WITH READLOCK；

下面是具体的验证过程：

一：打开general log，便于分析mysqldump具体执行了什么操作

mysql> set global general_log=on;

其中，general log的存放路径可通过以下命令查看

mysql> show variables like '%general_log_file%';

二：执行MySQLdump导出表实验如下：

2.1首先什么参数都不加的情况：

bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql

查看相应的general_log：

bogon:root@/mysql/data/data>more bogon.log

/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:

Tcp port: 3306 Unix socket: /tmp/mysql.sock

Time Id Command Argument

180429 14:12:22 17 Quit

180429 14:12:55 18 Connect root@localhost on

18 Query /*!40100 SET @@SQL_MODE='' */

18 Query /*!40103 SET TIME_ZONE='+00:00' */

18 Query SHOW VARIABLES LIKE 'gtid\_mode'

18 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC

HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM

INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHE

MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENG

INE ORDER BY LOGFILE_GROUP_NAME

18 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR

OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC

HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NA

ME

18 Query SHOW VARIABLES LIKE 'ndbinfo\_version'

18 Init DB liuhe

18 Query SHOW TABLES LIKE 'blocks\_infos\_opensearch'

18 Query LOCK TABLES `blocks_infos_opensearch` READ /*!32311 LOCAL */

18 Query show table status like 'blocks\_infos\_opensearch'

18 Query SET SQL_QUOTE_SHOW_CREATE=1

18 Query SET SESSION character_set_results = 'binary'

18 Query show create table `blocks_infos_opensearch`

18 Query SET SESSION character_set_results = 'utf8'

18 Query show fields from `blocks_infos_opensearch`

18 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos_opensearch`

18 Query SET SESSION character_set_results = 'binary'

18 Query use `liuhe`

18 Query select @@collation_database

18 Query SHOW TRIGGERS LIKE 'blocks\_infos\_opensearch'

18 Query SET SESSION character_set_results = 'utf8'

18 Query UNLOCK TABLES

18 Quit

2.2:加上参数--single-transaction

bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --single-transaction liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql

bogon:root@/mysql/data/data>more bogon.log

/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:

Tcp port: 3306 Unix socket: /tmp/mysql.sock

Time Id Command Argument

180429 14:20:41 21 Quit

180429 14:20:47 22 Connect root@localhost on

22 Query /*!40100 SET @@SQL_MODE='' */

22 Query /*!40103 SET TIME_ZONE='+00:00' */

22 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

22 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ #开启快照读

22 Query SHOW VARIABLES LIKE 'gtid\_mode'

22 Query UNLOCK TABLES

22