今天學(xué)習(xí)了下mysqldump原理，具體的結(jié)論如下： 1、mysqldump在不加任何參數(shù)進(jìn)行備份的時(shí)候： 如果單獨(dú)備份一個(gè)表會(huì)對(duì)備份表上讀鎖，直到備份結(jié)束unlock，如果備份的整個(gè)庫(kù)，那么會(huì)同時(shí)lock 這個(gè)庫(kù)下的所有的表，最后在unlock tables，如果備份的是整個(gè)實(shí)例(加參數(shù)--all-databases 或者-A會(huì)備份除了performance_schema和performance_schema這倆庫(kù)之外的所有的庫(kù)），那么是一個(gè)庫(kù)一庫(kù)的去備份，也就是說(shuō)先備份庫(kù)A，把庫(kù)A下的所有的表上讀鎖，備份完庫(kù)A，unlock tables,然后再備份庫(kù)B,把庫(kù)B下的所有的表上讀鎖，備份完庫(kù)B，unlock tables，那么所以在使用mysqldump備份某個(gè)表或者某個(gè)庫(kù)的時(shí)候，沒(méi)有加任何的參數(shù)，會(huì)上讀鎖，并且備份出來(lái)的數(shù)據(jù)是一致性的，但是如果備份的是整個(gè)實(shí)例，那么庫(kù)和庫(kù)之間的數(shù)據(jù)的一致性就不能保證了； 2、參數(shù)--single-transaction ； 針對(duì)innodb的引擎，可以加上參數(shù) --single-transaction來(lái)保證備份的一致性，并且是借助的修改隔離級(jí)別為REPEATABLE READ+START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */開(kāi)啟快照讀事務(wù)共同來(lái)保證一致性的，所以不需要加read lock;注意該參數(shù)僅僅對(duì)innodb引擎起作用，對(duì)于myisam引擎，雖然添加了--single-transaction參數(shù)的myisam表備份處理過(guò)程和innodb的過(guò)程完全一致，但是因?yàn)閙yisam不支持事務(wù)，在整個(gè)dump過(guò)程中無(wú)法保證可重復(fù)讀，無(wú)法得到一致性的備份。 3、參數(shù)--master-data； --master-data指定為2指的是會(huì)在備份文件中生成CHANGE MASTER的注釋。如下所示： -- CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587; 如果該值設(shè)置為1，則生成的是CHANGE MASTER的命令，而不是注釋。如下所示： CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587; 當(dāng)加上這個(gè)參數(shù)的時(shí)候，為了得到準(zhǔn)確的binlog的位置狀態(tài)信息，會(huì)通過(guò)FLUSH TABLES WITH READLOCK來(lái)保證，備份開(kāi)始到結(jié)束，是不允許別的事務(wù)修改的，同時(shí)也就保證了一致性； 4、參數(shù)--single-transaction和參數(shù)參數(shù)--master-data一起使用； 也會(huì)執(zhí)行 FLUSH TABLES WITH READ LOCK，但是在還沒(méi)有開(kāi)始備份時(shí)，也就是在 SHOW MASTER STATUS顯示了主庫(kù)的binlog狀態(tài)之后就unlock tables了，也會(huì) SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，之后和單獨(dú)加--single-transaction過(guò)程是一樣的。 5、參數(shù)--lock-all-tables，通過(guò)給整個(gè)實(shí)例所有表都加read lock來(lái)保證一致性備份； 加上--lock-all-tables和不加上--lock-all-tables得區(qū)別就在于前者是FLUSH TABLES WITH READ LOCK對(duì)整個(gè)實(shí)例所有的表都上讀鎖，后者只針對(duì)要備份的表加讀鎖（LOCK TABLES `liu` READ）；并且前者并沒(méi)有顯現(xiàn)的unlock tables，因?yàn)檎麄€(gè)過(guò)程中數(shù)據(jù)庫(kù)是不能寫(xiě)的，并且FLUSH TABLES WITH READ LOCK這個(gè)命令一旦這個(gè)會(huì)話結(jié)束，相應(yīng)的讀鎖也就不存在了，而后者只是鎖了一個(gè)表，顯現(xiàn)的unlock解鎖了，其實(shí)后者也是會(huì)話結(jié)束就釋放對(duì)表的讀鎖了，也可以不加unlock， 6、START TRANSACTION和START TRANSACTION WITH CONSISTENT SNAPSHOT并不一樣; START TRANSACTION WITH CONSISTENT SNAPSHOT相當(dāng)于在執(zhí)行完START TRANSACTION后對(duì)每個(gè)Innodb表執(zhí)行了SELECT操作，在隔離級(jí)別為REPEATABLE READ時(shí)，并不是當(dāng)start transaction 就能保證之后的查詢內(nèi)容是一樣，而是當(dāng)你發(fā)出第一個(gè)query的時(shí)候，才會(huì)開(kāi)啟快照讀取，之后再有相同的sql查出來(lái)的結(jié)果是一樣的。 在mysqldump加上參數(shù)--single-transaction的時(shí)候使用的是START TRANSACTION WITH CONSISTENT SNAPSHOT，而不是START TRANSACTION來(lái)保證一致性的，是因?yàn)槊總€(gè)表的備份時(shí)間并不相同，如果使用START TRANSACTION，在對(duì)第一張表進(jìn)行備份的期間，別的事務(wù)對(duì)第二個(gè)表進(jìn)行了insert數(shù)據(jù)A，那么在開(kāi)始對(duì)第二張表備份時(shí),是可以看到數(shù)據(jù)A的，那么第一個(gè)表和第二個(gè)表就不是一致性的了，所以START TRANSACTION無(wú)法實(shí)現(xiàn)當(dāng)一個(gè)庫(kù)下有多個(gè)表的時(shí)候的一致性。. 綜上所述： 在使用mysqldump進(jìn)行數(shù)據(jù)備份的時(shí)候，盡量在業(yè)務(wù)量比較小的時(shí)候執(zhí)行，并且根據(jù)是不是innodb引擎來(lái)選擇不同的參數(shù)，如果是innodb的引擎可以使用--single-transaction參數(shù)來(lái)保證一致性，并且還不用上read lock；但是如果想保證整個(gè)實(shí)例的一致性（既有innodb又有myisam的表）最好還是使用參數(shù)--lock-all-tables，當(dāng)然為了實(shí)現(xiàn)point to point恢復(fù)或者不停master服務(wù)來(lái)添加slave的目的，最好還是加上參數(shù)--master-data，同時(shí)也就能保證一致性，因?yàn)榧由蠀?shù)--master-data會(huì)執(zhí)行FLUSH TABLES WITH READLOCK； 下面是具體的驗(yàn)證過(guò)程： 一：打開(kāi)general log，便于分析mysqldump具體執(zhí)行了什么操作 mysql> set global general_log=on; 其中，general log的存放路徑可通過(guò)以下命令查看 mysql> show variables like '%general_log_file%'; 二：執(zhí)行MySQLdump導(dǎo)出表實(shí)驗(yàn)如下： 2.1首先什么參數(shù)都不加的情況： bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe liuhe blocks_infos_opensearch> /mysql/blocks_infos_opensearch.sql 查看相應(yīng)的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:加上參數(shù)--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 */ #開(kāi)啟快照讀 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 #把事務(wù)回退到這個(gè)點(diǎn) sp 22 Query RELEASE SAVEPOINT sp #放棄保存點(diǎn)，需要注意的是一旦rollback或者commit，那么之前創(chuàng)建的savepoint就會(huì)失效； 180429 14:20:48 22 Quit 通過(guò).1和3.2可以看出來(lái)加上參數(shù)--single-transaction，可以保證mysqldump的時(shí)候不需要LOCK TABLES `blocks_infos_opensearch` READ ;并且使用參數(shù)--single-transaction，需要修改MySQL的隔離界別為 REPEATABLE READ來(lái)保證各個(gè)事務(wù)之間互相不影響對(duì)方，保證在執(zhí)行MySQLdump的會(huì)話始終讀取不到別的事務(wù)的操作，進(jìn)而保證了MySQLdump出來(lái)的數(shù)據(jù)的一致性；并且為了能獲得準(zhǔn)確的pos點(diǎn)，需要START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 來(lái)開(kāi)啟快照讀的事務(wù)，因?yàn)槿绻籗TART TRANSACTION ，并沒(méi)有生成快照，而是在執(zhí)行第一個(gè)select的時(shí)候，才會(huì)生成快照，也就是說(shuō)如果START TRANSACTION之后，另一個(gè)事務(wù)insert了數(shù)據(jù)A，然后你再select，是可以看到的數(shù)據(jù)A的，這樣就不能得到精確的pos值了。 2.3加上參數(shù)--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 通過(guò)2.1和2.2可以看出來(lái)，通過(guò) SHOW MASTER STATUS來(lái)顯示當(dāng)時(shí)binlog的位置，通過(guò)FLUSH TABLES WITH READ LOCK，來(lái)保證一致性，注意盡管只是備份一個(gè)表，由于這個(gè)binlog的位置是可以在不停主庫(kù)的前提下添加從庫(kù)時(shí)直接可以使用的位置，所以需要鎖住整個(gè)實(shí)例的所有的表（ FLUSH TABLES WITH READ LOCK），來(lái)保證這個(gè)位置在備份開(kāi)始的時(shí)候，不再有任何dml操作，也就是這個(gè)位置就不再增大； 2.4同時(shí)添加上參數(shù)--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 通過(guò)2.4和2.1對(duì)比可以知道，當(dāng)同時(shí)添加上參數(shù)--master-data和 --single-transaction 的時(shí)候，會(huì)執(zhí)行 FLUSH TABLES WITH READ LOCK（但是還沒(méi)有開(kāi)始備份，在 SHOW MASTER STATUS顯示了主庫(kù)的binlog狀態(tài)之后就unlock tables了），也會(huì) SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，之后和單獨(dú)加--single-transaction是一樣的； 2.5:如果是myisam引擎會(huì)怎么樣？（創(chuàng)建了存儲(chǔ)引擎為myisam的表liu） bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe --single-transaction liuhe liu>/mysql/liu.sql 查看general log發(fā)現(xiàn)和innodb 添加--single-transaction參數(shù)的情況是一樣的執(zhí)行過(guò)程 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參數(shù)的myisam表處理過(guò)程和innodb的過(guò)程完全一致，但是因?yàn)閙yisam不支持事務(wù)，在整個(gè)dump過(guò)程中無(wú)法保證可重復(fù)讀，無(wú)法得到一致性的備份。而innodb在備份過(guò)程中，雖然其他線程也在寫(xiě)數(shù)據(jù)，但是dump出來(lái)的數(shù)據(jù)能保證是備份開(kāi)始時(shí)那個(gè)binlog pos的數(shù)據(jù)。 myisam引擎要保證得到一致性的數(shù)據(jù)的可以通過(guò)添加--lock-all-tables，這樣在flush tables with read lock后，直到整個(gè)dump過(guò)程結(jié)束，斷開(kāi)線程后才會(huì)unlock tables釋放鎖(沒(méi)必要主動(dòng)發(fā)unlock tables指令)，整個(gè)dump過(guò)程其他線程不可寫(xiě)，從而保證數(shù)據(jù)的一致性; 2.6:備份myisam的時(shí)候，加上--lock-all-tables和不加該參數(shù)的不同的執(zhí)行過(guò)程如下： 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得過(guò)程如下： 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 對(duì)比2.6.1和2.6.2可以知道m(xù)yisam表，加上--lock-all-tables和不加上--lock-all-tables得區(qū)別就在于前者是FLUSH TABLES WITH READ LOCK對(duì)整個(gè)實(shí)例所有的表都上讀鎖，后者只針對(duì)要備份的表加讀鎖（LOCK TABLES `liu` READ）；并且前者并沒(méi)有顯現(xiàn)的unlock tables，因?yàn)檎麄€(gè)過(guò)程中數(shù)據(jù)庫(kù)是不能寫(xiě)的，并且FLUSH TABLES WITH READ LOCK這個(gè)命令一旦這個(gè)會(huì)話結(jié)束，相應(yīng)的讀鎖也就不存在了，而后者只是鎖了一個(gè)表，顯現(xiàn)的unlock解鎖了，其實(shí)后者也是會(huì)話結(jié)束就釋放對(duì)表的讀鎖了，也可以不加unlock， 2.7.備份整個(gè)庫(kù)時(shí)候，不加任何參數(shù)，可以看到會(huì)同時(shí)lock 這個(gè)庫(kù)下的所有的表，最后在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：備份整個(gè)實(shí)例的所有的庫(kù)加上參數(shù)--all-databases 或者-A ，然后不加別的參數(shù) 如果是備份整個(gè)實(shí)例的所有的庫(kù)，也就加上參數(shù)--all-databases 或者-A ，然后不加別的參數(shù)，如下可知，是一個(gè)庫(kù)一庫(kù)的去備份，也就是說(shuō)先備份庫(kù)A，把庫(kù)A下的所有的表上讀鎖，備份完庫(kù)A，unlock tables,然后再備份庫(kù)B,把庫(kù)B下的所有的表上讀鎖，備份完庫(kù)B，unlock tables,這樣就可以知道，不加任何參數(shù)的話，全實(shí)例備份時(shí)，只能保證一個(gè)庫(kù)下的所有的表是一致性的，但是庫(kù)和庫(kù)之間卻不能保證一致性； [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 */ 題外話 考慮一下，我們知道當(dāng)沒(méi)有添加任何參數(shù)的時(shí)候，mysqldump默認(rèn)也會(huì)lock 這個(gè)需要備份的表，但是如果mysiam引擎中也添加--single-transaction參數(shù)（這樣備份出來(lái)的數(shù)據(jù)就是不一致的）再用這個(gè)備份去創(chuàng)建從庫(kù)或恢復(fù)到指定時(shí)間點(diǎn)，會(huì)有什么樣的影響？ 我個(gè)人的理解是如果整個(gè)dump過(guò)程中只有簡(jiǎn)單的insert操作，是沒(méi)有關(guān)系的，期間肯定會(huì)有很多的主鍵重復(fù)錯(cuò)誤，直接跳過(guò)或忽略就好了。如果是update操作，那就要出問(wèn)題了，分幾種情況考慮 1） 如果是基于時(shí)間點(diǎn)的恢復(fù)，假設(shè)整個(gè)dump過(guò)程有update a set id=5 where id=4之類(lèi)的操作，相當(dāng)于重復(fù)執(zhí)行兩次該操作，應(yīng)該問(wèn)題不大 2） 如果是創(chuàng)建從庫(kù)，遇到上面的sql從庫(kù)會(huì)報(bào)錯(cuò)，找不到該記錄，這時(shí)跳過(guò)就好 3）不管是恢復(fù)還是創(chuàng)建從庫(kù)，如果dump過(guò)程中有update a set id=id+5 之類(lèi)的操作，那就有問(wèn)題，重復(fù)執(zhí)行兩次，數(shù)據(jù)全變了。
