【MySQL】探测mysqldump详细过程

**前言：**相信大家对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.238083+08:00	   27 Query	show tables
2018-08-14T14:10:50.238489+08:00	   27 Query	LOCK TABLES `act_re_model` READ /*!32311 LOCAL */,`custom_api_info` READ /*!32311 LOCAL */,`custom_application_system` READ /*!32311 LOCAL */,`stud` READ /*!32311 LOCAL */,`students` READ /*!32311 LOCAL */
2018-08-14T14:10:50.238709+08:00	   27 Query	show table status like 'act\_re\_model'
2018-08-14T14:10:50.238999+08:00	   27 Query	SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:10:50.239181+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.239285+08:00	   27 Query	show create table `act_re_model`
2018-08-14T14:10:50.239497+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.239606+08:00	   27 Query	show fields from `act_re_model`
2018-08-14T14:10:50.240116+08:00	   27 Query	show fields from `act_re_model`
2018-08-14T14:10:50.240487+08:00	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `act_re_model`
2018-08-14T14:10:50.240693+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.240809+08:00	   27 Query	use `test_db`
2018-08-14T14:10:50.241090+08:00	   27 Query	select @@collation_database
2018-08-14T14:10:50.241384+08:00	   27 Query	SHOW TRIGGERS LIKE 'act\_re\_model'
2018-08-14T14:10:50.241813+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.242010+08:00	   27 Query	show table status like 'custom\_api\_info'
2018-08-14T14:10:50.242391+08:00	   27 Query	SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:10:50.242545+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.242705+08:00	   27 Query	show create table `custom_api_info`
2018-08-14T14:10:50.242888+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.243044+08:00	   27 Query	show fields from `custom_api_info`
2018-08-14T14:10:50.243541+08:00	   27 Query	show fields from `custom_api_info`
2018-08-14T14:10:50.244019+08:00	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `custom_api_info`
2018-08-14T14:10:50.244278+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.244439+08:00	   27 Query	use `test_db`
2018-08-14T14:10:50.244602+08:00	   27 Query	select @@collation_database
2018-08-14T14:10:50.244765+08:00	   27 Query	SHOW TRIGGERS LIKE 'custom\_api\_info'
2018-08-14T14:10:50.245215+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.245384+08:00	   27 Query	show table status like 'custom\_application\_system'
2018-08-14T14:10:50.245749+08:00	   27 Query	SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:10:50.245888+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.246032+08:00	   27 Query	show create table `custom_application_system`
2018-08-14T14:10:50.246215+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.246383+08:00	   27 Query	show fields from `custom_application_system`
2018-08-14T14:10:50.246816+08:00	   27 Query	show fields from `custom_application_system`
2018-08-14T14:10:50.247256+08:00	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `custom_application_system`
2018-08-14T14:10:50.247507+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.247670+08:00	   27 Query	use `test_db`
2018-08-14T14:10:50.247825+08:00	   27 Query	select @@collation_database
2018-08-14T14:10:50.247987+08:00	   27 Query	SHOW TRIGGERS LIKE 'custom\_application\_system'
2018-08-14T14:10:50.248474+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.248651+08:00	   27 Query	show table status like 'stud'
2018-08-14T14:10:50.248985+08:00	   27 Query	SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:10:50.249123+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.249291+08:00	   27 Query	show create table `stud`
2018-08-14T14:10:50.249460+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.249632+08:00	   27 Query	show fields from `stud`
2018-08-14T14:10:50.250060+08:00	   27 Query	show fields from `stud`
2018-08-14T14:10:50.250509+08:00	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `stud`
2018-08-14T14:10:50.250764+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.250906+08:00	   27 Query	use `test_db`
2018-08-14T14:10:50.251064+08:00	   27 Query	select @@collation_database
2018-08-14T14:10:50.251239+08:00	   27 Query	SHOW TRIGGERS LIKE 'stud'
2018-08-14T14:10:50.251662+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.251808+08:00	   27 Query	show table status like 'students'
2018-08-14T14:10:50.252120+08:00	   27 Query	SET SQL_QUOTE_SHOW_CREATE=1
2018-08-14T14:10:50.252293+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.252428+08:00	   27 Query	show create table `students`
2018-08-14T14:10:50.252595+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.252742+08:00	   27 Query	show fields from `students`
2018-08-14T14:10:50.253165+08:00	   27 Query	show fields from `students`
2018-08-14T14:10:50.253566+08:00	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `students`
2018-08-14T14:10:50.253783+08:00	   27 Query	SET SESSION character_set_results = 'binary'
2018-08-14T14:10:50.253894+08:00	   27 Query	use `test_db`
2018-08-14T14:10:50.254023+08:00	   27 Query	select @@collation_database
2018-08-14T14:10:50.254189+08:00	   27 Query	SHOW TRIGGERS LIKE 'students'
2018-08-14T14:10:50.254589+08:00	   27 Query	SET SESSION character_set_results = 'utf8'
2018-08-14T14:10:50.254719+08:00	   27 Query	LOCK TABLES mysql.event READ
2018-08-14T14:10:50.254958+08:00	   27 Query	show events
2018-08-14T14:10:50.255413+08:00	   27 Query	UNLOCK TABLES
2018-08-14T14:10:50.255528+08:00	   27 Query	LOCK TABLES mysql.proc READ
2018-08-14T14:10:50.255705+08:00	   27 Query	use `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-