MySQL 的原生备份工具 mysqldump 是特别常用的备份工具，它具有如下优点：
- 工具使用并不复杂，命令简单。
- 将数据生成 SQL语句，方便在不同数据库平台之间做数据迁移
- 对于 innodb 表可以在线备份。
本文主要探讨 mysqldump 是如何保证备份一致性的。
如何保证备份的一致性使用到如下两个关键参数
1. --single-transaction
加上这个参数目的在于，在备份 innodb 表前，将事务的隔离级别设置为 REPEATABLE READ，同时显示的开启事务快照，在事务中进行一致性快照读。摘自官方文档[1]
This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
2. --master_data
执行 show master status\G; 获取binlog文件和position值，同时需要注意的是，在指定了 --single-transaction 之后，在获取binlog pos 点会加全局读锁，获取到之后，会立即释放。官方文档上有如下说明[2]：
The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.
在了方便观察，我们打开 general log 方便进行查看，
使用参数 --master_data 和 --single-transaction。
1.执行一次备份操作
(base) [root@ip-10-0-0-22 ~]# /usr/local/mysql5.7/bin/mysqldump -S /tmp/mysql3306.sock --databases mysqldump_test --single-transaction --routines --triggers --master-data=2 > mysqldump_test.sql
2.观察 general log 输出
这里进行了一些删减，但并不影响我们理解整个过程
2022-03-16T09:44:02.229288Z 35 Query FLUSH /*!40101 LOCAL */ TABLES
2022-03-16T09:44:02.229422Z 35 Query FLUSH TABLES WITH READ LOCK
2022-03-16T09:44:02.229526Z 35 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-03-16T09:44:02.229619Z 35 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-03-16T09:44:02.231371Z 35 Query SHOW MASTER STATUS
2022-03-16T09:44:02.231457Z 35 Query UNLOCK TABLES
2022-03-16T09:44:02.234259Z 35 Init DB mysqldump_test
2022-03-16T09:44:02.234390Z 35 Query SHOW CREATE DATABASE IF NOT EXISTS `mysqldump_test`
2022-03-16T09:44:02.234472Z 35 Query SAVEPOINT sp
2022-03-16T09:44:02.234538Z 35 Query show tables
2022-03-16T09:44:02.234682Z 35 Query show table status like 'innodb\_table'
2022-03-16T09:44:02.235044Z 35 Query show create table `innodb_table`
2022-03-16T09:44:02.235157Z 35 Query SET SESSION character_set_results = 'utf8'
2022-03-16T09:44:02.235235Z 35 Query show fields from `innodb_table`
2022-03-16T09:44:02.235480Z 35 Query show fields from `innodb_table`
2022-03-16T09:44:02.235713Z 35 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `innodb_table`
2022-03-16T09:44:02.235865Z 35 Query SET SESSION character_set_results = 'binary'
2022-03-16T09:44:02.235961Z 35 Query use `mysqldump_test`
2022-03-16T09:44:02.236199Z 35 Query SHOW TRIGGERS LIKE 'innodb\_table'
2022-03-16T09:44:02.236587Z 35 Query ROLLBACK TO SAVEPOINT sp
2022-03-16T09:44:02.236675Z 35 Query show table status like 'myisam\_table'
2022-03-16T09:44:02.237049Z 35 Query show create table `myisam_table`
2022-03-16T09:44:02.237175Z 35 Query SET SESSION character_set_results = 'utf8'
2022-03-16T09:44:02.237295Z 35 Query show fields from `myisam_table`
2022-03-16T09:44:02.237559Z 35 Query show fields from `myisam_table`
2022-03-16T09:44:02.237798Z 35 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `myisam_table`
2022-03-16T09:44:02.238093Z 35 Query use `mysqldump_test`
2022-03-16T09:44:02.238208Z 35 Query select @@collation_database
2022-03-16T09:44:02.238312Z 35 Query SHOW TRIGGERS LIKE 'myisam\_table'
2022-03-16T09:44:02.238578Z 35 Query SET SESSION character_set_results = 'utf8'
2022-03-16T09:44:02.238666Z 35 Query ROLLBACK TO SAVEPOINT sp
2022-03-16T09:44:02.238773Z 35 Query RELEASE SAVEPOINT sp
2022-03-16T09:44:02.238849Z 35 Query use `mysqldump_test`
整个备份流程如下：
- 执行 flush table，目的是将内存中的表结构同步到磁盘上。
- 加上全局 S 锁，执行FLUSH TABLES WITH READ LOCK。
- 将 事务隔离级别设置为 RR，以此来实现一致性非锁定读。
- 显式的开始一致性事务快照，将整个备份任务放在一个事务中进行。
上面的部分就是参数 --single-transaction 起作用，接下来执行：
- 获取 binlog 位点相关信息
- 执行 unlock table，解锁 innodb 事务引擎的表
这一部分是 --master-data 起作用，下面就是开始遍历整个数据库中的表进行备份：
- 在开始备份的时候都会开启一个保存点，当备份完整之后进行 rollback，方便发生问题的时候进行回滚。需要注意的是，在此期间无法执行 DDL 语句。
- 同时备份触发器，视图等。
不使用 --master_data 和 --single-transaction
照例我们打开 general log 观察输出
2022-03-16T10:23:18.900771Z 36 Init DB mysqldump_test
2022-03-16T10:23:18.900842Z 36 Query SHOW CREATE DATABASE IF NOT EXISTS `mysqldump_test`
2022-03-16T10:23:18.900949Z 36 Query show tables
2022-03-16T10:23:18.901102Z 36 Query LOCK TABLES `innodb_table` READ /*!32311 LOCAL */,`myisam_table` READ /*!32311 LOCAL */
2022-03-16T10:23:18.901201Z 36 Query show table status like 'innodb\_table'
2022-03-16T10:23:18.902373Z 36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `innodb_table`
2022-03-16T10:23:18.902531Z 36 Query SET SESSION character_set_results = 'binary'
2022-03-16T10:23:18.902605Z 36 Query use `mysqldump_test`
2022-03-16T10:23:18.903162Z 36 Query show table status like 'myisam\_table'
2022-03-16T10:23:18.903594Z 36 Query show create table `myisam_table`
2022-03-16T10:23:18.904309Z 36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `myisam_table`
2022-03-16T10:23:18.908179Z 36 Query UNLOCK TABLES
2022-03-16T10:23:18.908308Z 36 Query UNLOCK TABLES
可以看到在备份过程中会对所有表执行 lock table，在备份完成之后执行 unlock table。
需要注意的是，这种情况下只能保证单个库的一致性，无法保证多个库的一致性，也就是说，在整个备份期间，有可能看到 database1 是 01:00 时刻的镜像，而 database2 是 02:00 时刻的镜像。
mysqldump 使用 --single-transaction 来保证备份的一致性，并且可以在备份期间执行 DML 语句。同时我们还可以使用 --lock-all-tables 选项，会在备份期间锁住所有表，对业务影响比较大。
[1]https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction
[2]https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data
如果您有其他问题，欢迎您联系火山引擎技术支持服务
