MySQL Backup mysqldump備份流程學習

正文我們都知道MySQL邏輯備份工具 mysqldump 可以保證備份數據的一致性，但是它是怎麼保持一致性的？本文不討論 mysqldump 具體的選項和用法，一直對 mysqldump 的工作機制梳理的不太清楚，這篇主要來分析下 mysqldump 的工作原理和工作步驟，瞭解它為什麼可以獲取一 ...

正文

我們都知道MySQL邏輯備份工具mysqldump可以保證備份數據的一致性，但是它是怎麼保持一致性的？

本文不討論mysqldump具體的選項和用法，一直對mysqldump的工作機制梳理的不太清楚，這篇主要來分析下mysqldump的工作原理和工作步驟，瞭解它為什麼可以獲取一致性的備份。

關於mysqldump常用選項說明與用法參考另一篇博文：MySQL Backup mysqldump 常用選項與主要用法

通過打開general log的方法來記錄mysqldump備份的過程。

前期準備

開啟general log

(root@localhost) [(none)] > set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)] > show global variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /data/mysql/3306/data/dbabd.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)

mysqldump執行全庫備份

# mysqldump -uadmin -p -h192.168.58.3 -P3306 -E -R --triggers --single-transaction --master-data=2 -A > test_all.sql

分析general log日誌

開頭部分

53 Connect   admin@dbabd on  using TCP/IP
53 Query     /*!40100 SET @@SQL_MODE='' */
53 Query     /*!40103 SET TIME_ZONE='+00:00' */
53 Query     FLUSH /*!40101 LOCAL */ TABLES
53 Query     FLUSH TABLES WITH READ LOCK
53 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
53 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
53 Query     SHOW VARIABLES LIKE 'gtid\_mode'
53 Query     SHOW MASTER STATUS
53 Query     UNLOCK TABLES
53 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
53 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
53 Query     SHOW DATABASES
53 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'

備份連接，設置sql_mode為''，設置time_zone

53 Connect   admin@dbabd on  using TCP/IP
53 Query     /*!40100 SET @@SQL_MODE='' */
53 Query     /*!40103 SET TIME_ZONE='+00:00' */

FLUSH TABLES

53 Query     FLUSH /*!40101 LOCAL */ TABLES

關閉所有的表，並強制關閉所有正在使用的表，同時也會移除所有query cache結果。

詳細說明可以參考官方文檔：FLUSH TABLES

根據官文文檔的描述：

FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_name ... WITH READ LOCK instead.

意思是如果有一個會話正在執行LOCK TABLES ... READ語句，另一個會話執行FLUSH TABLES語句是不允許的，會被阻塞。可以使用FLUSH TABLES ... WITH READ LOCK替代。

FLUSH TABLES WITH READ LOCK

53 Query     FLUSH TABLES WITH READ LOCK

關閉所有打開的表並且對所有資料庫表加一個全局讀鎖。

詳細說明可以參考官方文檔：FLUSH TABLES WITH READ LOCK

根據官方文檔的描述

FLUSH TABLES WITH READ LOCK acquires a global read lock rather than
table locks, so it is not subject to the same behavior as LOCK TABLES
and UNLOCK TABLES with respect to table locking and implicit commits:

UNLOCK TABLES implicitly commits any active transaction only if any
tables currently have been locked with LOCK TABLES. The commit does
not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK
because the latter statement does not acquire table locks.

Beginning a transaction causes table locks acquired with LOCK TABLES
to be released, as though you had executed UNLOCK TABLES. Beginning a
transaction does not release a global read lock acquired with FLUSH
TABLES WITH READ LOCK.

FLUSH TABLES WITH READ LOCK語句獲取的是一個全局讀鎖而不是進行鎖表，不像LOCK TABLES和UNLOCK TABLES語句的行為；
只要任何表當前被LOCK TABLES鎖住時，執行UNLOCK TABLES會隱式提交任何活動的事務，不過已執行FLUSH TABLES WITH READ LOCK再執行UNLOCK TABLES並不會進行提交，因為後續的語句並不會獲取表鎖；
開始一個事務會造成LOCK TABLES獲得的表鎖釋放，就像已經執行了UNLOCK TABLES。開始一個事務並不會造成FLUSH TABLES WITH READ LOCK獲取的全局讀鎖釋放。

設置會話隔離級別為REPEATABLE READ

53 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

開啟一致性快照事務

53 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

因為要開啟一致性快照事務，所以必須將務事務的隔離級別設置成REPEATABLE READ。所以便有了上面設置會話級隔離級別的語句。

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions.

根據官方文檔的描述，這一步是為了確保該事務開啟時之後讀取的快照都是基於同一個時刻的，同時獲取得到一個事務號，就是SELECT都能讀取到一開始同一個的READ VIEW，不受之後其他事務修改或者未提交事務的影響。

詳細說明可參考官方文檔：innodb-consistent-read

查看是否開啟GTID模式

53 Query     SHOW VARIABLES LIKE 'gtid\_mode'

獲取當前binlog的位置信息

53 Query     SHOW MASTER STATUS

釋放全局讀鎖

53 Query     UNLOCK TABLES

查看所有資料庫信息

53 Query     SHOW DATABASES

備份部分

來看下開始備份表的日誌，這裡選取test1為例說明：

53 Init DB   mysql
53 Query     SHOW CREATE DATABASE IF NOT EXISTS `mysql`
53 Query     SAVEPOINT sp
53 Query     show tables
53 Query     show table status like 'columns\_priv'
53 Query     SET SQL_QUOTE_SHOW_CREATE=1
53 Query     SET SESSION character_set_results = 'binary'
53 Query     show create table `columns_priv`
53 Query     SET SESSION character_set_results = 'utf8'
53 Query     show fields from `columns_priv`
53 Query     show fields from `columns_priv`
53 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `columns_priv`
53 Query     SET SESSION character_set_results = 'binary'
53 Query     use `mysql`
53 Query     select @@collation_database
53 Query     SHOW TRIGGERS LIKE 'columns\_priv'
53 Query     SET SESSION character_set_results = 'utf8'
53 Query     ROLLBACK TO SAVEPOINT sp
…………省略…………

53 Init DB   test1
53 Query     SHOW CREATE DATABASE IF NOT EXISTS `test1`
53 Query     SAVEPOINT sp
53 Query     show tables
53 Query     show table status like 't1'
53 Query     SET SQL_QUOTE_SHOW_CREATE=1
53 Query     SET SESSION character_set_results = 'binary'
53 Query     show create table `t1`
53 Query     SET SESSION character_set_results = 'utf8'
53 Query     show fields from `t1`
53 Query     show fields from `t1`
53 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
53 Query     SET SESSION character_set_results = 'binary'
53 Query     use `test1`
53 Query     select @@collation_database
53 Query     SHOW TRIGGERS LIKE 't1'
53 Query     SET SESSION character_set_results = 'utf8'
53 Query     ROLLBACK TO SAVEPOINT sp
53 Query     show events
53 Query     use `test1`
53 Query     select @@collation_database
53 Query     SET SESSION character_set_results = 'binary'
53 Query     SHOW FUNCTION STATUS WHERE Db = 'test1'
53 Query     SHOW PROCEDURE STATUS WHERE Db = 'test1'
…………省略…………

53 Init DB   test2
53 Query     SHOW CREATE DATABASE IF NOT EXISTS `test2`
53 Query     SAVEPOINT sp
53 Query     show tables
53 Query     show table status like 't1'
53 Query     SET SQL_QUOTE_SHOW_CREATE=1
53 Query     SET SESSION character_set_results = 'binary'
53 Query     show create table `t1`
53 Query     SET SESSION character_set_results = 'utf8'
53 Query     show fields from `t1`
53 Query     show fields from `t1`
53 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
53 Query     SET SESSION character_set_results = 'binary'
53 Query     use `test2`
53 Query     select @@collation_database
53 Query     SHOW TRIGGERS LIKE 't1'
53 Query     SET SESSION character_set_results = 'utf8'
53 Query     ROLLBACK TO SAVEPOINT sp
53 Query     RELEASE SAVEPOINT sp
53 Query     show events
53 Query     use `test2`
53 Query     select @@collation_database
53 Query     SET SESSION character_set_results = 'binary'
53 Query     SHOW FUNCTION STATUS WHERE Db = 'test2'
53 Query     SHOW PROCEDURE STATUS WHERE Db = 'test2'
53 Query     SET SESSION character_set_results = 'utf8'
53 Quit

查看建庫語句，所有資料庫進行迴圈順序備份

53 Init DB   test1
53 Query     SHOW CREATE DATABASE IF NOT EXISTS `test1`

創建檢查點

53 Query     SAVEPOINT sp

創建一個檢查點，檢查點的作用是在一個事務中執行ROLLBACK TO SAVEPOINT語句之後能夠將事務回滾到檢查點位置而不中止事務。

詳細說明可參考官方文檔：SAVEPOINT

mysqldump備份是通過執行SELECT進行的，但是SELECT語句執行沒結束同時會持有該對象的MDL鎖，為了保證在備份期間不影響已經備份表的DDL操作被阻塞，所以就有了SAVEPOINT，每次備份完一張表就將事務回滾到SAVEPOINT的位置，同時這個操作會釋放該表的MDL鎖，但這並不會中止這個事務，其他事務可以對這張表進行DDL操作。

獲取表的狀態信息

53 Query     show table status like 'columns\_priv'

設置字元集為binary

53 Query     SET SESSION character_set_results = 'binary'

mysqldump為了更好的備份表結構，將字元集先設置成binary，避免出錯。

備份建表語句

53 Query     show tables
53 Query     show table status like 't1'

設置字元集為utf8

53 Query     SET SESSION character_set_results = 'utf8'

開始備份表數據時將字元集設置為資料庫的字元集。

獲取表的欄位信息

53 Query     show fields from `t1`

開始備份表

53 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

開始備份觸發器

53 Query     SHOW TRIGGERS LIKE 't1'

回滾到檢查點

53 Query     ROLLBACK TO SAVEPOINT sp

以上一張表就算備份完成，接下去就是迴圈上面步驟備份完該資料庫下所有的表。

完成所有表的備份之後，最後備份的資料庫中的事件、函數、存儲過程：

53 Query     show events
53 Query     use `test1`
53 Query     select @@collation_database
53 Query     SET SESSION character_set_results = 'binary'
53 Query     SHOW FUNCTION STATUS WHERE Db = 'test1'
53 Query     SHOW PROCEDURE STATUS WHERE Db = 'test1'

至此，一個資料庫的備份完成，開始備份其他資料庫。

結尾部分

來看下結尾部分的日誌

53 Query     ROLLBACK TO SAVEPOINT sp
53 Query     RELEASE SAVEPOINT sp

當備份完最後一個資料庫的最後一張表後釋放了檢查點，再完成最後一個資料庫事件、函數和存儲過程的備份之後進行退出，退出預設會進行提交操作，所有備份結束。

總結

通過以上的日誌分析，可以總結下mysqldump備份的主要流程：

一開始執行FLUSH TABLES關閉實例中所有的表；
執行語句FLUSH TABLES WITH READ LOCK獲取全局表的讀鎖，保證表一致性；
設置會話級別事務的隔離級別為REPEATABLE READ，保證事務期間數據的一致性；
執行語句START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */創建一個一致性事務快照；
查看是否開啟了GTID；
獲取當前狀態下的binlog文件及位置信息(如有指定選項 --master-data)；
執行UNLOCK TABLES釋放全局表讀鎖；
開始備份第一個資料庫數據，為事務創建一個檢查點，備份完一張表之後還原至檢查點再接著備份下一張表，直至該資料庫所有的表備份完成，接著備份下一個資料庫數據，直至所有資料庫數據備份完成；
當備份完最後一個資料庫數據後釋放檢查點，退出並中止事務。