【MySQL】探测mysqldump详细过程

MySQL技术

发布于 2019-09-08 21:11:04

8680

发布于 2019-09-08 21:11:04

前言：相信大家对mysqldump应该不陌生，但是大家对mysqldump的原理及备份过程真的熟悉吗？今天，我们一起来深入理解一下mysqldump的备份原理及备份过程以及不同参数产生的效果。

1.直接 mysqldump -uroot -pyourpass test_db > test_db.sql 备份产生的general_log如下：

2018-08-14T14:10:50.227254+08:00       27 Connect   root@localhost on  using Socket
2018-08-14T14:10:50.227454+08:00       27 Query /*!40100 SET @@SQL_MODE='' */
2018-08-14T14:10:50.227640+08:00       27 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-08-14T14:10:50.227857+08:00       27 Query SHOW VARIABLES LIKE 'gtid\_mode'
2018-08-14T14:10:50.232239+08:00       27 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 ('test_db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-08-14T14:10:50.234201+08:00       27 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 ('test_db')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-08-14T14:10:50.235122+08:00       27 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-08-14T14:10:50.237841+08:00       27 Init DB   test_db
2018-08-14T14:10:50.237980+08:00       27 Query SHOW CREATE DATABASE IF NOT EXISTS `test_db`
...
2018-08-14T14:10:50.255795+08:00       27 Query select @@collation_database
2018-08-14T14:10:50.255995+08:00       27 Query SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.256128+08:00       27 Query SHOW FUNCTION STATUS WHERE Db = 'test_db'
2018-08-14T14:10:50.258323+08:00       27 Query SHOW PROCEDURE STATUS WHERE Db = 'test_db'
2018-08-14T14:10:50.259955+08:00       27 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.260080+08:00       27 Query UNLOCK TABLES
2018-08-14T14:10:50.260222+08:00       27 Query UNLOCK TABLES
2018-08-14T14:10:50.260327+08:00       27 Quit

2.增加 single-transaction 参数即 mysqldump -uroot -pyourpass --single-transaction test_db > test_db.sql 备份产生的general_log如下：

2018-08-14T14:15:29.731380+08:00       29 Connect   root@localhost on  using Socket
2018-08-14T14:15:29.731586+08:00       29 Query /*!40100 SET @@SQL_MODE='' */
2018-08-14T14:15:29.731755+08:00       29 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-08-14T14:15:29.731948+08:00       29 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2018-08-14T14:15:29.732107+08:00       29 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2018-08-14T14:15:29.732314+08:00       29 Query SHOW VARIABLES LIKE 'gtid\_mode'
2018-08-14T14:15:29.735166+08:00       29 Query UNLOCK TABLES
2018-08-14T14:15:29.735549+08:00       29 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 ('test_db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-08-14T14:15:29.737219+08:00       29 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 ('test_db')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-08-14T14:15:29.738111+08:00       29 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-08-14T14:15:29.741207+08:00       29 Init DB   test_db
2018-08-14T14:15:29.741344+08:00       29 Query SHOW CREATE DATABASE IF NOT EXISTS `test_db`
2018-08-14T14:15:29.741458+08:00       29 Query SAVEPOINT sp
2018-08-14T14:15:29.741544+08:00       29 Query show tables
2018-08-14T14:15:29.741759+08:00       29 Query show table status like 'act\_re\_model'
2018-08-14T14:15:29.742037+08:00       29 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:15:29.742158+08:00       29 Query SET SESSION character_set_results = 'binary'
2018-08-14T14:15:29.742283+08:00       29 Query show create table `act_re_model`
2018-08-14T14:15:29.742399+08:00       29 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:15:29.742493+08:00       29 Query show fields from `act_re_model`
2018-08-14T14:15:29.742944+08:00       29 Query show fields from `act_re_model`
2018-08-14T14:15:29.743360+08:00       29 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `act_re_model`
2018-08-14T14:15:29.743614+08:00       29 Query SET SESSION character_set_results = 'binary'
2018-08-14T14:15:29.743780+08:00       29 Query use `test_db`
2018-08-14T14:15:29.743941+08:00       29 Query select @@collation_database
2018-08-14T14:15:29.744161+08:00       29 Query SHOW TRIGGERS LIKE 'act\_re\_model'
2018-08-14T14:15:29.744657+08:00       29 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:15:29.744811+08:00       29 Query ROLLBACK TO SAVEPOINT sp
...
2018-08-14T14:15:29.756946+08:00       29 Query select @@collation_database
2018-08-14T14:15:29.757097+08:00       29 Query SHOW TRIGGERS LIKE 'students'
2018-08-14T14:15:29.757513+08:00       29 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:15:29.757647+08:00       29 Query ROLLBACK TO SAVEPOINT sp
2018-08-14T14:15:29.757771+08:00       29 Query RELEASE SAVEPOINT sp
2018-08-14T14:15:29.757897+08:00       29 Query show events
2018-08-14T14:15:29.758400+08:00       29 Query use `test_db`
2018-08-14T14:15:29.758544+08:00       29 Query select @@collation_database
2018-08-14T14:15:29.758682+08:00       29 Query SET SESSION character_set_results = 'binary'
2018-08-14T14:15:29.758813+08:00       29 Query SHOW FUNCTION STATUS WHERE Db = 'test_db'
2018-08-14T14:15:29.761003+08:00       29 Query SHOW PROCEDURE STATUS WHERE Db = 'test_db'
2018-08-14T14:15:29.762609+08:00       29 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:15:29.763200+08:00       29 Quit

3.使用 --master-data=2 及--single-transaction 参数即 mysqldump -uroot -pyourpass --master-data=2 --single-transaction test_db > test_db.sql 备份产生的general_log如下：

2018-08-14T14:30:06.020562+08:00       33 Connect   root@localhost on  using Socket
2018-08-14T14:30:06.020733+08:00       33 Query /*!40100 SET @@SQL_MODE='' */
2018-08-14T14:30:06.020902+08:00       33 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-08-14T14:30:06.021082+08:00       33 Query FLUSH /*!40101 LOCAL */ TABLES
2018-08-14T14:30:06.023110+08:00       33 Query FLUSH TABLES WITH READ LOCK
2018-08-14T14:30:06.023302+08:00       33 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2018-08-14T14:30:06.023428+08:00       33 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2018-08-14T14:30:06.023580+08:00       33 Query SHOW VARIABLES LIKE 'gtid\_mode'
2018-08-14T14:30:06.026820+08:00       33 Query SHOW MASTER STATUS
2018-08-14T14:30:06.026952+08:00       33 Query UNLOCK TABLES
2018-08-14T14:30:06.027163+08:00       33 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 ('test_db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-08-14T14:30:06.029717+08:00       33 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 ('test_db')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-08-14T14:30:06.030799+08:00       33 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-08-14T14:30:06.033857+08:00       33 Init DB   test_db
2018-08-14T14:30:06.033985+08:00       33 Query SHOW CREATE DATABASE IF NOT EXISTS `test_db`
2018-08-14T14:30:06.034125+08:00       33 Query SAVEPOINT sp
2018-08-14T14:30:06.034239+08:00       33 Query show tables
2018-08-14T14:30:06.034457+08:00       33 Query show table status like 'act\_re\_model'
2018-08-14T14:30:06.034770+08:00       33 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:30:06.034888+08:00       33 Query SET SESSION character_set_results = 'binary'
...
2018-08-14T14:30:06.048531+08:00       33 Query select @@collation_database
2018-08-14T14:30:06.048675+08:00       33 Query SHOW TRIGGERS LIKE 'students'
2018-08-14T14:30:06.049066+08:00       33 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:30:06.049221+08:00       33 Query ROLLBACK TO SAVEPOINT sp
2018-08-14T14:30:06.049344+08:00       33 Query RELEASE SAVEPOINT sp
2018-08-14T14:30:06.049467+08:00       33 Query show events
2018-08-14T14:30:06.050063+08:00       33 Query use `test_db`
2018-08-14T14:30:06.050220+08:00       33 Query select @@collation_database
2018-08-14T14:30:06.050364+08:00       33 Query SET SESSION character_set_results = 'binary'
2018-08-14T14:30:06.050503+08:00       33 Query SHOW FUNCTION STATUS WHERE Db = 'test_db'
2018-08-14T14:30:06.052734+08:00       33 Query SHOW PROCEDURE STATUS WHERE Db = 'test_db'
2018-08-14T14:30:06.054303+08:00       33 Query SET SESSION character_set_results = 'utf8'
2018-08-14T14:30:06.054808+08:00       33 Quit

4.使用 --skip-lock-tables 参数即 mysqldump -uroot -pyourpass --skip-lock-tables test_db > test_db.sql 备份产生的general_log如下：

2018-08-14T15:20:57.546924+08:00       37 Connect   root@localhost on  using Socket
2018-08-14T15:20:57.547051+08:00       37 Query /*!40100 SET @@SQL_MODE='' */
2018-08-14T15:20:57.547310+08:00       37 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-08-14T15:20:57.547548+08:00       37 Query SHOW VARIABLES LIKE 'gtid\_mode'
2018-08-14T15:20:57.550374+08:00       37 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 ('test_db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-08-14T15:20:57.551896+08:00       37 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 ('test_db')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-08-14T15:20:57.552833+08:00       37 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-08-14T15:20:57.555707+08:00       37 Init DB   test_db
2018-08-14T15:20:57.555815+08:00       37 Query SHOW CREATE DATABASE IF NOT EXISTS `test_db`
2018-08-14T15:20:57.555956+08:00       37 Query show tables
2018-08-14T15:20:57.556230+08:00       37 Query show table status like 'act\_re\_model'
2018-08-14T15:20:57.556516+08:00       37 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T15:20:57.556617+08:00       37 Query SET SESSION character_set_results = 'binary'
...
2018-08-14T15:20:57.568879+08:00       37 Query select @@collation_database
2018-08-14T15:20:57.569009+08:00       37 Query SHOW TRIGGERS LIKE 'students'
2018-08-14T15:20:57.569401+08:00       37 Query SET SESSION character_set_results = 'utf8'
2018-08-14T15:20:57.569533+08:00       37 Query show events
2018-08-14T15:20:57.569960+08:00       37 Query use `test_db`
2018-08-14T15:20:57.570082+08:00       37 Query select @@collation_database
2018-08-14T15:20:57.570248+08:00       37 Query SET SESSION character_set_results = 'binary'
2018-08-14T15:20:57.570394+08:00       37 Query SHOW FUNCTION STATUS WHERE Db = 'test_db'
2018-08-14T15:20:57.572401+08:00       37 Query SHOW PROCEDURE STATUS WHERE Db = 'test_db'
2018-08-14T15:20:57.573994+08:00       37 Query SET SESSION character_set_results = 'utf8'
2018-08-14T15:20:57.574450+08:00       37 Quit

5.使用 --lock-all-tables 参数即 mysqldump -uroot -pyourpass --lock-all-tables test_db > test_db.sql 备份产生的general_log如下：

2018-08-16T15:27:44.653042+08:00       12 Connect   root@localhost on  using Socket
2018-08-16T15:27:44.653378+08:00       12 Query /*!40100 SET @@SQL_MODE='' */
2018-08-16T15:27:44.653576+08:00       12 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-08-16T15:27:44.653782+08:00       12 Query FLUSH TABLES
2018-08-16T15:27:44.656576+08:00       12 Query FLUSH TABLES WITH READ LOCK
2018-08-16T15:27:44.656779+08:00       12 Query SHOW VARIABLES LIKE 'gtid\_mode'
2018-08-16T15:27:44.661157+08:00       12 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 ('test_db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-08-16T15:27:44.664158+08:00       12 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 ('test_db')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-08-16T15:27:44.665119+08:00       12 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-08-16T15:27:44.667725+08:00       12 Init DB   test_db
2018-08-16T15:27:44.667857+08:00       12 Query SHOW CREATE DATABASE IF NOT EXISTS `test_db`
2018-08-16T15:27:44.668053+08:00       12 Query show tables
2018-08-16T15:27:44.668384+08:00       12 Query show table status like 'ac'
2018-08-16T15:27:44.668738+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.668888+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.669050+08:00       12 Query show create table `ac`
2018-08-16T15:27:44.669244+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.669408+08:00       12 Query show fields from `ac`
2018-08-16T15:27:44.669844+08:00       12 Query show fields from `ac`
2018-08-16T15:27:44.670334+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `ac`
2018-08-16T15:27:44.670605+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.670750+08:00       12 Query use `test_db`
2018-08-16T15:27:44.670900+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.671088+08:00       12 Query SHOW TRIGGERS LIKE 'ac'
2018-08-16T15:27:44.671556+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.671718+08:00       12 Query show table status like 'act\_re\_model'
2018-08-16T15:27:44.672046+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.672201+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.672356+08:00       12 Query show create table `act_re_model`
2018-08-16T15:27:44.672525+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.672680+08:00       12 Query show fields from `act_re_model`
2018-08-16T15:27:44.673126+08:00       12 Query show fields from `act_re_model`
2018-08-16T15:27:44.673618+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `act_re_model`
2018-08-16T15:27:44.673893+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.674051+08:00       12 Query use `test_db`
2018-08-16T15:27:44.674223+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.674389+08:00       12 Query SHOW TRIGGERS LIKE 'act\_re\_model'
2018-08-16T15:27:44.674825+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.674997+08:00       12 Query show table status like 'custom\_api\_info'
2018-08-16T15:27:44.675333+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.675487+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.675623+08:00       12 Query show create table `custom_api_info`
2018-08-16T15:27:44.675812+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.675972+08:00       12 Query show fields from `custom_api_info`
2018-08-16T15:27:44.676422+08:00       12 Query show fields from `custom_api_info`
2018-08-16T15:27:44.676854+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `custom_api_info`
2018-08-16T15:27:44.677085+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.677246+08:00       12 Query use `test_db`
2018-08-16T15:27:44.677390+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.677541+08:00       12 Query SHOW TRIGGERS LIKE 'custom\_api\_info'
2018-08-16T15:27:44.677951+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.678113+08:00       12 Query show table status like 'custom\_application\_system'
2018-08-16T15:27:44.678429+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.678564+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.678700+08:00       12 Query show create table `custom_application_system`
2018-08-16T15:27:44.678891+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.679052+08:00       12 Query show fields from `custom_application_system`
2018-08-16T15:27:44.679467+08:00       12 Query show fields from `custom_application_system`
2018-08-16T15:27:44.679871+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `custom_application_system`
2018-08-16T15:27:44.680096+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.680257+08:00       12 Query use `test_db`
2018-08-16T15:27:44.680401+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.680552+08:00       12 Query SHOW TRIGGERS LIKE 'custom\_application\_system'
2018-08-16T15:27:44.680973+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.681121+08:00       12 Query show table status like 'stud'
2018-08-16T15:27:44.681440+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.681569+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.681705+08:00       12 Query show create table `stud`
2018-08-16T15:27:44.681864+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.682024+08:00       12 Query show fields from `stud`
2018-08-16T15:27:44.682431+08:00       12 Query show fields from `stud`
2018-08-16T15:27:44.682824+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `stud`
2018-08-16T15:27:44.683069+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.683229+08:00       12 Query use `test_db`
2018-08-16T15:27:44.683373+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.683522+08:00       12 Query SHOW TRIGGERS LIKE 'stud'
2018-08-16T15:27:44.683954+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.684121+08:00       12 Query show table status like 'students'
2018-08-16T15:27:44.684439+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.684568+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.684703+08:00       12 Query show create table `students`
2018-08-16T15:27:44.684860+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.685018+08:00       12 Query show fields from `students`
2018-08-16T15:27:44.685480+08:00       12 Query show fields from `students`
2018-08-16T15:27:44.685876+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `students`
2018-08-16T15:27:44.686108+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.686265+08:00       12 Query use `test_db`
2018-08-16T15:27:44.686409+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.686563+08:00       12 Query SHOW TRIGGERS LIKE 'students'
2018-08-16T15:27:44.686947+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.687098+08:00       12 Query show table status like 'upper\_table'
2018-08-16T15:27:44.687394+08:00       12 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-08-16T15:27:44.687521+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.687648+08:00       12 Query show create table `upper_table`
2018-08-16T15:27:44.687796+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.687933+08:00       12 Query show fields from `upper_table`
2018-08-16T15:27:44.688339+08:00       12 Query show fields from `upper_table`
2018-08-16T15:27:44.688710+08:00       12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `upper_table`
2018-08-16T15:27:44.688906+08:00       12 Query SET SESSION character_set_results = 'binary'
2018-08-16T15:27:44.689046+08:00       12 Query use `test_db`
2018-08-16T15:27:44.689229+08:00       12 Query select @@collation_database
2018-08-16T15:27:44.689386+08:00       12 Query SHOW TRIGGERS LIKE 'upper\_table'
2018-08-16T15:27:44.689890+08:00       12 Query SET SESSION character_set_results = 'utf8'
2018-08-16T15:27:44.691705+08:00       12 Quit

因推文字数限制只截取了部分日志详细参考我的博客：https://www.jianshu.com/p/c98f7ea09d44

总结： 日常备份可使用 --skip-lock-tables 或 --single-transaction 参数；若要做从库需记录binlog位置则推荐使用 --master-data=2 --single-transaction参数叠加使用。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2018-12-24，如有侵权请联系 cloudcommunity@tencent.com 删除

sql