mysqldump single-transaction一致性的研發

2024-07-24 12:34:52

字體：大中小

來源：轉載

供稿：網友

　　--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 的大致實現過程是：連接 -> 初始化信息 -> 刷新表（鎖表） -> 開啟事務（一致性快照） -> 記錄偏移量 -> 解鎖表

（編輯：武林網）

上一篇：MySQL的幾個圖形化界面用具

下一篇：MySQL count 之索引挑選