mysqldump原理分析
今天學習了下mysqldump原理，具體的結論如下：
1、mysqldump在不加任何參數進行備份的時候：
如果單獨備份一個表會對備份表上讀鎖，直到備份結束unlock，如果備份的整個庫，那么會同時lock 這個庫下的所有的表，最后在unlock tables，如果備份的是整個實例(加參數--all-databases 或者-A會備份除了performance_schema和performance_schema這倆庫之外的所有的庫），那么是一個庫一庫的去備份，也就是說先備份庫A，把庫A下的所有的表上讀鎖，備份完庫A，unlock tables,然后再備份庫B,把庫B下的所有的表上讀鎖，備份完庫B，unlock tables，那么所以在使用mysqldump備份某個表或者某個庫的時候，沒有加任何的參數，會上讀鎖，并且備份出來的數據是一致性的，但是如果備份的是整個實例，那么庫和庫之間的數據的一致性就不能保證了；
2、參數--single-transaction ；
針對innodb的引擎，可以加上參數 --single-transaction來保證備份的一致性，并且是借助的修改隔離級別為REPEATABLE READ+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */開啟快照讀事務共同來保證一致性的，所以不需要加read lock;注意該參數僅僅對innodb引擎起作用，對于myisam引擎，雖然添加了--single-transaction參數的myisam表備份處理過程和innodb的過程完全一致，但是因為myisam不支持事務，在整個dump過程中無法保證可重復讀，無法得到一致性的備份。
3、參數--master-data；
--master-data指定為2指的是會在備份文件中生成CHANGE MASTER的注釋。如下所示：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587;
如果該值設置為1，則生成的是CHANGE MASTER的命令，而不是注釋。如下所示：
CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587;
當加上這個參數的時候，為了得到準確的binlog的位置狀態信息，會通過FLUSH TABLES WITH READLOCK來保證，備份開始到結束，是不允許別的事務修改的，同時也就保證了一致性；
4、參數--single-transaction和參數參數--master-data一起使用；
也會執行 FLUSH TABLES WITH READ LOCK，但是在還沒有開始備份時，也就是在 SHOW MASTER STATUS顯示了主庫的binlog狀態之后就unlock tables了，也會 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，之后和單獨加--single-transaction過程是一樣的。
5、參數--lock-all-tables，通過給整個實例所有表都加read lock來保證一致性備份；
加上--lock-all-tables和不加上--lock-all-tables得區別就在于前者是FLUSH TABLES WITH READ LOCK對整個實例所有的表都上讀鎖，后者只針對要備份的表加讀鎖（LOCK TABLES `liu` READ）；并且前者并沒有顯現的unlock tables，因為整個過程中數據庫是不能寫的，并且FLUSH TABLES WITH READ LOCK這個命令一旦這個會話結束，相應的讀鎖也就不存在了，而后者只是鎖了一個表，顯現的unlock解鎖了，其實后者也是會話結束就釋放對表的讀鎖了，也可以不加unlock，
6、START TRANSACTION和START TRANSACTION WITH CONSISTENT SNAPSHOT并不一樣;
START TRANSACTION WITH CONSISTENT SNAPSHOT相當于在執行完START TRANSACTION后對每個Innodb表執行了SELECT操作，在隔離級別為REPEATABLE READ時，并不是當start transaction 就能保證之后的查詢內容是一樣，而是當你發出第一個query的時候，才會開啟快照讀取，之后再有相同的sql查出來的結果是一樣的。
在mysqldump加上參數--single-transaction的時候使用的是START TRANSACTION WITH CONSISTENT SNAPSHOT，而不是START TRANSACTION來保證一致性的，是因為每個表的備份時間并不相同，如果使用START TRANSACTION，在對第一張表進行備份的期間，別的事務對第二個表進行了insert數據A，那么在開始對第二張表備份時,是可以看到數據A的，那么第一個表和第二個表就不是一致性的了，所以START TRANSACTION無法實現當一個庫下有多個表的時候的一致性。.
綜上所述：
在使用mysqldump進行數據備份的時候，盡量在業務量比較小的時候執行，并且根據是不是innodb引擎來選擇不同的參數，如果是innodb的引擎可以使用--single-transaction參數來保證一致性，并且還不用上read lock；但是如果想保證整個實例的一致性（既有innodb又有myisam的表）最好還是使用參數--lock-all-tables，當然為了實現point to point恢復或者不停master服務來添加slave的目的，最好還是加上參數--master-data，同時也就能保證一致性，因為加上參數--master-data會執行FLUSH TABLES WITH READLOCK；
下面是具體的驗證過程：
一：打開general log，便于分析mysqldump具體執行了什么操作
mysql> set global general_log=on;
其中，general log的存放路徑可通過以下命令查看
mysql> show variables like '%general_log_file%';
二：執行MySQLdump導出表實驗如下：
2.1首先什么參數都不加的情況：
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql
查看相應的general_log：
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 14:12:22 17 Quit
180429 14:12:55 18 Connect root@localhost on
18 Query /*!40100 SET @@SQL_MODE='' */
18 Query /*!40103 SET TIME_ZONE='+00:00' */
18 Query SHOW VARIABLES LIKE 'gtid\_mode'
18 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENG
INE ORDER BY LOGFILE_GROUP_NAME
18 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NA
ME
18 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
18 Init DB liuhe
18 Query SHOW TABLES LIKE 'blocks\_infos\_opensearch'
18 Query LOCK TABLES `blocks_infos_opensearch` READ /*!32311 LOCAL */
18 Query show table status like 'blocks\_infos\_opensearch'
18 Query SET SQL_QUOTE_SHOW_CREATE=1
18 Query SET SESSION character_set_results = 'binary'
18 Query show create table `blocks_infos_opensearch`
18 Query SET SESSION character_set_results = 'utf8'
18 Query show fields from `blocks_infos_opensearch`
18 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos_opensearch`
18 Query SET SESSION character_set_results = 'binary'
18 Query use `liuhe`
18 Query select @@collation_database
18 Query SHOW TRIGGERS LIKE 'blocks\_infos\_opensearch'
18 Query SET SESSION character_set_results = 'utf8'
18 Query UNLOCK TABLES
18 Quit
2.2:加上參數--single-transaction
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --single-transaction liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 14:20:41 21 Quit
180429 14:20:47 22 Connect root@localhost on
22 Query /*!40100 SET @@SQL_MODE='' */
22 Query /*!40103 SET TIME_ZONE='+00:00' */
22 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
22 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ #開啟快照讀
22 Query SHOW VARIABLES LIKE 'gtid\_mode'
22 Query UNLOCK TABLES
22 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENG
INE ORDER BY LOGFILE_GROUP_NAME
22 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NA
ME
22 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
22 Init DB liuhe
22 Query SHOW TABLES LIKE 'blocks\_infos\_opensearch'
22 Query SAVEPOINT sp
22 Query show table status like 'blocks\_infos\_opensearch'
22 Query SET SQL_QUOTE_SHOW_CREATE=1
22 Query SET SESSION character_set_results = 'binary'
22 Query show create table `blocks_infos_opensearch`
22 Query SET SESSION character_set_results = 'utf8'
22 Query show fields from `blocks_infos_opensearch`
22 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos_opensearch`
22 Query SET SESSION character_set_results = 'binary'
22 Query use `liuhe`
22 Query select @@collation_database
22 Query SHOW TRIGGERS LIKE 'blocks\_infos\_opensearch'
22 Query SET SESSION character_set_results = 'utf8'
22 Query ROLLBACK TO SAVEPOINT sp #把事務回退到這個點 sp
22 Query RELEASE SAVEPOINT sp #放棄保存點，需要注意的是一旦rollback或者commit，那么之前創建的savepoint就會失效；
180429 14:20:48 22 Quit
通過.1和3.2可以看出來加上參數--single-transaction，可以保證mysqldump的時候不需要LOCK TABLES `blocks_infos_opensearch` READ ;并且使用參數--single-transaction，需要修改MySQL的隔離界別為 REPEATABLE READ來保證各個事務之間互相不影響對方，保證在執行MySQLdump的會話始終讀取不到別的事務的操作，進而保證了MySQLdump出來的數據的一致性；并且為了能獲得準確的pos點，需要START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 來開啟快照讀的事務，因為如果只START TRANSACTION ，并沒有生成快照，而是在執行第一個select的時候，才會生成快照，也就是說如果START TRANSACTION之后，另一個事務insert了數據A，然后你再select，是可以看到的數據A的，這樣就不能得到精確的pos值了。
2.3加上參數--master-data
具體如下：
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --master-data=2 liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 18:01:27 35 Quit
180429 18:02:15 36 Connect root@localhost on
36 Query /*!40100 SET @@SQL_MODE='' */
36 Query /*!40103 SET TIME_ZONE='+00:00' */
36 Query FLUSH /*!40101 LOCAL */ TABLES
36 Query FLUSH TABLES WITH READ LOCK
36 Query SHOW VARIABLES LIKE 'gtid\_mode'
36 Query SHOW MASTER STATUS
36 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENG
INE ORDER BY LOGFILE_GROUP_NAME
36 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NA
ME
36 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
36 Init DB liuhe
36 Query SHOW TABLES LIKE 'blocks\_infos\_opensearch'
36 Query show table status like 'blocks\_infos\_opensearch'
36 Query SET SQL_QUOTE_SHOW_CREATE=1
36 Query SET SESSION character_set_results = 'binary'
36 Query show create table `blocks_infos_opensearch`
36 Query SET SESSION character_set_results = 'utf8'
36 Query show fields from `blocks_infos_opensearch`
36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos_opensearch`
180429 18:02:16 36 Query SET SESSION character_set_results = 'binary'
36 Query use `liuhe`
36 Query select @@collation_database
36 Query SHOW TRIGGERS LIKE 'blocks\_infos\_opensearch'
36 Query SET SESSION character_set_results = 'utf8'
36 Quit
通過2.1和2.2可以看出來，通過 SHOW MASTER STATUS來顯示當時binlog的位置，通過FLUSH TABLES WITH READ LOCK，來保證一致性，注意盡管只是備份一個表，由于這個binlog的位置是可以在不停主庫的前提下添加從庫時直接可以使用的位置，所以需要鎖住整個實例的所有的表（ FLUSH TABLES WITH READ LOCK），來保證這個位置在備份開始的時候，不再有任何dml操作，也就是這個位置就不再增大；
2.4同時添加上參數--master-data和--single-transaction
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --single-transaction --master-data=2 liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql
Warning: Using a password on the command line interface can be insecure.
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 18:50:06 37 Quit
180429 18:50:36 38 Connect root@localhost on
38 Query /*!40100 SET @@SQL_MODE='' */
38 Query /*!40103 SET TIME_ZONE='+00:00' */
38 Query FLUSH /*!40101 LOCAL */ TABLES
38 Query FLUSH TABLES WITH READ LOCK
38 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
38 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
38 Query SHOW VARIABLES LIKE 'gtid\_mode'
38 Query SHOW MASTER STATUS
38 Query UNLOCK TABLES
38 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENG
INE ORDER BY LOGFILE_GROUP_NAME
38 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('blocks_infos_opensearch')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NA
ME
38 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
38 Init DB liuhe
38 Query SHOW TABLES LIKE 'blocks\_infos\_opensearch'
38 Query SAVEPOINT sp
38 Query show table status like 'blocks\_infos\_opensearch'
38 Query SET SQL_QUOTE_SHOW_CREATE=1
38 Query SET SESSION character_set_results = 'binary'
38 Query show create table `blocks_infos_opensearch`
38 Query SET SESSION character_set_results = 'utf8'
38 Query show fields from `blocks_infos_opensearch`
38 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos_opensearch`
38 Query SET SESSION character_set_results = 'binary'
38 Query use `liuhe`
38 Query select @@collation_database
38 Query SHOW TRIGGERS LIKE 'blocks\_infos\_opensearch'
38 Query SET SESSION character_set_results = 'utf8'
38 Query ROLLBACK TO SAVEPOINT sp
38 Query RELEASE SAVEPOINT sp
38 Quit
通過2.4和2.1對比可以知道，當同時添加上參數--master-data和 --single-transaction 的時候，會執行 FLUSH TABLES WITH READ LOCK（但是還沒有開始備份，在 SHOW MASTER STATUS顯示了主庫的binlog狀態之后就unlock tables了），也會 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，之后和單獨加--single-transaction是一樣的；
2.5:如果是myisam引擎會怎么樣？（創建了存儲引擎為myisam的表liu）
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --single-transaction liuhe liu>/mysql/liu.sql
查看general log發現和innodb 添加--single-transaction參數的情況是一樣的執行過程
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 19:54:28 3 Quit
180429 19:55:29 4 Connect root@localhost on
4 Query /*!40100 SET @@SQL_MODE='' */
4 Query /*!40103 SET TIME_ZONE='+00:00' */
4 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
4 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
4 Query SHOW VARIABLES LIKE 'gtid\_mode'
4 Query UNLOCK TABLES
4 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('liu'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE
_GROUP_NAME
4 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('liu')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
4 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
4 Init DB liuhe
4 Query SHOW TABLES LIKE 'liu'
4 Query SAVEPOINT sp
4 Query show table status like 'liu'
4 Query SET SQL_QUOTE_SHOW_CREATE=1
4 Query SET SESSION character_set_results = 'binary'
4 Query show create table `liu`
4 Query SET SESSION character_set_results = 'utf8'
4 Query show fields from `liu`
4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `liu`
4 Query SET SESSION character_set_results = 'binary'
4 Query use `liuhe`
4 Query select @@collation_database
4 Query SHOW TRIGGERS LIKE 'liu'
4 Query SET SESSION character_set_results = 'utf8'
4 Query ROLLBACK TO SAVEPOINT sp
4 Query RELEASE SAVEPOINT sp
4 Quit
bogon:root@/mysql/data/data>
分析：
雖然添加了--single-transaction參數的myisam表處理過程和innodb的過程完全一致，但是因為myisam不支持事務，在整個dump過程中無法保證可重復讀，無法得到一致性的備份。而innodb在備份過程中，雖然其他線程也在寫數據，但是dump出來的數據能保證是備份開始時那個binlog pos的數據。
myisam引擎要保證得到一致性的數據的可以通過添加--lock-all-tables，這樣在flush tables with read lock后，直到整個dump過程結束，斷開線程后才會unlock tables釋放鎖(沒必要主動發unlock tables指令)，整個dump過程其他線程不可寫，從而保證數據的一致性;
2.6:備份myisam的時候，加上--lock-all-tables和不加該參數的不同的執行過程如下：
2.6.1加上--lock-all-tables的情況如下：
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --lock-all-tables liuhe liu>/mysql/liu.sql
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 20:16:12 5 Quit
180429 20:18:18 6 Connect root@localhost on
6 Query /*!40100 SET @@SQL_MODE='' */
6 Query /*!40103 SET TIME_ZONE='+00:00' */
6 Query FLUSH TABLES
180429 20:18:19 6 Query FLUSH TABLES WITH READ LOCK
6 Query SHOW VARIABLES LIKE 'gtid\_mode'
6 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('liu'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE
_GROUP_NAME
6 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('liu')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
6 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
6 Init DB liuhe
6 Query SHOW TABLES LIKE 'liu'
6 Query show table status like 'liu'
6 Query SET SQL_QUOTE_SHOW_CREATE=1
6 Query SET SESSION character_set_results = 'binary'
6 Query show create table `liu`
6 Query SET SESSION character_set_results = 'utf8'
6 Query show fields from `liu`
6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `liu`
6 Query SET SESSION character_set_results = 'binary'
6 Query use `liuhe`
6 Query select @@collation_database
6 Query SHOW TRIGGERS LIKE 'liu'
6 Query SET SESSION character_set_results = 'utf8'
6 Quit
2.6.2不加上--lock-all-tables得過程如下：
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe liuhe liu>/mysql/liu.sql
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 20:25:43 7 Quit
180429 20:25:53 8 Connect root@localhost on
8 Query /*!40100 SET @@SQL_MODE='' */
8 Query /*!40103 SET TIME_ZONE='+00:00' */
8 Query SHOW VARIABLES LIKE 'gtid\_mode'
8 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('liu'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE
_GROUP_NAME
8 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA='liuhe' AND TABLE_NAME IN ('liu')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
8 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
8 Init DB liuhe
8 Query SHOW TABLES LIKE 'liu'
8 Query LOCK TABLES `liu` READ /*!32311 LOCAL */
8 Query show table status like 'liu'
8 Query SET SQL_QUOTE_SHOW_CREATE=1
8 Query SET SESSION character_set_results = 'binary'
8 Query show create table `liu`
8 Query SET SESSION character_set_results = 'utf8'
8 Query show fields from `liu`
8 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `liu`
8 Query SET SESSION character_set_results = 'binary'
8 Query use `liuhe`
8 Query select @@collation_database
8 Query SHOW TRIGGERS LIKE 'liu'
8 Query SET SESSION character_set_results = 'utf8'
8 Query UNLOCK TABLES
8 Quit
對比2.6.1和2.6.2可以知道myisam表，加上--lock-all-tables和不加上--lock-all-tables得區別就在于前者是FLUSH TABLES WITH READ LOCK對整個實例所有的表都上讀鎖，后者只針對要備份的表加讀鎖（LOCK TABLES `liu` READ）；并且前者并沒有顯現的unlock tables，因為整個過程中數據庫是不能寫的，并且FLUSH TABLES WITH READ LOCK這個命令一旦這個會話結束，相應的讀鎖也就不存在了，而后者只是鎖了一個表，顯現的unlock解鎖了，其實后者也是會話結束就釋放對表的讀鎖了，也可以不加unlock，
2.7.備份整個庫時候，不加任何參數，可以看到會同時lock 這個庫下的所有的表，最后在unlock
bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe liuhe > /mysql/blocks_infos_opensearch.sql
bogon:root@/mysql/data/data>more bogon.log
/usr/local/mysql/bin/mysqld, Version: 5.6.38-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 21:21:44 9 Quit
180429 21:22:21 10 Connect root@localhost on
10 Query /*!40100 SET @@SQL_MODE='' */
10 Query /*!40103 SET TIME_ZONE='+00:00' */
10 Query SHOW VARIABLES LIKE 'gtid\_mode'
10 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_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_SCHE
MA.PARTITIONS WHERE TABLE_SCHEMA IN ('liuhe'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
10 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SC
HEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('liuhe')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
10 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
10 Init DB liuhe
10 Query show tables
10 Query LOCK TABLES `blocks_infos` READ /*!32311 LOCAL */,`blocks_infos1` READ /*!32311 LOCAL */,`blocks_inf
os_opensearch` READ /*!32311 LOCAL */,`liu` READ /*!32311 LOCAL */,`test` READ /*!32311 LOCAL */
10 Query show table status like 'blocks\_infos'
10 Query SET SQL_QUOTE_SHOW_CREATE=1
10 Query SET SESSION character_set_results = 'binary'
10 Query show create table `blocks_infos`
10 Query SET SESSION character_set_results = 'utf8'
10 Query show fields from `blocks_infos`
10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos`
180429 21:22:23 10 Query SET SESSION character_set_results = 'binary'
10 Query use `liuhe`
10 Query select @@collation_database
10 Query SHOW TRIGGERS LIKE 'blocks\_infos'
10 Query SHOW CREATE TRIGGER `tri_insert_blocks_infos1`
10 Query SHOW CREATE TRIGGER `tri_update_blocks_infos1`
10 Query SHOW CREATE TRIGGER `tri_delete_blocks_infos1`
10 Query SHOW CREATE TRIGGER `tri_delete_blocks_infos`
10 Query SET SESSION character_set_results = 'utf8'
10 Query show table status like 'blocks\_infos1'
10 Query SET SQL_QUOTE_SHOW_CREATE=1
10 Query SET SESSION character_set_results = 'binary'
10 Query show create table `blocks_infos1`
10 Query SET SESSION character_set_results = 'utf8'
10 Query show fields from `blocks_infos1`
10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos1`
10 Query SET SESSION character_set_results = 'binary'
10 Query use `liuhe`
10 Query select @@collation_database
10 Query SHOW TRIGGERS LIKE 'blocks\_infos1'
10 Query SET SESSION character_set_results = 'utf8'
10 Query show table status like 'blocks\_infos\_opensearch'
10 Query SET SQL_QUOTE_SHOW_CREATE=1
10 Query SET SESSION character_set_results = 'binary'
10 Query show create table `blocks_infos_opensearch`
10 Query SET SESSION character_set_results = 'utf8'
10 Query show fields from `blocks_infos_opensearch`
10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `blocks_infos_opensearch`
180429 21:22:24 10 Query SET SESSION character_set_results = 'binary'
10 Query use `liuhe`
10 Query select @@collation_database
10 Query SHOW TRIGGERS LIKE 'blocks\_infos\_opensearch'
10 Query SET SESSION character_set_results = 'utf8'
10 Query show table status like 'liu'
10 Query SET SQL_QUOTE_SHOW_CREATE=1
10 Query SET SESSION character_set_results = 'binary'
10 Query show create table `liu`
10 Query SET SESSION character_set_results = 'utf8'
10 Query show fields from `liu`
10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `liu`
10 Query SET SESSION character_set_results = 'binary'
10 Query use `liuhe`
10 Query select @@collation_database
10 Query SHOW TRIGGERS LIKE 'liu'
10 Query SET SESSION character_set_results = 'utf8'
10 Query show table status like 'test'
10 Query SET SQL_QUOTE_SHOW_CREATE=1
10 Query SET SESSION character_set_results = 'binary'
10 Query show create table `test`
10 Query SET SESSION character_set_results = 'utf8'
10 Query show fields from `test`
10 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`
10 Query SET SESSION character_set_results = 'binary'
10 Query use `liuhe`
10 Query select @@collation_database
10 Query SHOW TRIGGERS LIKE 'test'
10 Query SET SESSION character_set_results = 'utf8'
10 Query UNLOCK TABLES
10 Quit
2.8：備份整個實例的所有的庫加上參數--all-databases 或者-A ，然后不加別的參數
如果是備份整個實例的所有的庫，也就加上參數--all-databases 或者-A ，然后不加別的參數，如下可知，是一個庫一庫的去備份，也就是說先備份庫A，把庫A下的所有的表上讀鎖，備份完庫A，unlock tables,然后再備份庫B,把庫B下的所有的表上讀鎖，備份完庫B，unlock tables,這樣就可以知道，不加任何參數的話，全實例備份時，只能保證一個庫下的所有的表是一致性的，但是庫和庫之間卻不能保證一致性；
[root@oracle3 ~]# more /home/mysql/data/data/oracle3.log
/usr/local/mysql/bin/mysqld, Version: 5.6.39-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
180429 21:58:24 35 Query show variables like '%general_log_file%'
180429 21:58:30 35 Quit
180429 21:58:45 36 Connect root@localhost on
36 Query /*!40100 SET @@SQL_MODE='' */
36 Query /*!40103 SET TIME_ZONE='+00:00' */
36 Query SHOW VARIABLES LIKE 'gtid\_mode'
36 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GR
OUP_NAME
36 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
36 Query SHOW DATABASES
36 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
36 Init DB liuhe
36 Query SHOW CREATE DATABASE IF NOT EXISTS `liuhe`
36 Query show tables
36 Query UNLOCK TABLES
36 Init DB liuwenhe
36 Query SHOW CREATE DATABASE IF NOT EXISTS `liuwenhe`
36 Query show tables
36 Query LOCK TABLES `test` READ /*!32311 LOCAL */
36 Query show table status like 'test'
36 Query SET SQL_QUOTE_SHOW_CREATE=1
36 Query SET SESSION character_set_results = 'binary'
36 Query show create table `test`
36 Query SET SESSION character_set_results = 'utf8'
36 Query show fields from `test`
36 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`
36 Query SET SESSION character_set_results = 'binary'
36 Query use `liuwenhe`
36 Query select @@collation_database
36 Query SHOW TRIGGERS LIKE 'test'
36 Query SET SESSION character_set_results = 'utf8'
36 Query UNLOCK TABLES
36 Init DB mysql
36 Query SHOW CREATE DATABASE IF NOT EXISTS `mysql`
36 Query show tables
36 Query LOCK TABLES `columns_priv` READ /*!32311 LOCAL */,`db` READ /*!32311 LOCAL */,`event` READ /*!32311
LOCAL */,`func` READ /*!32311 LOCAL */,`help_category` READ /*!32311 LOCAL */,`help_keyword` READ /*!32311 LOCAL */,`help_relation`
READ /*!32311 LOCAL */,`help_topic` READ /*!32311 LOCAL */,`innodb_index_stats` READ /*!32311 LOCAL */,`innodb_table_stats` READ /*!
32311 LOCAL */,`ndb_binlog_index` READ /*!32311 LOCAL */,`plugin` READ /*!32311 LOCAL */,`proc` READ /*!32311 LOCAL */,`procs_priv`
READ /*!32311 LOCAL */,`proxies_priv` READ /*!32311 LOCAL */,`servers` READ /*!32311 LOCAL */,`slave_master_info` READ /*!32311 LOCA
L */,`slave_relay_log_info` READ /*!32311 LOCAL */,`slave_worker_info` READ /*!32311 LOCAL */,`tables_priv` READ /*!32311 LOCAL */,`
time_zone` READ /*!32311 LOCAL */,`time_zone_leap_second` READ /*!32311 LOCAL */,`time_zone_name` READ /*!32311 LOCAL */,`time_zone_
transition` READ /*!32311 LOCAL */,`time_zone_transition_type` READ /*!32311 LOCAL */,`user` READ /*!32311 LOCAL */
題外話
考慮一下，我們知道當沒有添加任何參數的時候，mysqldump默認也會lock 這個需要備份的表，但是如果mysiam引擎中也添加--single-transaction參數（這樣備份出來的數據就是不一致的）再用這個備份去創建從庫或恢復到指定時間點，會有什么樣的影響？
我個人的理解是如果整個dump過程中只有簡單的insert操作，是沒有關系的，期間肯定會有很多的主鍵重復錯誤，直接跳過或忽略就好了。如果是update操作，那就要出問題了，分幾種情況考慮
1） 如果是基于時間點的恢復，假設整個dump過程有update a set id=5 where id=4之類的操作，相當于重復執行兩次該操作，應該問題不大
2） 如果是創建從庫，遇到上面的sql從庫會報錯，找不到該記錄，這時跳過就好
3）不管是恢復還是創建從庫，如果dump過程中有update a set id=id+5 之類的操作，那就有問題，重復執行兩次，數據全變了。
