MySQL Backup mysqldump備份流程學習

目錄

• 前期準備
• 開啟general log
• mysqldump執行全庫備份
• 分析general log日誌
• 開頭部分
• 備份部分
• 結尾部分
• 總結
• 參考

正文

我們都知道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.

1. FLUSH TABLES WITH READ LOCK語句獲取的是一個全局讀鎖而不是進行鎖表，不像LOCK TABLES和UNLOCK TABLES語句的行為；
2. 只要任何錶當前被LOCK TABLES鎖住時，執行UNLOCK TABLES會隱式提交任何活動的事務，不過已執行FLUSH TABLES WITH READ LOCK再執行UNLOCK TABLES並不會進行提交，因為後續的語句並不會獲取表鎖；
3. 開始一個事務會造成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備份的主要流程：

1. 一開始執行FLUSH TABLES關閉實例中所有的表；
2. 執行語句FLUSH TABLES WITH READ LOCK獲取全局表的讀鎖，保證表一致性；
3. 設置會話級別事務的隔離級別為REPEATABLE READ，保證事務期間數據的一致性；
4. 執行語句START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */創建一個一致性事務快照；
5. 查看是否開啟了

   GTID

   ；
6. 獲取當前狀態下的binlog文件及位置信息(如有指定選項

   --master-data

   )；
7. 執行UNLOCK TABLES釋放全局表讀鎖；
8. 開始備份第一個資料庫數據，為事務創建一個檢查點，備份完一張表之後還原至檢查點再接著備份下一張表，直至該資料庫所有的表備份完成，接著備份下一個資料庫數據，直至所有資料庫數據備份完成；
9. 當備份完最後一個資料庫數據後釋放檢查點，退出並中止事務。

☆〖本人水平有限，文中如有錯誤還請留言批評指正！〗☆

作者: H_Johnny

出處: http://www.cnblogs.com/dbabd/

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！