mysqldump備份時的數據一致性問題--single-transaction

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.

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

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

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

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

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.

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

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

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

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

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

... 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。大家在備份的時候一定要先了解自己備份的具體需求和備份實例可以接受什么樣的影響，小心的選擇這三個參數。

數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。