正文
我们都知道 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.
FLUSH TABLES WITH READ LOCK
语句获取的是一个全局读锁而不是进行锁表, 不像 LOCK TABLES 和 UNLOCK TABLES 语句的行为;
只要任何表当前被 LOCK TABLES 锁住时, 执行 UNLOCK TABLES 会隐式提交任何活动的事务, 不过已执行
FLUSH TABLES WITH READ LOCK
再执行 UNLOCK TABLES 并不会进行提交, 因为后续的语句并不会获取表锁;
开始一个事务会造成 LOCK TABLES 获得的表锁释放, 就像已经执行了 UNLOCK TABLES. 开始一个事务并不会造成
FLUSH TABLES WITH READ LOCK
获取的全局读锁释放.
设置会话隔离级别为 REPEATABLE READ
53 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
开启一致性快照事务
53 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
因为要开启一致性快照事务, 所以必须将务事务的隔离级别设置成 REPEATABLE READ. 所以便有了上面设置会话级隔离级别的语句.
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions.
根据官方文档的描述, 这一步是为了确保该事务开启时之后读取的快照都是基于同一个时刻的, 同时获取得到一个事务号, 就是 SELECT 都能读取到一开始同一个的 READ VIEW, 不受之后其他事务修改或者未提交事务的影响.
详细说明可参考官方文档:
查看是否开启 GTID 模式
53 Query SHOW VARIABLES LIKE 'gtid\_mode'
获取当前 binlog 的位置信息
53 Query SHOW MASTER STATUS
释放全局读锁
53 Query UNLOCK TABLES
查看所有数据库信息
53 Query SHOW DATABASES
备份部分
来看下开始备份表的日志, 这里选取 test1 为例说明:
- 53 Init DB MySQL
- 53 Query SHOW CREATE DATABASE IF NOT EXISTS `mysql`
- 53 Query SAVEPOINT sp
- 53 Query show tables
- 53 Query show table status like 'columns\_priv'
- 53 Query SET SQL_QUOTE_SHOW_CREATE=1
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query show create table `columns_priv`
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Query show fields from `columns_priv`
- 53 Query show fields from `columns_priv`
- 53 Query SELECT /*!40001 SQL_NO_CACHE */* FROM `columns_priv`
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query use `mysql`
- 53 Query select @@collation_database
- 53 Query SHOW TRIGGERS LIKE 'columns\_priv'
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Query ROLLBACK TO SAVEPOINT sp
............ 省略............
- 53 Init DB test1
- 53 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
- 53 Query SAVEPOINT sp
- 53 Query show tables
- 53 Query show table status like 't1'
- 53 Query SET SQL_QUOTE_SHOW_CREATE=1
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query show create table `t1`
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Query show fields from `t1`
- 53 Query show fields from `t1`
- 53 Query SELECT /*!40001 SQL_NO_CACHE */* FROM `t1`
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query use `test1`
- 53 Query select @@collation_database
- 53 Query SHOW TRIGGERS LIKE 't1'
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Query ROLLBACK TO SAVEPOINT sp
- 53 Query show events
- 53 Query use `test1`
- 53 Query select @@collation_database
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query SHOW FUNCTION STATUS WHERE Db = 'test1'
- 53 Query SHOW PROCEDURE STATUS WHERE Db = 'test1'
............ 省略............
- 53 Init DB test2
- 53 Query SHOW CREATE DATABASE IF NOT EXISTS `test2`
- 53 Query SAVEPOINT sp
- 53 Query show tables
- 53 Query show table status like 't1'
- 53 Query SET SQL_QUOTE_SHOW_CREATE=1
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query show create table `t1`
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Query show fields from `t1`
- 53 Query show fields from `t1`
- 53 Query SELECT /*!40001 SQL_NO_CACHE */* FROM `t1`
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query use `test2`
- 53 Query select @@collation_database
- 53 Query SHOW TRIGGERS LIKE 't1'
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Query ROLLBACK TO SAVEPOINT sp
- 53 Query RELEASE SAVEPOINT sp
- 53 Query show events
- 53 Query use `test2`
- 53 Query select @@collation_database
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query SHOW FUNCTION STATUS WHERE Db = 'test2'
- 53 Query SHOW PROCEDURE STATUS WHERE Db = 'test2'
- 53 Query SET SESSION character_set_results = 'utf8'
- 53 Quit
查看建库语句, 所有数据库进行循环顺序备份
- 53 Init DB test1
- 53 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
创建检查点
53 Query SAVEPOINT sp
创建一个检查点, 检查点的作用是在一个事务中执行 ROLLBACK TO SAVEPOINT 语句之后能够将事务回滚到检查点位置而不中止事务.
详细说明可参考官方文档: SAVEPOINT
mysqldump 备份是通过执行 SELECT 进行的, 但是 SELECT 语句执行没结束同时会持有该对象的 MDL 锁, 为了保证在备份期间不影响已经备份表的 DDL 操作被阻塞, 所以就有了 SAVEPOINT, 每次备份完一张表就将事务回滚到 SAVEPOINT 的位置, 同时这个操作会释放该表的 MDL 锁, 但这并不会中止这个事务, 其他事务可以对这张表进行 DDL 操作.
获取表的状态信息
53 Query show table status like 'columns\_priv'
设置字符集为 binary
53 Query SET SESSION character_set_results = 'binary'
mysqldump 为了更好的备份表结构, 将字符集先设置成 binary, 避免出错.
备份建表语句
- 53 Query show tables
- 53 Query show table status like 't1'
设置字符集为 utf8
53 Query SET SESSION character_set_results = 'utf8'
开始备份表数据时将字符集设置为数据库的字符集.
获取表的字段信息
53 Query show fields from `t1`
开始备份表
53 Query SELECT /*!40001 SQL_NO_CACHE */* FROM `t1`
开始备份触发器
53 Query SHOW TRIGGERS LIKE 't1'
回滚到检查点
53 Query ROLLBACK TO SAVEPOINT sp
以上一张表就算备份完成, 接下去就是循环上面步骤备份完该数据库下所有的表.
完成所有表的备份之后, 最后备份的数据库中的事件, 函数, 存储过程:
- 53 Query show events
- 53 Query use `test1`
- 53 Query select @@collation_database
- 53 Query SET SESSION character_set_results = 'binary'
- 53 Query SHOW FUNCTION STATUS WHERE Db = 'test1'
- 53 Query SHOW PROCEDURE STATUS WHERE Db = 'test1'
至此, 一个数据库的备份完成, 开始备份其他数据库.
结尾部分
来看下结尾部分的日志
- 53 Query ROLLBACK TO SAVEPOINT sp
- 53 Query RELEASE SAVEPOINT sp
当备份完最后一个数据库的最后一张表后释放了检查点, 再完成最后一个数据库事件, 函数和存储过程的备份之后进行退出, 退出默认会进行提交操作, 所有备份结束.
总结
通过以上的日志分析, 可以总结下 mysqldump 备份的主要流程:
一开始执行 FLUSH TABLES 关闭实例中所有的表;
执行语句
FLUSH TABLES WITH READ LOCK
获取全局表的读锁, 保证表一致性;
设置会话级别事务的隔离级别为 REPEATABLE READ, 保证事务期间数据的一致性;
执行语句
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
创建一个一致性事务快照;
查看是否开启了 GTID;
获取当前状态下的 binlog 文件及位置信息 (如有指定选项 --master-data);
执行 UNLOCK TABLES 释放全局表读锁;
开始备份第一个数据库数据, 为事务创建一个检查点, 备份完一张表之后还原至检查点再接着备份下一张表, 直至该数据库所有的表备份完成, 接着备份下一个数据库数据, 直至所有数据库数据备份完成;
当备份完最后一个数据库数据后释放检查点, 退出并中止事务.
参考
- https://dev.mysql.com/doc/refman/5.7/en/flush.html
- http://www.unixfbi.com/475.html
☆[本人水平有限, 文中如有错误还请留言批评指正!]☆
来源: https://www.cnblogs.com/dbabd/p/10238105.html