Shulou(Shulou.com)06/01 Report--

-- single-transaction means that before starting dump, set the isolation level to RR, and start transaction. The exported data is a consistent snapshot by encapsulating the export operation in a Repeatable Read.

Test the database version:

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.26-log |

+-+

1 row in set (0.00 sec)

Test 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

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

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)

As you can see, consistent reads under the RR isolation level do not establish the snapshot at the point in time of the transactional begin. After a simple begin, the transactions of other sessions can still be read before the first read, but after the first reading of the data, the read transactions no longer change.

Test 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

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)

Consistent reads under the RR isolation level occur during the first select in a transaction. I tried to replace the select statement of session A with the delete statement and found that the transaction of session B was still read.

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

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)

After Session A first select established a consistent read, session B inserted the data, but the select of session A still could not read it, but the update was updated to the data inserted by session B because it was currently read.

So begin and start transaction are signs of the start of a transaction, but not the starting point of a transaction. If you want to use start transaction as the time point for the start of the transaction, you must use:

This is how snapshots in START TRANSACTION WITH consistent snapshot # mysqldump are implemented.

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

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)

Initiate backup

Mysqldump-uroot-poracle-single-transaction-- master-data=1-R-E-- triggers-B ming-- ignore_table=ming.test02 > / tmp/ming_st.sql

View the general log of the backup process:

[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`

.

As you can see, the general implementation process of mysqldump is as follows: connect-> initialize information-> refresh table (lock table)-> open transaction (consistency snapshot)-> record offset-> unlock table.

Reference: https://yq.aliyun.com/articles/552972?spm=a2c4e.11153940.0.0.18a12415csEBjM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.