今天學習了下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 之類的操作，那就有問題，重複執行兩次，資料全變了。

mysqldump原理分析

相關文章