mysqldump 如何保证备份一致性

数据库关系型数据库技术服务知识库
前言

MySQL 的原生备份工具 mysqldump 是特别常用的备份工具,它具有如下优点:

  1. 工具使用并不复杂,命令简单。
  2. 将数据生成 SQL语句,方便在不同数据库平台之间做数据迁移
  3. 对于 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 输出
这里进行了一些删减,但并不影响我们理解整个过程
````undefined
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`
```
`
整个备份流程如下:

1. 执行 flush table,目的是将内存中的表结构同步到磁盘上。
2. 加上全局 S 锁,执行FLUSH TABLES WITH READ LOCK。
3. 将 事务隔离级别设置为 RR,以此来实现一致性非锁定读。
4. 显式的开始一致性事务快照,将整个备份任务放在一个事务中进行。

上面的部分就是参数 --single-transaction 起作用,接下来执行:

5. 获取 binlog 位点相关信息
6. 执行 unlock table,解锁 innodb 事务引擎的表

这一部分是 --master-data 起作用,下面就是开始遍历整个数据库中的表进行备份:

7. 在开始备份的时候都会开启一个保存点,当备份完整之后进行 rollback,方便发生问题的时候进行回滚。需要注意的是,在此期间无法执行 DDL 语句。
8. 同时备份触发器,视图等。

## 不使用 --master_data 和 --single-transaction
照例我们打开 general log 观察输出
````undefined
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](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](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data)
**如果您有其他问题,欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)

22
0
0
0
相关产品
评论
未登录
看完啦,登录分享一下感受吧~
暂无评论