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.
一致性狀態:
單個庫裡的所有表都保持一致性,庫之間的表不一定能保證一致性。
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。大家在備份的時候一定要先了解自己備份的具體需求和備份例項可以接受什麼樣的影響,小心的選擇這三個引數。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2691903/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysqldump備份時如何保持資料的一致性MySql
- mysqldump備份時加single-transaction會不會加鎖MySql
- mysqldump備份時使用single-transaction會不會加鎖???MySql
- mysqldump --single-transaction一致性的研究MySql
- mysqldump 資料庫備份程式MySql資料庫
- mysql 備份資料庫 mysqldumpMySql資料庫
- mysqldump 進行資料備份MySql
- MySQLDump的備份方法MySql
- Mysqldump的備份流程MySql
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- mysqldump來備份MYSQL資料庫(指令碼)MySql資料庫指令碼
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- Linux基礎命令---mysqldump資料庫備份LinuxMySql資料庫
- 資料庫一致性備份資料庫
- 什麼是備份資料庫?什麼是資料庫一致性備份和非一致性備份?資料庫
- 【mysqldump】mysqldump及備份恢復示例MySql
- mysqldump --single-transaction引數的作用MySql
- mysqldump備份技巧分享MySql
- mysqldump 備份指令碼MySql指令碼
- mysqldump備份指令碼MySql指令碼
- 邏輯備份--mysqldumpMySql
- 詳解MySQL資料備份之mysqldump使用方法MySql
- 使用mysqldump進行mysql資料庫備份還原MySql資料庫
- RMAN備份中不同版本是否備份空資料塊的問題
- mysqldump使用方法(MySQL資料庫的備份與恢復)MySql資料庫
- MySQL資料庫mysqldump命令備份異常的一個案例MySql資料庫
- 簡單的mysqldump備份(windows)MySqlWindows
- 資料庫非一致性備份資料庫
- Mysqldump備份說明及資料庫備份指令碼分享-運維筆記MySql資料庫指令碼運維筆記
- mysqldump的single-transaction和master-dataMySqlAST
- Oracle資料庫備份、災備的23個常見問題Oracle資料庫
- windows mysqldump備份指令碼WindowsMySql指令碼
- mysqldump備份原理解析MySql
- mysql 邏輯備份 (mysqldump)MySql
- mysqldump單表備份加--single-transaction --master-data=2引數執行過程的內部剖析MySqlAST
- [BUG反饋]ot資料庫備份問題資料庫
- Mysqldump備份的基本流程介紹MySql
- mysqldump備份不輸入密碼直接備份MySql密碼