mysqldump備份時的數(shù)據(jù)一致性問題
在日常運維當中，經常會用到mysqldump。使用mysqldump導出數(shù)據(jù)的時候，我們最關心的問題之一就是表的一致性。簡單的說就是所有表是不是同一時間的數(shù)據(jù)和結構。隨著備份參數(shù)的不同，表的一致性和對數(shù)據(jù)庫的影響也會不一樣。
測試的mysqldump版本
mysqldump Ver 10.13 Distrib 5.7.22-22, for Linux (x86_64)
由于mysiam引擎的退休，這里默認談論的都是innodb引擎的表。
情況一，在不使用任何其他參數(shù)的情況下
mysqldump -h127.0.0.1 -uwxp -p'wxp' test > dump.sql
很簡單，只是指定了連接地址，賬號密碼，和需要導出的數(shù)據(jù)庫。在沒有指定參數(shù)的情況下，默認會使用lock-tables參數(shù)。官方文檔參數(shù)解釋如下：
For each dumped database, lock all tables to be dumped before dumping them。Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.
一致性狀態(tài)：
單個庫里的所有表都保持一致性，庫之間的表不一定能保證一致性。
general日志相關語句：
...
LOCK TABLES `backup` READ /*!32311 LOCAL */,`t` READ /*!32311 LOCAL */,`t1` READ /*!32311 LOCAL */
...
UNLOCK TABLES
在備份一開始就顯示的一次性給所有的表加上讀鎖，讓庫在備份期間變成只讀來確保表的一致性。由于是一個庫一個庫的備份，多個庫之間的表是不一定存在一致性的。舉個夸張的例子，同時導a,b兩個庫，a庫里面的表可能是下午三點的狀態(tài)，而b庫里面的表卻是下午4點鐘的狀態(tài)。
備份表可以執(zhí)行的語句:
正在備份的庫當中所有表的并發(fā)DML，DDL都會被阻塞，只能執(zhí)行查詢語句（SELECT）。
情況二，使用lock-all-tables
mysqldump -h127.0.0.1 -uwxp -p'wxp' --lock-all-tables test > dump.sql
官方參數(shù)解釋
Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.
一致性狀態(tài)：
所有庫的所有表都能保持一致性。
general日志相關語句：
...
FLUSH TABLES
FLUSH TABLES WITH READ LOCK。
...
備份表可以執(zhí)行的語句：
整個實例變成了只讀，所有表的DDL和DML都會被阻塞，只能執(zhí)行查詢語句（SELECT）。
情況三，使用single-transaction
mysqldump -h127.0.0.1 -uwxp -p'wxp' --single-transaction test > dump.sql
一致性狀態(tài)：
所有庫的所有表都能保持一致性。
general日志相關語句：
...
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
SAVEPOINT sp
show tables
show table status like 'backup'
SET SQL_QUOTE_SHOW_CREATE=1
SET SESSION character_set_results = 'binary'
show create table `backup`
SET SESSION character_set_results = 'utf8'
show fields from `backup`
show fields from `backup`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `backup`
SET SESSION character_set_results = 'binary'
use `test`
select @@collation_database
SHOW TRIGGERS LIKE 'backup'
SET SESSION character_set_results = 'utf8'
ROLLBACK TO SAVEPOINT sp
...
設置會話級別為RR，然后開啟一個會話。這里開啟會話的時候多了一個WITH CONSISTENT SNAPSHOT，這個很關鍵。
官方文檔關于這兩個的區(qū)別
START TRANSACTION
If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table.
下面用例子來展示上面的意思，會話隔離級別都是REPEATABLE READ。
會話1: 會話2:
select * from backup; START TRANSACTION;
Empty set (0.00 sec)
insert into backup() values(now());
Query OK, 1 row affected, 1 warning (0.00 sec)
select * from backup;
+------------+
| shijian |
+------------+
| 2020-05-09 |
+------------+
-------------------------------------------------------------------------------------------------------
select * from backup;
+------------+
| shijian |
+------------+
| 2020-05-09 |
+------------+
-------------------------------------------------------------------------------------------------------
insert into backup() values(now());
Query OK, 1 row affected, 1 warning (0.00 sec)
select * from backup;
+------------+
| shijian |
+------------+
| 2020-05-09 |
| 2020-05-09 |
+------------+
2 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------------------
select * from backup;
+------------+
| shijian |
+------------+
| 2020-05-09 |
+------------+
1 row in set (0.00 sec)
會話1一次插入一條記錄，總共插入兩次。會話2只能看到第一條記錄，也就是說只要執(zhí)行了select語句，回話2能查詢到的數(shù)據(jù)就會保持一致。如果我們添加了/*!40100 WITH CONSISTENT SNAPSHOT */，那么會話2這兩條記錄是都看不到的，效果就像是從會話一開始就自動執(zhí)行了select * from backup。這樣就保證了整個備份期間數(shù)據(jù)都是一致的。
備份表可以執(zhí)行的語句：
備份庫當中所有表都可以并發(fā)的執(zhí)行DML和查詢語句（SELECT）。但是DDL有一些特殊。
可以分為三種種情況。如果是備份該表前，DDL可以成功執(zhí)行，但是到具體備份這張表的時候會出錯 ERROR 1412 (HY000): Table definition has changed, please retry transaction。在備份的過程當中時，會被阻塞。
admin@localhost [performance_schema] 10:15:42>select * from metadata_locks where object_schema='test';
+---------------+-------------+---------------------+---------------+-------------+-----------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+---------------+-------------+---------------------+---------------+-------------+-----------------+
| test | backup | SHARED_READ | TRANSACTION | GRANTED | 818988 |
| test | backup | EXCLUSIVE | TRANSACTION | PENDING | 818989 |
+---------------+-------------+---------------------+---------------+-------------+-----------------+
從上面的通用日志中可以看出，在剛開始備份一張表的時候，都會創(chuàng)建一個SAVEPOINT，備份完畢以后就會回滾到這個SAVEPOINT。在回滾以后就可以執(zhí)行DDL語句的。
總結
在使用mysqldump備份的時候，可以使用lock-tables，lock-all-tables，single-transaction三個參數(shù)來控制表的一致性問題。lock-tables和lock-all-tables都是通過顯示的加上只讀鎖來確保表的一致性。只有single-transaction通過MVCC來確保表的一致性，并且可以并發(fā)的執(zhí)行DML和DDL。大家在備份的時候一定要先了解自己備份的具體需求和備份實例可以接受什么樣的影響，小心的選擇這三個參數(shù)。
