--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>settx_isolation='repeatable-read';

QueryOK,0rowsaffected(0.00sec)

mysql>settx_isolation='repeatable-read';

QueryOK,0rowsaffected(0.00sec)

mysql>begin;

QueryOK,0rowsaffected(0.01sec)

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>settx_isolation='repeatable-read';

mysql>settx_isolation='repeatable-read';

mysql>select*fromt1;

Emptyset(0.00sec)

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>settx_isolation='repeatable-read';

mysql>settx_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 wherec1=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1Warnings: 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>settx_isolation='repeatable-read';

mysql>settx_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 withconsistent 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.FILESWHERE FILE_TYPE = 'UNDO LOG

' AND FILE_NAME IS NOT NULL ANDLOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCTLOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE'AN

D TABLESPACE_NAME IN (SELECT DISTINCTTABLESPACE_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 FROMINFORMATION_SCHEMA.FILES WHERE FILE

_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN(SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERETABLE_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