mysql解析dump_mysqldump备份原理解析

这里通过开启general log追踪mysqldump的备份过程，可以动态调整general log的参数设置。

一、不加--single-transaction备份

mysqldump --default-character-set=utf8mb4 -uroot --triggers -R -E -A --master-data=2 |gzip >dbbackup.sql.gz

1、连接数据库

2、调整sql_mode、time_zone

3、刷新tables(关闭所有打开的表并清空缓存数据)

4、FLUSH TABLES WITH READ LOCK(执行全局读锁)

5、记录相关变量、参数、binlog位置等

6、备份数据文件、表空间等

7、备份业务表结构、表数据、触发器

8、备份系统表结构、表数据、触发器

9、备份函数、存储过程

10、备份结束退出，全局锁自动释放

2017-06-24T00:48:48.622170Z        56 Connect   root@localhost on  using Socket

2017-06-24T00:48:48.622372Z        56 Query     /*!40100 SET @@SQL_MODE='' */

2017-06-24T00:48:48.622500Z        56 Query     /*!40103 SET TIME_ZONE='+00:00' */

2017-06-24T00:48:48.622654Z        56 Query     FLUSH /*!40101 LOCAL */ TABLES

2017-06-24T00:48:48.623539Z        56 Query     FLUSH TABLES WITH READ LOCK

2017-06-24T00:48:48.623669Z        56 Query     SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =

'performance_schema' AND table_name = 'session_variables'

2017-06-24T00:48:48.624191Z        56 Query     SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARI

ABLE_NAME LIKE 'rocksdb\_skip\_fill\_cache'

2017-06-24T00:48:48.625417Z        56 Query     SHOW VARIABLES LIKE 'gtid\_mode'

2017-06-24T00:48:48.627542Z        56 Query     SHOW MASTER STATUS

2017-06-24T00:48:48.627743Z        56 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E

NGINE, 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

2017-06-24T00:48:48.628321Z        56 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE

NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L

OGFILE_GROUP_NAME

2017-06-24T00:48:48.628778Z        56 Query     SHOW DATABASES

2017-06-24T00:48:48.629166Z        56 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'

2017-06-24T00:48:48.631175Z        56 Init DB   dbtest

2017-06-24T00:48:48.631282Z        56 Query     SHOW CREATE DATABASE IF NOT EXISTS `dbtest`

2017-06-24T00:48:48.631415Z        56 Query     show tables

2017-06-24T00:48:48.631704Z        56 Query     show table status like 't'

2017-06-24T00:48:48.632269Z        56 Query     SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:48:48.632372Z        56 Query     SET SESSION character_set_results = 'binary'

2017-06-24T00:48:48.632462Z        56 Query     show create table `t`

2017-06-24T00:48:48.632642Z        56 Query     SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:48:48.632810Z        56 Query     show fields from `t`

2017-06-24T00:48:48.633252Z        56 Query     show fields from `t`

2017-06-24T00:48:48.633664Z        56 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`

2017-06-24T00:48:48.633894Z        56 Query     SET SESSION character_set_results = 'binary'

2017-06-24T00:48:48.634000Z        56 Query     use `dbtest`

2017-06-24T00:48:48.634136Z        56 Query     select @@collation_database

2017-06-24T00:48:48.634281Z        56 Query     SHOW TRIGGERS LIKE 't'

2017-06-24T00:48:48.634750Z        56 Query     SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:48:48.634887Z        56 Query     show table status like 't1'

2017-06-24T00:48:48.635388Z        56 Query     SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:48:48.635