前言：相信大家对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 Quit4.使用 --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参数叠加使用。
