mysqldump是官方提供的mysql逻辑备份命令,接下来我们就通过general log分析mysqldump命令的执行过程,从而总结mysqldump的原理。
前提:
mysqldump命令:
mysqldump -u -p --single-transaction --master-data=2 --triggers --routines --events --all-databases > t.sql
MySQL版本:5.7.27
通过下方的general日志我们可以得出mysqldump执行的过程精简如下:
1、FLUSH /*!40101 LOCAL */ TABLES 强制关闭所有正在使用的表
2、FLUSH TABLES WITH READ LOCK 关闭所有打开的表,并使用全局读锁锁定整个实例下的所有表
3、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 修改隔离级别为rr
4、START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 开启一致性事物快照
5、SHOW VARIABLES LIKE 'gtid\_mode' 查询是否开启GTID
6、SELECT @@GLOBAL.GTID_EXECUTED 获取GTID信息(用于主从复制)
7、SHOW MASTER STATUS 获取binlog和pos信息(用于主从复制)
8、UNLOCK TABLES 释放表锁
9、SHOW DATABASES 获取所有库信息
10、app-plateform 进入app-plateform库
11、SHOW CREATE DATABASE IF NOT EXISTS `app-plateform` 获取创建库的预警
12、SAVEPOINT sp 设置事物回滚点
13、show tables 获取库中所有表的信息
14、show table status like 'pub_alarm_history' 获取表的状态信息
15、show create table `pub_alarm_history` 获取创建表的语句
16、SELECT /*!40001 SQL_NO_CACHE */ * FROM `pub_alarm_history` 查询表中的数据
17、use `app-plateform`
18、SHOW TRIGGERS LIKE 'pub_alarm_history' 获取触发器
19、ROLLBACK TO SAVEPOINT sp 回滚到回滚点sp
重复12-19,直至所有表及数据备份完成
根据general日志中的过程我们可以得知mysqldump的备份原理如下:
1、关闭所有打开的表
2、调用FTWRL锁定所有的表,全局禁止更新
3、修改实例隔离级别
4、开启一致性快照读
5、获取gtid和binlog、pos信息
6、释放FTWRL锁
7、设置回滚点逐一备份数据表
8、备份完成
了解到mysqldump的备份原理后我们可以得知mysqldump备份存在的问题:
1、不增加–single-transaction,整个备份过程中需要全局锁表,阻塞时间长
2、增加–single-transaction时,获取binlog信息后释放锁,通过一致性快照读进行数据备份,无法支持myisam表的一致性备份(备份过程中仍有增量数据写入,myisam表不支持一致性快照
除了上述两个问题,mysqldump还存在两个不在上述讨论中的问题如下:
3、mysqldump是单线程,数据量大的时候,备份恢复时间长
4、在进行单事务备份时,为确保有效的备份文件(正确的表内容和二进制日志位置),不能有其他连接应使用语句:ALTER TABLE,CREATE TABLE,DROP TABLE,RENAME TABLE,TRUNCATE等DDL语句。这会导致一致状态被破坏,可能导致mysqldump执行SELECT检索表数据时查询到 不正确的内容 或 备份失败
general日志:
2021-02-08T18:49:14.721338+08:00 3631 Connect root@localhost on using Socket
2021-02-08T18:49:14.721621+08:00 3631 Query /*!40100 SET @@SQL_MODE='' */
2021-02-08T18:49:14.721780+08:00 3631 Query /*!40103 SET TIME_ZONE='+00:00' */
2021-02-08T18:49:14.722109+08:00 3631 Query FLUSH /*!40101 LOCAL */ TABLES
2021-02-08T18:49:14.726084+08:00 3631 Query FLUSH TABLES WITH READ LOCK
2021-02-08T18:49:14.726208+08:00 3631 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-02-08T18:49:14.726273+08:00 3631 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2021-02-08T18:49:14.726431+08:00 3631 Query SHOW VARIABLES LIKE 'gtid\_mode'
2021-02-08T18:49:14.727846+08:00 3631 Query SELECT @@GLOBAL.GTID_EXECUTED
2021-02-08T18:49:14.727975+08:00 3631 Query SHOW MASTER STATUS
2021-02-08T18:49:14.728098+08:00 3631 Query UNLOCK TABLES
2021-02-08T18:49:14.728295+08:00 3631 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
2021-02-08T18:49:14.732872+08:00 3631 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
2021-02-08T18:49:14.734330+08:00 3631 Query SHOW DATABASES
2021-02-08T18:49:14.735078+08:00 3631 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2021-02-08T18:49:14.735579+08:00 3631 Init DB app-plateform
2021-02-08T18:49:14.735639+08:00 3631 Query SHOW CREATE DATABASE IF NOT EXISTS `app-plateform`
2021-02-08T18:49:14.735696+08:00 3631 Query SAVEPOINT sp
2021-02-08T18:49:14.735822+08:00 3631 Query show tables
2021-02-08T18:49:14.736056+08:00 3631 Query show table status like 'pub\_alarm\_history'
2021-02-08T18:49:14.736922+08:00 3631 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-02-08T18:49:14.736982+08:00 3631 Query SET SESSION character_set_results = 'binary'
2021-02-08T18:49:14.737028+08:00 3631 Query show create table `pub_alarm_history`
2021-02-08T18:49:14.737147+08:00 3631 Query SET SESSION character_set_results = 'utf8'
2021-02-08T18:49:14.737193+08:00 3631 Query show fields from `pub_alarm_history`
2021-02-08T18:49:14.737882+08:00 3631 Query show fields from `pub_alarm_history`
2021-02-08T18:49:14.738241+08:00 3631 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `pub_alarm_history`
2021-02-08T18:49:14.759745+08:00 3631 Query SET SESSION character_set_results = 'binary'
2021-02-08T18:49:14.759845+08:00 3631 Query use `app-plateform`
2021-02-08T18:49:14.759926+08:00 3631 Query select @@collation_database
2021-02-08T18:49:14.760009+08:00 3631 Query SHOW TRIGGERS LIKE 'pub\_alarm\_history'
2021-02-08T18:49:14.760430+08:00 3631 Query SET SESSION character_set_results = 'utf8'
2021-02-08T18:49:14.760483+08:00 3631 Query ROLLBACK TO SAVEPOINT sp
2021-02-08T18:49:14.760545+08:00 3631 Query show table status like 'pub\_bind\_mobile\_user'
2021-02-08T18:49:14.760940+08:00 3631 Query SET SQL_QUOTE_SHOW_CREATE=1