MySQL dump备份时MySQL到底做了什么？
一、先决条件
- 备份时使用的mysqldump命令
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- 开启general_log命令
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)- 查询 testdb 库一共有多少张表
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| sbtest1 |
| sbtest2 |
+------------------+
2 rows in set (0.00 sec)二、分析general_log日志过程
- 开启
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- 备份完成后打开
general_log日志文件- 参考链接 SAVEPOINT ：https://dev.mysql.com/doc/refman/5.7/en/savepoint.html
- 参考链接 SQL_QUOTE_SHOW_CREATE ：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html
[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