我们都知道MySQL逻辑备份工具mysqldump可以保证备份数据的一致性，但是它是怎么保持一致性的？

本文不讨论mysqldump具体的选项和用法，一直对mysqldump的工作机制梳理的不太清楚，这篇主要来分析下mysqldump的工作原理和工作步骤，了解它为什么可以获取一致性的备份。

关于mysqldump常用选项说明与用法参考另一篇博文：MySQL Backup mysqldump 常用选项与主要用法

通过打开general log的方法来记录mysqldump备份的过程。

前期准备

开启general log

(root@localhost) [(none)] > set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)] > show global variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /data/mysql/3306/data/dbabd.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)

mysqldump执行全库备份

# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 -A > test_all.sql

分析general log日志

开头部分

53 Connect   admin@dbabd on  using TCP/IP
53 Query     /*!40100 SET @@SQL_MODE='' */
53 Query     /*!40103 SET TIME_ZONE='+00:00' */
53 Query     FLUSH /*!40101 LOCAL */ TABLES
53 Query     FLUSH TABLES WITH READ LOCK
53 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
53 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
53 Query     SHOW VARIABLES LIKE 'gtid\_mode'
53 Query     SHOW MASTER STATUS
53 Query     UNLOCK TABLES
53 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
53 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
53 Query     SHOW DATABASES
53 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'

• 备份连接，设置sql_mode为''，设置time_zone

53 Connect   admin@dbabd on  using TCP/IP
53 Query     /*!40100 SET @@SQL_MODE='' */
53 Query     /*!40103 SET TIME_ZONE='+00:00' */

• FLUSH TABLES

53 Query     FLUSH /*!40101 LOCAL */ TABLES

关闭所有的表，并强制关闭所有正在使用的表，同时也会移除所有query cache结果。

详细说明可以参考官方文档：FLUSH TABLES

根据官文文档的描述：

FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_name ... WITH READ LOCK instead.

意思是如果有一个会话正在执行LOCK TABLES ... READ语句，另一个会话执行FLUSH TABLES语句是不允许的，会被阻塞。可以使用FLUSH TABLES ... WITH READ LOCK替代。

• FLUSH TABLES WITH READ LOCK

53 Query     FLUSH TABLES WITH READ LOCK

关闭所有打开的表并且对所有数据库表加一个全局读锁。

详细说明可以参考官方文档：FLUSH TABLES WITH READ LOCK

根据官方文档的描述

FLUSH TABLES WITH READ LOCK acquires a global read lock rather than
table locks, so it is not subject to the same behavior as LOCK TABLES
and UNLOCK TABLES with respect to table locking and implicit commits:

UNLOCK TABLES implicitly commits any active transaction only if any
tables currently have been locked with LOCK TABLES. The commit does
not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK
because the latter statement does not acquire table locks.

Beginning a transaction causes table locks acquired with LOCK TABLES
to be released, as though you had executed UNLOCK TABLES. Beginning a
transaction does not release a global read lock acquired with FLUSH
TABLES WITH READ LOCK.

1. FLUSH TABLES WITH READ LOCK语句获取的是一个全局读锁而不是进行锁表，不像LOCK TABLES和U