mysqldump --single-transaction一致性的研究
--single-transaction 的含義是，在開始 dump 前，設定隔離級別為 RR ，並且 start transaction. 通過將匯出操作封裝在一個事務 (Repeatable Read) 內來使得匯出的資料是一個一致性快照。
測試資料庫版本：
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
測試 1 ：
|
sesseion A |
session B |
|
mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)
|
mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)
|
|
mysql> begin; Query OK, 0 rows affected (0.01 sec) |
|
|
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec)
mysql> insert into test01 select 4,4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec) |
|
|
|
mysql> insert into test01 select 5,5; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec) |
|
可以看到， RR 隔離級別下的一致性讀，不是以事務 begin 的時間點建立 snapshot 的。單純 begin 後，到第一次讀取前之間其他會話的事務還是可以讀取的，但是在第一次讀取資料完成後，讀取到的事務就不再變化了。
測試 2 ：
|
session A |
session B |
|
mysql> set tx_isolation='repeatable-read'; |
mysql> set tx_isolation='repeatable-read'; |
|
|
mysql> select * from t1; Empty set (0.00 sec) |
|
mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec) |
|
|
|
mysql> insert into test01 select 6,6; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec) |
|
RR 隔離級別下的一致性讀，發生在事務裡面第一次 select 的時候。我試了下用 delete 語句替換 session A 的 select 語句，結果發現還是會讀取到 session B 的事務。
|
session A |
session B |
|
mysql> set tx_isolation='repeatable-read'; |
mysql> set tx_isolation='repeatable-read'; mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec) |
|
mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec) |
|
|
|
mysql> insert into test01 select 4,4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec) |
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec) |
|
|
mysql> update test01 set c2=5 where c1=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 5 | +----+------+ 4 rows in set (0.00 sec) |
|
Session A 第一次 select 建立一致性讀後， session B 插入資料， session A 的 select 仍然讀不到，但是 update 因為是當前讀，所以更新到 session B 插入的資料。
所以 begin 和 start transaction 是事務開始的標誌，但不是事務開始的起點。如果要將 start transaction 作為事務開始的時間點，那麼必須使用：
START TRANSACTION WITH consistent snapshot ###mysqldump 中的快照就是用這個實現的
|
mysql> set tx_isolation='repeatable-read'; |
mysql> set tx_isolation='repeatable-read'; |
|
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec) |
|
mysql> start transaction with consistent snapshot; Query OK, 0 rows affected (0.00 sec) |
|
|
|
mysql> insert into test01 select 4,4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
mysql> select * from test01; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec) |
|
發起備份
mysqldump -uroot -poracle --single-transaction --master-data=1 -R -E --triggers -B ming --ignore_table=ming.test02 > /tmp/ming_st.sql
檢視備份過程的 general log ：
|
[root@oradb-2062 binlog]# more /u01/mysql/3306/data/oradb-2062.log /u01/mysql_57/bin/mysqld, Version: 5.7.26-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /u01/mysql/3306/data/mysqld.sock Time Id Command Argument 2019-08-01T08:30:50.718358Z 12 Query show variables like 'log_output' 2019-08-01T08:31:33.211254Z 14 Connect root@localhost on using Socket 2019-08-01T08:31:33.211413Z 14 Query /*!40100 SET @@SQL_MODE='' */ 2019-08-01T08:31:33.211474Z 14 Query /*!40103 SET TIME_ZONE='+00:00' */ 2019-08-01T08:31:33.211565Z 14 Query FLUSH /*!40101 LOCAL */ TABLES 2019-08-01T08:31:33.212009Z 14 Query FLUSH TABLES WITH READ LOCK 2019-08-01T08:31:33.212047Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2019-08-01T08:31:33.212070Z 14 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 2019-08-01T08:31:33.212115Z 14 Query SHOW VARIABLES LIKE 'gtid\_mode' 2019-08-01T08:31:33.216296Z 14 Query SHOW MASTER STATUS 2019-08-01T08:31:33.216472Z 14 Query UNLOCK TABLES 2019-08-01T08:31:33.219582Z 14 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 AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AN D TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('ming'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, I NITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 2019-08-01T08:31:33.223184Z 14 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE _TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('ming')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 2019-08-01T08:31:33.223727Z 14 Query SHOW VARIABLES LIKE 'ndbinfo\_version' 2019-08-01T08:31:33.225502Z 14 Init DB ming 2019-08-01T08:31:33.225545Z 14 Query SHOW CREATE DATABASE IF NOT EXISTS `ming` 2019-08-01T08:31:33.225683Z 14 Query SAVEPOINT sp 2019-08-01T08:31:33.225750Z 14 Query show tables 2019-08-01T08:31:33.225957Z 14 Query show table status like 'mytest01' 2019-08-01T08:31:33.226083Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1 2019-08-01T08:31:33.226114Z 14 Query SET SESSION character_set_results = 'binary' 2019-08-01T08:31:33.226145Z 14 Query show create table `mytest01` 2019-08-01T08:31:33.226190Z 14 Query SET SESSION character_set_results = 'utf8' 2019-08-01T08:31:33.226226Z 14 Query show fields from `mytest01` 2019-08-01T08:31:33.226468Z 14 Query show fields from `mytest01` 2019-08-01T08:31:33.226687Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `mytest01` 2019-08-01T08:31:33.226810Z 14 Query SET SESSION character_set_results = 'binary' 2019-08-01T08:31:33.226844Z 14 Query use `ming` 2019-08-01T08:31:33.226877Z 14 Query select @@collation_database 2019-08-01T08:31:33.226920Z 14 Query SHOW TRIGGERS LIKE 'mytest01' 2019-08-01T08:31:33.227098Z 14 Query SET SESSION character_set_results = 'utf8' 2019-08-01T08:31:33.227130Z 14 Query ROLLBACK TO SAVEPOINT sp 2019-08-01T08:31:33.227162Z 14 Query show table status like 'test01' 2019-08-01T08:31:33.227262Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1 2019-08-01T08:31:33.227289Z 14 Query SET SESSION character_set_results = 'binary' 2019-08-01T08:31:33.227316Z 14 Query show create table `test01` 2019-08-01T08:31:33.227356Z 14 Query SET SESSION character_set_results = 'utf8' 2019-08-01T08:31:33.227389Z 14 Query show fields from `test01` 2019-08-01T08:31:33.227730Z 14 Query show fields from `test01` 2019-08-01T08:31:33.227911Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `test01` 2019-08-01T08:31:33.228005Z 14 Query SET SESSION character_set_results = 'binary' 2019-08-01T08:31:33.228053Z 14 Query use `ming` 2019-08-01T08:31:33.228084Z 14 Query select @@collation_database 2019-08-01T08:31:33.228143Z 14 Query SHOW TRIGGERS LIKE 'test01' 2019-08-01T08:31:33.228336Z 14 Query SET SESSION character_set_results = 'utf8' 2019-08-01T08:31:33.228369Z 14 Query ROLLBACK TO SAVEPOINT sp 2019-08-01T08:31:33.228399Z 14 Query show table status like 'test03' 2019-08-01T08:31:33.228501Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1 2019-08-01T08:31:33.228686Z 14 Query SET SESSION character_set_results = 'binary' 2019-08-01T08:31:33.228726Z 14 Query show create table `test03` 。。。。。。 |
可以看到， mysqldump 的大致實現過程是：連線 -> 初始化資訊 -> 重新整理表（鎖表） -> 開啟事務（一致性快照） -> 記錄偏移量 -> 解鎖表
參考：https://yq.aliyun.com/articles/552972?spm=a2c4e.11153940.0.0.18a12415csEBjM
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31480688/viewspace-2652628/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysqldump備份時的資料一致性問題--single-transactionMySql
- mysqldump --single-transaction引數的作用MySql
- [轉帖]mysqldump --master-data=2 --single-transactionMySqlAST
- 深入理解mysqldump原理 --single-transaction --lock-all-tables --master-dataMySqlAST
- mysqldump備份時如何保持資料的一致性MySql
- raft演算法一致性的研究Raft演算法
- 【mysqldump】mysqldump及備份恢復示例MySql
- mysqldump的最佳實踐MySql
- Mysqldump的備份流程MySql
- MySQLDump的備份方法MySql
- mysqldump原理分析MySql
- mysqldump小談MySql
- Using mysqldump for backupsMySql
- mysqldump的內部實現原理MySql
- 簡單的mysqldump備份（windows）MySqlWindows
- mysqldump 深入淺出MySql
- mysqldump Got error: 1045MySqlGoError
- mysqldump Got error 1290MySqlGoError
- mysqldump 使用規範MySql
- mysqldump匯出報錯"mysqldump: Error 2013 ... during query when dumping tableMySqlError
- mysqldump引數說明MySql
- MYSQL-mysqldump學習MySql
- mysqldump: Error: Binlogging on server not activeMySqlErrorServer
- mysqldump備份技巧分享MySql
- MySQL：MTS和mysqldump死鎖MySql
- windows mysqldump備份指令碼WindowsMySql指令碼
- mysql 大表mysqldump遷移方案MySql
- mysqldump 資料庫備份程式MySql資料庫
- mysqldump error1066 錯誤的解決辦法MySqlError
- 運用mysqldump 工具時需要注意的問題MySql
- Mysqldump實現mysql的master-slave主從複製MySqlAST
- mysql5.6 mysqldump備份報錯MySql
- mysqldump從mysql遷移資料到OceanBaseMySql
- Mysqldump 匯出表結構異常MySql
- MySQL主從配置及mysqldump備份MySql
- mysqldump 恢復單個資料庫MySql資料庫
- mysqldump 欄位值帶單引號MySql
- Mysqldump匯出亂碼問題排查MySql