mysqldump備份時的資料一致性問題--single-transaction

lhrbest發表於2020-05-13

mysqldump備份時的資料一致性問題--lock-tables，lock-all-tables，single-transaction

在日常運維當中，經常會用到mysqldump。使用mysqldump匯出資料的時候，我們最關心的問題之一就是表的一致性。簡單的說就是所有表是不是同一時間的資料和結構。隨著備份引數的不同，表的一致性和對資料庫的影響也會不一樣。

測試的mysqldump版本

mysqldump Ver 10.13 Distrib 5.7.22-22, for Linux (x86_64)

由於mysiam引擎的退休，這裡預設談論的都是innodb引擎的表。

情況一，在不使用任何其他引數的情況下

mysqldump -h127.0.0.1 -uwxp -p'wxp'  test  > dump.sql

很簡單，只是指定了連線地址，賬號密碼，和需要匯出的資料庫。在沒有指定引數的情況下，預設會使用lock-tables引數。官方文件引數解釋如下：

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.

一致性狀態：

單個庫裡的所有表都保持一致性，庫之間的表不一定能保證一致性。

general日誌相關語句：

...LOCK TABLES `backup` READ /*!32311 LOCAL */,`t` READ /*!32311 LOCAL */,`t1` READ /*!32311 LOCAL */...UNLOCK TABLES

在備份一開始就顯示的一次性給所有的表加上讀鎖，讓庫在備份期間變成只讀來確保表的一致性。由於是一個庫一個庫的備份，多個庫之間的表是不一定存在一致性的。舉個誇張的例子，同時導a,b兩個庫，a庫裡面的表可能是下午三點的狀態，而b庫裡面的表卻是下午4點鐘的狀態。

備份表可以執行的語句:

正在備份的庫當中所有表的併發DML，DDL都會被阻塞，只能執行查詢語句（SELECT）。

情況二，使用lock-all-tables

mysqldump -h127.0.0.1 -uwxp -p'wxp' --lock-all-tables test  > dump.sql

官方引數解釋

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.

一致性狀態：

所有庫的所有表都能保持一致性。

general日誌相關語句：

...FLUSH TABLESFLUSH TABLES WITH READ LOCK。...

備份表可以執行的語句：

整個例項變成了只讀，所有表的DDL和DML都會被阻塞，只能執行查詢語句（SELECT）。

情況三，使用single-transaction

mysqldump -h127.0.0.1 -uwxp -p'wxp' --single-transaction  test  > dump.sql

一致性狀態：

所有庫的所有表都能保持一致性。

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，這個很關鍵。官方文件關於這兩個的區別

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只能看到第一條記錄，也就是說只要執行了select語句，回話2能查詢到的資料就會保持一致。如果我們新增了/*!40100 WITH CONSISTENT SNAPSHOT */，那麼會話2這兩條記錄是都看不到的，效果就像是從會話一開始就自動執行了select * from backup。這樣就保證了整個備份期間資料都是一致的。

備份表可以執行的語句：

備份庫當中所有表都可以併發的執行DML和查詢語句（SELECT）。但是DDL有一些特殊。

從上面的通用日誌中可以看出，在剛開始備份一張表的時候，都會建立一個SAVEPOINT，備份完畢以後就會回滾到這個SAVEPOINT。在回滾以前是無法執行DDL語句的。如果執行DDL，會產生如下的鎖阻塞。

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 |
+---------------+-------------+---------------------+---------------+-------------+-----------------+

總結

在使用mysqldump備份的時候，可以使用lock-tables，lock-all-tables，single-transaction三個引數來控制表的一致性問題。lock-tables和lock-all-tables都是通過顯示的加上只讀鎖來確保表的一致性。只有single-transaction通過MVCC來確保表的一致性，並且可以併發的執行DML和DDL。大家在備份的時候一定要先了解自己備份的具體需求和備份例項可以接受什麼樣的影響，小心的選擇這三個引數。

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號（
DB寶）上有同步更新

● 本文itpub地址：
http://blog.itpub.net/26736162 

● 本文部落格園地址：
http://www.cnblogs.com/lhrbest 

● 本文CSDN地址：
https://blog.csdn.net/lihuarongaini 

● 本文pdf版、個人簡介及小麥苗雲盤地址：
http://blog.itpub.net/26736162/viewspace-1624453/ 

● 資料庫筆試面試題庫及解答：
http://blog.itpub.net/26736162/viewspace-2134706/ 

● DBA寶典今日頭條號地址：
http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 

........................................................................................................................

● QQ群號：
230161599
、618766405

● 微 信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友
（
646634621
），註明新增緣由

● 於 2020-05-01 06:00 ~ 2020-05-30 24:00 在西安完成

● 最新修改時間：2020-05-01 06:00 ~ 2020-05-30 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

●
小麥苗的微店：
https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

●
小麥苗出版的資料庫類叢書：
http://blog.itpub.net/26736162/viewspace-2142121/

●
小麥苗OCP、OCM、高可用網路班：
http://blog.itpub.net/26736162/viewspace-2148098/

●
小麥苗騰訊課堂主頁：
https://lhr.ke.qq.com/

........................................................................................................................
使用

微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（
DB寶）及QQ群（DBA寶典）、新增小麥苗微 信，
學習最實用的資料庫技術。 


........................................................................................................................