mysqldump的內部實現原理
我們可以透過開啟general log，檢視mysqldump全庫備份時執行的命令來了解mysqldump背後的原理。
開啟general log
mysql> set global general_log=on;
其中，general log的存放路徑可透過以下命令檢視
mysql> show variables like '%general_log_file%';
總結
1、--master-data產生如下，會導致mysqldump期間無法執行DDL和DML，而且執行mysqldump之前表有鎖，則mysqldump會等待，匯出結果是發生mysqldump這一刻的資料
FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK和SHOW MASTER STATUS
1、--master-data產生如下，會導致mysqldump期間無法執行DDL和DML，而且執行mysqldump之前表有鎖，則mysqldump會等待，匯出結果是發生mysqldump這一刻的資料
FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK和SHOW MASTER STATUS
2、--lock-all-tables產生如下，會導致mysqldump期間無法執行DDL和DML，而且執行mysqldump之前表有鎖，則mysqldump會等待，匯出結果是發生mysqldump這一刻的資料
FLUSH TABLES和FLUSH TABLES WITH READ LOCK
FLUSH TABLES和FLUSH TABLES WITH READ LOCK
3、--master-data和--lock-all-tables同時使用時，效果和--master-data一致
4、--single-transaction產生如下，mysqldump期間不影響DML和DDL，但是mysqldump本身可能受DDL影響而報錯退出，mysqldump之前表有鎖，mysqldump也不會等待，匯出結果是發生mysqldump這一刻的資料
UNLOCK TABLES，SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，SAVEPOINT，ROLLBACK TO SAVEPOINT，RELEASE SAVEPOINT
UNLOCK TABLES，SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，SAVEPOINT，ROLLBACK TO SAVEPOINT，RELEASE SAVEPOINT
5、--skip-lock-tables不產生任何LOCK，mysqldump期間不影響DML和DDL，但是mysqldump本身可能受DDL影響而報錯退出，mysqldump之前表有鎖，mysqldump也不會等待，匯出結果不是mysqldump這一刻的資料，而是導那張表的開始時刻就是該表的資料(比如導A表是8:00,則匯出過程要一個小時，匯出結果也是8:00的資料，導B表是9:00,則匯出過程要一個小時，B的匯出結果也是9:00的資料)，和oracle 的expdp相似。
6、預設匯出產生如下，會導致mysqldump期間,正在執行匯出的庫無法執行DDL和DML，沒有在執行匯出的庫可以執行DDL和DML，mysqldump之前表有鎖，mysqldump也不會等待，匯出結果不是發生mysqldump這一刻的資料，而是導那個庫的開始時刻就是那個庫的資料(比如導A庫是8:00,則匯出過程要一個小時，A的匯出結果也是8:00的資料，導B庫是9:00,則匯出過程要一個小時，B的匯出結果也是9:00的資料)。
LOCK TABLES `table1` READ /*!32311 LOCAL */,`table2` READ /*!32311 LOCAL */
UNLOCK TABLES
LOCK TABLES `table1` READ /*!32311 LOCAL */,`table2` READ /*!32311 LOCAL */
UNLOCK TABLES
7、生成建庫語句，生成建表語句，備份的核心語句（該語句查詢到表table1的所有資料，在備份檔案中會生成相應的insert語句。其中SQL_NO_CACHE的作用是查詢的結果並不會快取到查詢快取中），分別如下
SHOW CREATE DATABASE IF NOT EXISTS `test1`
show create table `table1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
SHOW CREATE DATABASE IF NOT EXISTS `test1`
show create table `table1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
--master-data日誌如下
有FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK和SHOW MASTER STATUS
沒有任何UNLOCK
mysqldump --master-data=2 --databases test1 > /home/test1_2.sql
有FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK和SHOW MASTER STATUS
沒有任何UNLOCK
mysqldump --master-data=2 --databases test1 > /home/test1_2.sql
2019-01-06T06:29:59.361208Z 4 Connect root@localhost on using Socket
2019-01-06T06:29:59.361337Z 4 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T06:29:59.361434Z 4 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T06:29:59.361558Z 4 Query FLUSH /*!40101 LOCAL */ TABLES
2019-01-06T06:29:59.369450Z 4 Query FLUSH TABLES WITH READ LOCK
2019-01-06T06:29:59.369580Z 4 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T06:29:59.372284Z 4 Query SHOW MASTER STATUS
2019-01-06T06:29:59.372533Z 4 Query SELECT ...
...
2019-01-06T06:29:59.375795Z 4 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T06:29:59.378605Z 4 Init DB test1
2019-01-06T06:29:59.378670Z 4 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T06:29:59.378767Z 4 Query show tables
2019-01-06T06:29:59.379070Z 4 Query show table status like 'table1'
2019-01-06T06:29:59.379457Z 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:29:59.379555Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.379649Z 4 Query show create table `table1`
2019-01-06T06:29:59.379778Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.379940Z 4 Query show fields from `table1`
2019-01-06T06:29:59.380463Z 4 Query show fields from `table1`
2019-01-06T06:29:59.380933Z 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T06:29:59.381225Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.381323Z 4 Query use `test1`
2019-01-06T06:29:59.381416Z 4 Query select @@collation_database
2019-01-06T06:29:59.381546Z 4 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T06:29:59.382209Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.382309Z 4 Query show table status like 'table2'
2019-01-06T06:29:59.382597Z 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:29:59.382670Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.382746Z 4 Query show create table `table2`
2019-01-06T06:29:59.382848Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.382983Z 4 Query show fields from `table2`
2019-01-06T06:29:59.383385Z 4 Query show fields from `table2`
2019-01-06T06:29:59.383782Z 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T06:29:59.384125Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.384223Z 4 Query use `test1`
2019-01-06T06:29:59.384312Z 4 Query select @@collation_database
2019-01-06T06:29:59.384430Z 4 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T06:29:59.385032Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.420821Z 4 Quit
2019-01-06T06:29:59.361337Z 4 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T06:29:59.361434Z 4 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T06:29:59.361558Z 4 Query FLUSH /*!40101 LOCAL */ TABLES
2019-01-06T06:29:59.369450Z 4 Query FLUSH TABLES WITH READ LOCK
2019-01-06T06:29:59.369580Z 4 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T06:29:59.372284Z 4 Query SHOW MASTER STATUS
2019-01-06T06:29:59.372533Z 4 Query SELECT ...
...
2019-01-06T06:29:59.375795Z 4 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T06:29:59.378605Z 4 Init DB test1
2019-01-06T06:29:59.378670Z 4 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T06:29:59.378767Z 4 Query show tables
2019-01-06T06:29:59.379070Z 4 Query show table status like 'table1'
2019-01-06T06:29:59.379457Z 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:29:59.379555Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.379649Z 4 Query show create table `table1`
2019-01-06T06:29:59.379778Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.379940Z 4 Query show fields from `table1`
2019-01-06T06:29:59.380463Z 4 Query show fields from `table1`
2019-01-06T06:29:59.380933Z 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T06:29:59.381225Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.381323Z 4 Query use `test1`
2019-01-06T06:29:59.381416Z 4 Query select @@collation_database
2019-01-06T06:29:59.381546Z 4 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T06:29:59.382209Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.382309Z 4 Query show table status like 'table2'
2019-01-06T06:29:59.382597Z 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:29:59.382670Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.382746Z 4 Query show create table `table2`
2019-01-06T06:29:59.382848Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.382983Z 4 Query show fields from `table2`
2019-01-06T06:29:59.383385Z 4 Query show fields from `table2`
2019-01-06T06:29:59.383782Z 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T06:29:59.384125Z 4 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:29:59.384223Z 4 Query use `test1`
2019-01-06T06:29:59.384312Z 4 Query select @@collation_database
2019-01-06T06:29:59.384430Z 4 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T06:29:59.385032Z 4 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:29:59.420821Z 4 Quit
--single-transaction日誌如下
沒有FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK
只有UNLOCK TABLES，SAVEPOINT，ROLLBACK TO SAVEPOINT，RELEASE SAVEPOINT
mysqldump --single-transaction --databases test1 > /home/test1_2.sql
2019-01-06T06:16:52.542170Z 8 Connect root@localhost on using Socket
2019-01-06T06:16:52.542314Z 8 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T06:16:52.542433Z 8 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T06:16:52.542593Z 8 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-01-06T06:16:52.542674Z 8 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2019-01-06T06:16:52.542794Z 8 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T06:16:52.545877Z 8 Query UNLOCK TABLES
2019-01-06T06:16:52.546158Z 8 Query SELECT...
...
2019-01-06T06:16:52.550745Z 8 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T06:16:52.553711Z 8 Init DB test1
2019-01-06T06:16:52.553793Z 8 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T06:16:52.553896Z 8 Query SAVEPOINT sp
2019-01-06T06:16:52.554040Z 8 Query show tables
2019-01-06T06:16:52.554281Z 8 Query show table status like 'table1'
2019-01-06T06:16:52.554665Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:16:52.554766Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.554862Z 8 Query show create table `table1`
2019-01-06T06:16:52.555144Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.555263Z 8 Query show fields from `table1`
2019-01-06T06:16:52.555766Z 8 Query show fields from `table1`
2019-01-06T06:16:52.556323Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T06:16:52.556585Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.556682Z 8 Query use `test1`
2019-01-06T06:16:52.556776Z 8 Query select @@collation_database
2019-01-06T06:16:52.556908Z 8 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T06:16:52.557623Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.557709Z 8 Query ROLLBACK TO SAVEPOINT sp
2019-01-06T06:16:52.557793Z 8 Query show table status like 'table2'
2019-01-06T06:16:52.558118Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:16:52.558191Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.558264Z 8 Query show create table `table2`
2019-01-06T06:16:52.558391Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.558481Z 8 Query show fields from `table2`
2019-01-06T06:16:52.558904Z 8 Query show fields from `table2`
2019-01-06T06:16:52.559413Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T06:16:52.559580Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.559653Z 8 Query use `test1`
2019-01-06T06:16:52.559726Z 8 Query select @@collation_database
2019-01-06T06:16:52.559858Z 8 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T06:16:52.560307Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.560378Z 8 Query ROLLBACK TO SAVEPOINT sp
2019-01-06T06:16:52.560465Z 8 Query RELEASE SAVEPOINT sp
2019-01-06T06:16:52.658772Z 8 Quit
2019-01-06T06:16:52.542314Z 8 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T06:16:52.542433Z 8 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T06:16:52.542593Z 8 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-01-06T06:16:52.542674Z 8 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2019-01-06T06:16:52.542794Z 8 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T06:16:52.545877Z 8 Query UNLOCK TABLES
2019-01-06T06:16:52.546158Z 8 Query SELECT...
...
2019-01-06T06:16:52.550745Z 8 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T06:16:52.553711Z 8 Init DB test1
2019-01-06T06:16:52.553793Z 8 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T06:16:52.553896Z 8 Query SAVEPOINT sp
2019-01-06T06:16:52.554040Z 8 Query show tables
2019-01-06T06:16:52.554281Z 8 Query show table status like 'table1'
2019-01-06T06:16:52.554665Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:16:52.554766Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.554862Z 8 Query show create table `table1`
2019-01-06T06:16:52.555144Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.555263Z 8 Query show fields from `table1`
2019-01-06T06:16:52.555766Z 8 Query show fields from `table1`
2019-01-06T06:16:52.556323Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T06:16:52.556585Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.556682Z 8 Query use `test1`
2019-01-06T06:16:52.556776Z 8 Query select @@collation_database
2019-01-06T06:16:52.556908Z 8 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T06:16:52.557623Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.557709Z 8 Query ROLLBACK TO SAVEPOINT sp
2019-01-06T06:16:52.557793Z 8 Query show table status like 'table2'
2019-01-06T06:16:52.558118Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:16:52.558191Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.558264Z 8 Query show create table `table2`
2019-01-06T06:16:52.558391Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.558481Z 8 Query show fields from `table2`
2019-01-06T06:16:52.558904Z 8 Query show fields from `table2`
2019-01-06T06:16:52.559413Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T06:16:52.559580Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:16:52.559653Z 8 Query use `test1`
2019-01-06T06:16:52.559726Z 8 Query select @@collation_database
2019-01-06T06:16:52.559858Z 8 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T06:16:52.560307Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:16:52.560378Z 8 Query ROLLBACK TO SAVEPOINT sp
2019-01-06T06:16:52.560465Z 8 Query RELEASE SAVEPOINT sp
2019-01-06T06:16:52.658772Z 8 Quit
--skip-lock-tables日誌如下
沒有FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK
也沒有任何UNLOCK
mysqldump --skip-lock-tables --databases test2 > /home/test1_2.sql
2019-01-03T14:35:41.956739Z 7 Connect root@localhost on using Socket
2019-01-03T14:35:41.956875Z 7 Query /*!40100 SET @@SQL_MODE='' */
2019-01-03T14:35:41.956976Z 7 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-03T14:35:41.957122Z 7 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-03T14:35:41.959794Z 7 Query SELECT ...
...
2019-01-03T14:35:41.965124Z 7 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-03T14:35:41.967453Z 7 Init DB test2
2019-01-03T14:35:41.967557Z 7 Query SHOW CREATE DATABASE IF NOT EXISTS `test2`
2019-01-03T14:35:41.967659Z 7 Query show tables
2019-01-03T14:35:41.967871Z 7 Query show table status like 't1'
2019-01-03T14:35:41.968196Z 7 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:35:41.968279Z 7 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:35:41.968359Z 7 Query show create table `t1`
2019-01-03T14:35:41.968468Z 7 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:35:41.968690Z 7 Query show fields from `t1`
2019-01-03T14:35:41.969127Z 7 Query show fields from `t1`
2019-01-03T14:35:41.969589Z 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2019-01-03T14:35:41.969824Z 7 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:35:41.969906Z 7 Query use `test2`
2019-01-03T14:35:41.969986Z 7 Query select @@collation_database
2019-01-03T14:35:41.970098Z 7 Query SHOW TRIGGERS LIKE 't1'
2019-01-03T14:35:41.970753Z 7 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:35:41.970855Z 7 Query show table status like 't2'
2019-01-03T14:35:41.971246Z 7 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:35:41.971325Z 7 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:35:41.971404Z 7 Query show create table `t2`
2019-01-03T14:35:41.971671Z 7 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:35:41.971807Z 7 Query show fields from `t2`
2019-01-03T14:35:41.972269Z 7 Query show fields from `t2`
2019-01-03T14:35:41.972777Z 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
2019-01-03T14:35:41.956875Z 7 Query /*!40100 SET @@SQL_MODE='' */
2019-01-03T14:35:41.956976Z 7 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-03T14:35:41.957122Z 7 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-03T14:35:41.959794Z 7 Query SELECT ...
...
2019-01-03T14:35:41.965124Z 7 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-03T14:35:41.967453Z 7 Init DB test2
2019-01-03T14:35:41.967557Z 7 Query SHOW CREATE DATABASE IF NOT EXISTS `test2`
2019-01-03T14:35:41.967659Z 7 Query show tables
2019-01-03T14:35:41.967871Z 7 Query show table status like 't1'
2019-01-03T14:35:41.968196Z 7 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:35:41.968279Z 7 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:35:41.968359Z 7 Query show create table `t1`
2019-01-03T14:35:41.968468Z 7 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:35:41.968690Z 7 Query show fields from `t1`
2019-01-03T14:35:41.969127Z 7 Query show fields from `t1`
2019-01-03T14:35:41.969589Z 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2019-01-03T14:35:41.969824Z 7 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:35:41.969906Z 7 Query use `test2`
2019-01-03T14:35:41.969986Z 7 Query select @@collation_database
2019-01-03T14:35:41.970098Z 7 Query SHOW TRIGGERS LIKE 't1'
2019-01-03T14:35:41.970753Z 7 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:35:41.970855Z 7 Query show table status like 't2'
2019-01-03T14:35:41.971246Z 7 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:35:41.971325Z 7 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:35:41.971404Z 7 Query show create table `t2`
2019-01-03T14:35:41.971671Z 7 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:35:41.971807Z 7 Query show fields from `t2`
2019-01-03T14:35:41.972269Z 7 Query show fields from `t2`
2019-01-03T14:35:41.972777Z 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
預設備份日誌如下
沒有FLUSH /*!40101 LOCAL */ TABLES和FLUSH TABLES WITH READ LOCK
但是有LOCK TABLES，結尾有UNLOCK TABLES
mysqldump --databases test2 > /home/test1_2.sql
2019-01-03T14:38:19.276575Z 8 Connect root@localhost on using Socket
2019-01-03T14:38:19.276814Z 8 Query /*!40100 SET @@SQL_MODE='' */
2019-01-03T14:38:19.276918Z 8 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-03T14:38:19.277065Z 8 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-03T14:38:19.279690Z 8 Query SELECT ...
...
2019-01-03T14:38:19.285033Z 8 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-03T14:38:19.287392Z 8 Init DB test2
2019-01-03T14:38:19.287461Z 8 Query SHOW CREATE DATABASE IF NOT EXISTS `test2`
2019-01-03T14:38:19.287559Z 8 Query show tables
2019-01-03T14:38:19.287886Z 8 Query LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */,`t3` READ /*!32311 LOCAL */,`t4` READ /*!32311 LOCAL */,`t7` READ /*!32311 LOCAL */
2019-01-03T14:38:19.288043Z 8 Query show table status like 't1'
2019-01-03T14:38:19.288403Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:38:19.288488Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:38:19.288569Z 8 Query show create table `t1`
2019-01-03T14:38:19.288714Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:38:19.288808Z 8 Query show fields from `t1`
2019-01-03T14:38:19.289236Z 8 Query show fields from `t1`
2019-01-03T14:38:19.289789Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2019-01-03T14:38:19.290008Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:38:19.290090Z 8 Query use `test2`
2019-01-03T14:38:19.290171Z 8 Query select @@collation_database
2019-01-03T14:38:19.290281Z 8 Query SHOW TRIGGERS LIKE 't1'
2019-01-03T14:38:19.290768Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:38:19.290869Z 8 Query show table status like 't2'
2019-01-03T14:38:19.291184Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:38:19.291260Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:38:19.291338Z 8 Query show create table `t2`
2019-01-03T14:38:19.291437Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:38:19.291528Z 8 Query show fields from `t2`
2019-01-03T14:38:19.292061Z 8 Query show fields from `t2`
2019-01-03T14:38:19.292454Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
2019-01-03T14:38:19.292612Z 8 Query SET SESSION character_set_results = 'binary'
...
2019-01-03T14:38:19.302719Z 8 Query UNLOCK TABLES
2019-01-03T14:38:19.306712Z 8 Quit
2019-01-03T14:38:19.276814Z 8 Query /*!40100 SET @@SQL_MODE='' */
2019-01-03T14:38:19.276918Z 8 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-03T14:38:19.277065Z 8 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-03T14:38:19.279690Z 8 Query SELECT ...
...
2019-01-03T14:38:19.285033Z 8 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-03T14:38:19.287392Z 8 Init DB test2
2019-01-03T14:38:19.287461Z 8 Query SHOW CREATE DATABASE IF NOT EXISTS `test2`
2019-01-03T14:38:19.287559Z 8 Query show tables
2019-01-03T14:38:19.287886Z 8 Query LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */,`t3` READ /*!32311 LOCAL */,`t4` READ /*!32311 LOCAL */,`t7` READ /*!32311 LOCAL */
2019-01-03T14:38:19.288043Z 8 Query show table status like 't1'
2019-01-03T14:38:19.288403Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:38:19.288488Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:38:19.288569Z 8 Query show create table `t1`
2019-01-03T14:38:19.288714Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:38:19.288808Z 8 Query show fields from `t1`
2019-01-03T14:38:19.289236Z 8 Query show fields from `t1`
2019-01-03T14:38:19.289789Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2019-01-03T14:38:19.290008Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:38:19.290090Z 8 Query use `test2`
2019-01-03T14:38:19.290171Z 8 Query select @@collation_database
2019-01-03T14:38:19.290281Z 8 Query SHOW TRIGGERS LIKE 't1'
2019-01-03T14:38:19.290768Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:38:19.290869Z 8 Query show table status like 't2'
2019-01-03T14:38:19.291184Z 8 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-03T14:38:19.291260Z 8 Query SET SESSION character_set_results = 'binary'
2019-01-03T14:38:19.291338Z 8 Query show create table `t2`
2019-01-03T14:38:19.291437Z 8 Query SET SESSION character_set_results = 'utf8'
2019-01-03T14:38:19.291528Z 8 Query show fields from `t2`
2019-01-03T14:38:19.292061Z 8 Query show fields from `t2`
2019-01-03T14:38:19.292454Z 8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
2019-01-03T14:38:19.292612Z 8 Query SET SESSION character_set_results = 'binary'
...
2019-01-03T14:38:19.302719Z 8 Query UNLOCK TABLES
2019-01-03T14:38:19.306712Z 8 Quit
--lock-all-tables
產生FLUSH TABLES和FLUSH TABLES WITH READ LOCK
沒有任何UNLOCK
mysqldump --lock-all-tables --databases test1 > /home/test1_2.sql
2019-01-06T06:47:17.614479Z 5 Connect root@localhost on using Socket
2019-01-06T06:47:17.614801Z 5 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T06:47:17.614922Z 5 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T06:47:17.615074Z 5 Query FLUSH TABLES
2019-01-06T06:47:17.620471Z 5 Query FLUSH TABLES WITH READ LOCK
2019-01-06T06:47:17.620629Z 5 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T06:47:17.623672Z 5 Query SELECT ...
...
2019-01-06T06:47:17.626996Z 5 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T06:47:17.629714Z 5 Init DB test1
2019-01-06T06:47:17.629798Z 5 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T06:47:17.629916Z 5 Query show tables
2019-01-06T06:47:17.630154Z 5 Query show table status like 'table1'
2019-01-06T06:47:17.630615Z 5 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:47:17.630702Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.630780Z 5 Query show create table `table1`
2019-01-06T06:47:17.630889Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.630979Z 5 Query show fields from `table1`
2019-01-06T06:47:17.631397Z 5 Query show fields from `table1`
2019-01-06T06:47:17.632023Z 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T06:47:17.632286Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.632384Z 5 Query use `test1`
2019-01-06T06:47:17.632479Z 5 Query select @@collation_database
2019-01-06T06:47:17.632668Z 5 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T06:47:17.633103Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.633202Z 5 Query show table status like 'table2'
2019-01-06T06:47:17.633489Z 5 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:47:17.633713Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.633811Z 5 Query show create table `table2`
2019-01-06T06:47:17.633937Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.634045Z 5 Query show fields from `table2`
2019-01-06T06:47:17.634532Z 5 Query show fields from `table2`
2019-01-06T06:47:17.635011Z 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T06:47:17.635177Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.635250Z 5 Query use `test1`
2019-01-06T06:47:17.635317Z 5 Query select @@collation_database
2019-01-06T06:47:17.635400Z 5 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T06:47:17.635962Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.639473Z 5 Quit
2019-01-06T06:47:17.614801Z 5 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T06:47:17.614922Z 5 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T06:47:17.615074Z 5 Query FLUSH TABLES
2019-01-06T06:47:17.620471Z 5 Query FLUSH TABLES WITH READ LOCK
2019-01-06T06:47:17.620629Z 5 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T06:47:17.623672Z 5 Query SELECT ...
...
2019-01-06T06:47:17.626996Z 5 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T06:47:17.629714Z 5 Init DB test1
2019-01-06T06:47:17.629798Z 5 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T06:47:17.629916Z 5 Query show tables
2019-01-06T06:47:17.630154Z 5 Query show table status like 'table1'
2019-01-06T06:47:17.630615Z 5 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:47:17.630702Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.630780Z 5 Query show create table `table1`
2019-01-06T06:47:17.630889Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.630979Z 5 Query show fields from `table1`
2019-01-06T06:47:17.631397Z 5 Query show fields from `table1`
2019-01-06T06:47:17.632023Z 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T06:47:17.632286Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.632384Z 5 Query use `test1`
2019-01-06T06:47:17.632479Z 5 Query select @@collation_database
2019-01-06T06:47:17.632668Z 5 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T06:47:17.633103Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.633202Z 5 Query show table status like 'table2'
2019-01-06T06:47:17.633489Z 5 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T06:47:17.633713Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.633811Z 5 Query show create table `table2`
2019-01-06T06:47:17.633937Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.634045Z 5 Query show fields from `table2`
2019-01-06T06:47:17.634532Z 5 Query show fields from `table2`
2019-01-06T06:47:17.635011Z 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T06:47:17.635177Z 5 Query SET SESSION character_set_results = 'binary'
2019-01-06T06:47:17.635250Z 5 Query use `test1`
2019-01-06T06:47:17.635317Z 5 Query select @@collation_database
2019-01-06T06:47:17.635400Z 5 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T06:47:17.635962Z 5 Query SET SESSION character_set_results = 'utf8'
2019-01-06T06:47:17.639473Z 5 Quit
--lock-all-tables --master-data同時使用
mysqldump --lock-all-tables --master-data=2 --databases test1 > /home/test1_2.sql
2019-01-06T07:11:08.806918Z 10 Connect root@localhost on using Socket
2019-01-06T07:11:08.807047Z 10 Query /*!40100 SET @@SQL_MODE='' */
2019-01-06T07:11:08.807174Z 10 Query /*!40103 SET TIME_ZONE='+00:00' */
2019-01-06T07:11:08.807392Z 10 Query FLUSH /*!40101 LOCAL */ TABLES
2019-01-06T07:11:08.807499Z 10 Query FLUSH TABLES WITH READ LOCK
2019-01-06T07:11:08.807598Z 10 Query SHOW VARIABLES LIKE 'gtid\_mode'
2019-01-06T07:11:08.810307Z 10 Query SHOW MASTER STATUS
2019-01-06T07:11:08.810538Z 10 Query SELECT ...
...
2019-01-06T07:11:08.813490Z 10 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2019-01-06T07:11:08.815880Z 10 Init DB test1
2019-01-06T07:11:08.815948Z 10 Query SHOW CREATE DATABASE IF NOT EXISTS `test1`
2019-01-06T07:11:08.816048Z 10 Query show tables
2019-01-06T07:11:08.816379Z 10 Query show table status like 'table1'
2019-01-06T07:11:08.816703Z 10 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T07:11:08.816787Z 10 Query SET SESSION character_set_results = 'binary'
2019-01-06T07:11:08.816868Z 10 Query show create table `table1`
2019-01-06T07:11:08.816977Z 10 Query SET SESSION character_set_results = 'utf8'
2019-01-06T07:11:08.817070Z 10 Query show fields from `table1`
2019-01-06T07:11:08.817533Z 10 Query show fields from `table1`
2019-01-06T07:11:08.817952Z 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table1`
2019-01-06T07:11:08.818288Z 10 Query SET SESSION character_set_results = 'binary'
2019-01-06T07:11:08.818376Z 10 Query use `test1`
2019-01-06T07:11:08.818455Z 10 Query select @@collation_database
2019-01-06T07:11:08.818567Z 10 Query SHOW TRIGGERS LIKE 'table1'
2019-01-06T07:11:08.818996Z 10 Query SET SESSION character_set_results = 'utf8'
2019-01-06T07:11:08.819080Z 10 Query show table status like 'table2'
2019-01-06T07:11:08.819376Z 10 Query SET SQL_QUOTE_SHOW_CREATE=1
2019-01-06T07:11:08.819439Z 10 Query SET SESSION character_set_results = 'binary'
2019-01-06T07:11:08.819503Z 10 Query show create table `table2`
2019-01-06T07:11:08.819589Z 10 Query SET SESSION character_set_results = 'utf8'
2019-01-06T07:11:08.819664Z 10 Query show fields from `table2`
2019-01-06T07:11:08.819993Z 10 Query show fields from `table2`
2019-01-06T07:11:08.820434Z 10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `table2`
2019-01-06T07:11:08.820578Z 10 Query SET SESSION character_set_results = 'binary'
2019-01-06T07:11:08.820644Z 10 Query use `test1`
2019-01-06T07:11:08.820707Z 10 Query select @@collation_database
2019-01-06T07:11:08.820789Z 10 Query SHOW TRIGGERS LIKE 'table2'
2019-01-06T07:11:08.821171Z 10 Query SET SESSION character_set_results = 'utf8'
2019-01-06T07:11:08.846442Z 10 Quit
SELECT ...
...
內如為
2019-01-01T06:16:52.546158Z 8 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 ('test1'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
2019-01-01T06:16:52.549144Z 8 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 ('test1')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2374624/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Java 阻塞佇列(BlockingQueue)的內部實現原理Java佇列BloC
- 快速理解Go陣列和切片的內部實現原理Go陣列
- gostring的內部實現Go
- 從原始碼的角度來談一談HashMap的內部實現原理原始碼HashMap
- mysqldump原理分析MySql
- 從 Flutter 原始碼看 InheritedWidget 內部實現原理 | 掘金技術徵文Flutter原始碼
- 夯實Java基礎系列8：深入理解Java內部類及其實現原理Java
- 夯實Java基礎系列18：深入理解Java內部類及其實現原理Java
- Object.create(..)和new(..)的內部實現Object
- kafka的內部實現、安裝和使用Kafka
- 精讀《JS 陣列的內部實現》JS陣列
- Kubernetes 內部元件工作原理元件
- 浮點數演算法的內部實現演算法
- 【譯】Go 切片：用法和內部實現Go
- mysqldump的最佳實踐MySql
- pinpoint-php-aop 內部原理PHP
- 譯—現代瀏覽器內部原理（第一部分）瀏覽器
- 匿名內部類方式實現執行緒的建立執行緒
- sql server 2005 資料修改的內部原理SQLServer
- 與你探索classnames模組內部實現
- 現代瀏覽器內部工作原理（附詳細流程圖）瀏覽器流程圖
- Spark SQL / Catalyst 內部原理 與 RBOSparkSQL
- MongoDB 儲存引擎與內部原理MongoDB儲存引擎
- 深入解析 oracle drop table內部原理Oracle
- Mysqldump實現mysql的master-slave主從複製MySqlAST
- 淺談內聯鉤取原理與實現
- 用匿名內部類實現 Java 同步回撥Java
- Vue實現內部元件輪播切換效果Vue元件
- async、await和generator函式內部原理AI函式
- Flink 核心元件 內部原理 多圖剖析元件
- 關於call, apply, bind方法的區別與內部實現APP
- 成品直播原始碼，實現在平臺內部的搜尋原始碼
- 5分鐘瞭解Redis的內部實現快速列表（quicklist）RedisUI
- 第三部分：Spdlog 日誌庫的實現原理
- iOS中atomic和nonatomic區別及內部實現iOS
- Category的實現原理Go
- Vitepress 的實現原理Vite
- synchronized 的實現原理synchronized