内部分析如何使用--single-transaction --master-data=2参数进行mysqldump单表备份

开启general_log:

mysql(mdba@localhost:(none) 01:08:55)>set globalgeneral_log=on;
查询OK,0行受影响（0.02秒）

mysql(mdba@localhost:(none) 01:09:07)>显示像'%general%'这样的变量;

|变量名 |价值|

|一般日志 |开 |开 | |
|通用日志文件 | /usr/local/mysql/data/localhost.log | /usr/local/mysql/data/localhost.log | br/>

mysql(mdba@localhost:(none) 01:31:43)>显示数据库;

|数据库                                                     | information_schema |
| bbb            |
|信用                   | |
|性能模式 |
|系统|
|测试|
|世界健康管理协会p |

集合中 12 行（0.05 秒）


mysql(mdba@localhost:(none) 01:31:46)>使用信用
读取表信息以完成表名和列名
您可以使用 -A 关闭此功能以更快地启动

数据库已更改

mysql( mdba@localhost:credit 01:32:02)>显示表格；

| Tables_in_credit |

| f_audit |
| f_audit_bak_20170504 |

2 rows in set (0.00 sec)



例如，要备份信用数据库下的 f_audit 表，请使用 - -single-transaction - -master-data=2 参数：

[root@localhost tmp]# mysqldump -umdba -pdsf0723 --single-transaction --master-data=2 Credit f_audit > /tmp /f_audit.sql



查看general_log：
[root@localhost tmp]# cat /usr/local/mysql/data/localhost.log
/opt/app/mysql/5.7.18/bin/mysqld，版本：5.7.18-log（MySQL 社区服务器（GPL））。开始于：
Tcp 端口：3306 Unix 套接字：/tmp/mysql.sock
时间 Id C命令参数
2017-08-31T05:09:09.189375Z 19513 查询显示诸如 '%gen%' 之类的变量
2017-08-31T05:09:24.603322Z 195 13 查询 SELECT DATABASE()< br/ >2017-08-31T05:09:24.604041Z 19513 初始化数据库信用
2017-08-31T05:09:24.605173Z 19513 查询显示数据库
2017-08-31T05:09:24。 605838Z19513 查询显示表
2017-08-31T05:09:24.605976Z 19513 字段列表 f_audit
2017-08-31T05:09:24.606416Z 19513 字段列表 f_audit_bak_20170504
2017-08 -31T05 : 09:26.575331Z 19513 查询显示表
2017-08-31T05:11:18.126829Z 19514 使用Socket连接mdba@localhost
2017-08-31T05:11:18.126927Z 19514 查询 /*! 40100 SET @@SQL_MODE='' */
2017-08-31T05:11:18.130352Z 19514 查询 /*!40103 SET TIME_ZONE='+00:00' */
2017-08-31T05 : 11:18.130450Z 19514 查询 FLUSH /*!40101 LOCAL */ 表
2017-08-31T05:11:18.140266Z 19514 查询带读锁的 FLUSH 表
2017-08-31T 05:11: 18.140318Z 19514 查询设置会话事务隔离级别可重复读取
2017-08-31T05:11:18.140355Z 19514 查询开始事务 /*!40100 具有一致的快照 */
2017-08-31T05:11:18.140417Z 19514 查询显示变量如'gtid\ _mode'
2017-08- 31T05:11:18.150840Z 19514 查询显示主状态
2017-08-31T05:11:18.168898Z 19514 查询解锁表
2017-08 -31T05:11 :18.181199Z 19514 查询选择日志文件_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 TABLE SPACE_NAME IN（选择与 INFORMATION_SCHEMA.PARTITIONS 中的 TABLESPACE_NAME 不同，其中 TABLE_SCHEMA='credit' 和 ('f_audit')) 中的 TABLE_NAME)) 按 LOGFILE_GROUP_NAME、FILE_NAME、ENGINE、TOTAL_EXTENTS、INITIAL_SIZE 进行分组，按 LOGFILE_GROUP_NAME 排序
2017-08-31T05:11:1 8.210240Z 19514 查询选择不同的表空间名称、文件名称、日志文件_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='credit' AND TABLE_NAME IN ('f_audit')) ORDER BY TABLESPACE_NAME, LOGFILE_ GROUP_NAME
2017-08-31T05:11:18.220 125Z 19514 查询 SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-08-31T05:11:18.221667Z 19514 初始化数据库信用
2017-08-31T05: 11:18.221712Z 19514 查询 SHOW TABLES LIKE 'f\_audit'
2017-08-31T05:11:18.221834Z 19514 查询 SAVEPOINT sp
2017-08-31T05:11:18.221887Z 19514 查询 show表状态如 'f\_audit'
2017-08-31T05:11:18.222054Z 19514 查询 SET SQL_QUOTE_SHOW_CREATE=1
2017-08-31T05:11:18.222100Z 19514 查询 SET SESSION character_set_results = 'binary '
2017-08-31T05:11:18.222142Z 19514 查询 show create table `f_audit`
2017-08-31T05:11:18.222231Z 19514 查询 SET SESSION character_set_result s = 'utf8'< br/ >2017-08-31T05:11:18.222287Z 19514 查询显示`f_audit`中的字段
2017-08-31T05:11:18.222614Z 19514 查询显示`f_audit`中的字段
2017 -08 -31T05:11:18.222940Z 19514 查询 SELECT /* !40001 SQL_NO_CACHE */ * FROM `f_audit`
2017-08-31T05:11:18.223074Z 19514 查询 SET SESSION character_set_res ults = 'binary'
2017 -08-31T05:11:18.223116Z 19514 查询使用 `credit`
2017-08-31T05:11:18.223165Z 19514 查询 select @@collat​​ion_database
2017-08-31T05:11:18.223224Z 19514 查询 SHOW TRIGGERS LIKE ' f\_audit'< br />2017-08-31T05:11:18.223465Z 19514 查询 SET SESSION character_set_results = 'utf8'
2017-08-31T05:11:18.223509Z 19514 查询回滚到 SAVEPO INT sp
2017-08- 31T05:11:18.223547Z 19514 Query RELEASE SAVEPOINT sp
2017-08-31T05:11:18.281441Z 19514 Quit


发现执行了FLUSH TABLES WITH READ LOCK :
2017-08-31T05:11:18.130450Z 19514 查询 FLUSH /*!40101 LOCAL */ 表
2017-08-31T05:11:18.140266Z 19514 查询带读锁的 FLUSH 表


通过进一步测试，发现备份mysqldump时，只使用--single-transaction而不使用--master-data=2参数，不会锁表。
不添加--master-data=2参数：
[root@localhost tmp]#mysqldump -umdba -pdsf0723 --single-transaction Credit f_audit > f_audit.sql


查看general_log：
2017-08-31T05:24:05.890881Z 19516 使用Socket连接mdba@ localhost
2017-08-31T05:24:05.890984Z 19516 Query /*!40100 SET @@SQL_MODE='' */
2017-08-31T05:24:05.891037Z 19516 查询 / *!40103 SET TIME_ZONE='+00:00' */
2017-08-31T05: 24:05.891478Z 19516 查询设置会话事务隔离级别可重复读取
2017-08-31T05:24:05.891520 Z 19516 查询开始事务 /*!40100 具有一致的快照 */
2017-08-31T0 5： 24:05.891589Z 19516 查询显示变量如 'gtid\_mode'
2017-08-31T05 :24: 05.893983Z 19516 查询解锁表
2017-08-31T05:24:05.894099Z 19516 查询选择 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（从 INFORMATION_SCH EMA.FILES WHERE FILE_TYPE 中选择不同的 LOGFILE_GROUP_NAME = '数据文件' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('f_audit'))) GROUP BY LOGFILE_GROUP_NAME、FILE_NAME、ENGINE、TOTAL_EXTENTS、INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2017 -08- 31T05 :24: 05.911788Z 19516 查询 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='credit' AND TABLE_NAME IN ('f_audit ')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2017-08-31T05:24:05.916870Z 19516 查询显示类似“ndbinfo\_version”的变量
2017-08-31T05:24:05.918150Z 19516 初始化数据库信用
2017 -08-31T05:24:05.918208Z 19516 查询显示类似“f\_audit”的表
2017-08-31T05:24:05.918336Z 19516 查询 SAVEPOINT sp
2017-08-31T05 :24:05.918390 Z 19516 查询显示表状态如 'f\_audit'
2017-08 -31T05：24：05.918558Z 19516查询集SQL_QUOTE_SHOW_CREATE = 1
2017-08-31T05：24：05.918603 Z 19516查询集会话character_set_results ='二进制'
2017-08-31T05：24：05 .918648Z 19516 查询显示创建表 `f_audit`
2017-08-31T05:24:05.918730Z 19516 查询 SET SES SION character_set_results = 'utf8'
2017-08-31T05:24:05.918786Z 19516 查询显示字段来自 `f_audit`
2017-08-31T05:24:05.919114Z 19516 查询显示来自 `f_audit`的字段
2017-08-31T05:24:05.919417Z 19516 查询 SELECT /* !40001 SQL_NO_CACHE */ * FROM `f_audit`
2017-08-31T05:24:05.919547Z 19516 查询 SET SESSION character_set_results = 'binary'
2017-08-31T05:24:05.919592Z 19516 查询使用 `Credit`
2017-08-31T05:24:05.919639Z 19516 查询 select @@collat​​ion_database
2017-08-31T05:24:05.919698Z 19516 查询 SHOW TRIGGERS LIKE 'f\_audit'
2017-08-31T05:24:05.919999Z 19516 查询 SET SESSION character_set_results = 'utf8'
2017-08-31T05:24:05.920064Z 19516 查询回滚到保存点 sp
2017-08-31T05: 24 :05.920107Z 19516 QueryRELEASE SAVEPOINT sp
2017-08-31T05:24:05.971293Z 19516 退出


发现使用读锁刷新表。

只有使用--master-data=2参数获取show master status，才会执行FLUSH TABLES WITH READ LOCK的全局锁。在业务低迷时期，mysql会很快获取全局锁，但是在业务高峰时期，数据库表很多。某些情况下不建议进行全局锁，
所以应该在业务低峰时期进行mysqldump备份，或者备份时直接去掉--master-data=2参数。

看完上面的内容，你是不是对 --single-transaction --master-data=2 参数的 mysqldump 单表备份执行过程的内部分析有了进一步的了解呢？如果您想了解更多知识或相关内容，请关注行业资讯频道。感谢您的支持。