MySQL dump备份时MySQL到底做了什么？
一、先决条件
mysqldump -uroot -p -S /data/mysql/data/3308/mysqld.sock -B testdb -R --triggers --routines --master-data=2 --single-transaction --set-gtid-purged=OFF > /root/testdb.sql
mysql> set global general_log=1; Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘%g%log%‘; +----------------------------------+------------------------------------+ | Variable_name | Value | +----------------------------------+------------------------------------+ | binlog_rows_query_log_events | ON | | general_log | ON | | general_log_file | /data/mysql/data/3308/dmp-udp3.log | | log_statements_unsafe_for_binlog | ON | | log_syslog | OFF | | log_syslog_facility | daemon | | log_syslog_include_pid | ON | | log_syslog_tag | | +----------------------------------+------------------------------------+ 8 rows in set (0.00 sec)
mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | sbtest1 | | sbtest2 | +------------------+ 2 rows in set (0.00 sec)
二、分析general_log日志过程
[root@dmp-udp3 ~]# /data/mysql/base/5.7.25/bin/mysqldump -uroot -p -S /data/mysql/data/3308/mysqld.sock -B testdb -R --triggers --routines --master-data=2 --single-transaction --set-gtid-purged=OFF > /root/testdb.sql
[root@dmp-udp3 ~]# cat /data/mysql/data/3308/dmp-udp3.log 2020-06-24T07:38:58.288670-00:00 391 Connect root@localhost on using Socket <---------- root@localhost用户本地socket连接 2020-06-24T07:38:58.289269-00:00 391 Query /*!40100 SET @@SQL_MODE=‘‘ */ <---------- 临时设置sql_mode严格模式为关闭 2020-06-24T07:38:58.289550-00:00 391 Query /*!40103 SET TIME_ZONE=‘+00:00‘ */ 2020-06-24T07:38:58.289764-00:00 391 Query FLUSH /*!40101 LOCAL */ TABLES <---------- 刷新表缓存 2020-06-24T07:38:58.290656-00:00 391 Query FLUSH TABLES WITH READ LOCK <---------- 加全局只读锁（ 因为mysqldump时加了 --master-data=2 参数，要获取MySQL服务器一个时间点的状态信息 ） 2020-06-24T07:38:58.291805-00:00 391 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ <---------- 会话级临时修改MVCC隔离级别为RR可重复读 2020-06-24T07:38:58.292738-00:00 391 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ <---------- 开启一个事务 2020-06-24T07:38:58.293075-00:00 391 Query SHOW MASTER STATUS <---------- 获取binlog文件名、position位点、GTID值 2020-06-24T07:38:58.293330-00:00 391 Query UNLOCK TABLES <---------- 释放全局只读锁（ 已经在上方获取到了一个时间点的状态信息，所以要释放全局锁 ） <---------- 查询 information_schema.partitions 表，确定 testdb 库是否有 “分区表” 2020-06-24T07:38:58.293657-00:00 391 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 AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE‘ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘testdb‘))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 2020-06-24T07:38:58.296022-00:00 391 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = ‘DATAFILE‘ AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (‘testdb‘)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 2020-06-24T07:38:58.297643-00:00 391 Query SHOW VARIABLES LIKE ‘ndbinfo\_version‘ 2020-06-24T07:38:58.301038-00:00 391 Init DB testdb 2020-06-24T07:38:58.301171-00:00 391 Query SHOW CREATE DATABASE IF NOT EXISTS `testdb` <---------- 查询testdb建库语句 2020-06-24T07:38:58.301309-00:00 391 Query SAVEPOINT sp <---------- 保存命名为 sp 在事务内某一个点的状态 https://dev.mysql.com/doc/refman/5.7/en/savepoint.html 2020-06-24T07:38:58.301429-00:00 391 Query show tables 2020-06-24T07:38:58.301671-00:00 391 Query show table status like ‘sbtest1‘ <---------- 查询 sbtest1 表元数据信息 2020-06-24T07:38:58.301999-00:00 391 Query SET SQL_QUOTE_SHOW_CREATE=1 <---------- 设置导出后建表语句对表名、字段名加反引号 https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html 2020-06-24T07:38:58.302177-00:00 391 Query SET SESSION character_set_results = ‘binary‘ 2020-06-24T07:38:58.302310-00:00 391 Query show create table `sbtest1` <---------- 查询 sbtest1 表的建表语句 2020-06-24T07:38:58.302490-00:00 391 Query SET SESSION character_set_results = ‘utf8‘ 2020-06-24T07:38:58.302618-00:00 391 Query show fields from `sbtest1` <---------- 类似执行 desc sbtest1; 一样的效果，查看表结构 2020-06-24T07:38:58.303065-00:00 391 Query show fields from `sbtest1` 2020-06-24T07:38:58.303513-00:00 391 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1` <---------- 查询 sbtest1 表所有的数据 2020-06-24T07:38:58.971979-00:00 391 Query SET SESSION character_set_results = ‘binary‘ 2020-06-24T07:38:58.972338-00:00 391 Query use `testdb` 2020-06-24T07:38:58.972539-00:00 391 Query select @@collation_database <---------- 查询当前数据库级别的字符集校对规则 2020-06-24T07:38:58.972787-00:00 391 Query SHOW TRIGGERS LIKE ‘sbtest1‘ <---------- 查看 sbtest1 表是否存在触发器需要备份 （ 参数 --triggers 的作用 ） 2020-06-24T07:38:58.973504-00:00 391 Query SET SESSION character_set_results = ‘utf8‘ 2020-06-24T07:38:58.973643-00:00 391 Query ROLLBACK TO SAVEPOINT sp <---------- 回滚到保存 sp 状态的时间 2020-06-24T07:38:58.973820-00:00 391 Query show table status like ‘sbtest2‘ <---------- 查询 sbtest2 表元数据信息 2020-06-24T07:38:58.974130-00:00 391 Query SET SQL_QUOTE_SHOW_CREATE=1 <---------- 设置导出后建表语句对表名、字段名加反引号 https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html 2020-06-24T07:38:58.974256-00:00 391 Query SET SESSION character_set_results = ‘binary‘ 2020-06-24T07:38:58.974371-00:00 391 Query show create table `sbtest2` <---------- 查询 sbtest2 表的建表语句 2020-06-24T07:38:58.974551-00:00 391 Query SET SESSION character_set_results = ‘utf8‘ 2020-06-24T07:38:58.974697-00:00 391 Query show fields from `sbtest2` 2020-06-24T07:38:58.975132-00:00 391 Query show fields from `sbtest2` 2020-06-24T07:38:58.975489-00:00 391 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest2` <---------- 查询 sbtest2 表所有的数据 2020-06-24T07:38:59.642175-00:00 391 Query SET SESSION character_set_results = ‘binary‘ 2020-06-24T07:38:59.642386-00:00 391 Query use `testdb` 2020-06-24T07:38:59.642531-00:00 391 Query select @@collation_database <---------- 查询当前数据库级别的字符集校对规则 2020-06-24T07:38:59.642737-00:00 391 Query SHOW TRIGGERS LIKE ‘sbtest2‘ <---------- 查看 sbtest2 表是否存在触发器需要备份 （ 参数 --triggers 的作用 ） 2020-06-24T07:38:59.643297-00:00 391 Query SET SESSION character_set_results = ‘utf8‘ 2020-06-24T07:38:59.643458-00:00 391 Query ROLLBACK TO SAVEPOINT sp <---------- 回滚到保存 sp 状态的时间 2020-06-24T07:38:59.643585-00:00 391 Query RELEASE SAVEPOINT sp <---------- 释放命名为 sp 的状态位点 2020-06-24T07:38:59.643730-00:00 391 Query use `testdb` 2020-06-24T07:38:59.643892-00:00 391 Query select @@collation_database 2020-06-24T07:38:59.644069-00:00 391 Query SET SESSION character_set_results = ‘binary‘ 2020-06-24T07:38:59.644244-00:00 391 Query SHOW FUNCTION STATUS WHERE Db = ‘testdb‘
