mysqldump --single-transaction一致性的研究

最新推荐文章于 2021-12-04 18:50:00 发布
最新推荐文章于 2021-12-04 18:50:00 发布
阅读量2.3k 收藏 2
点赞数 1
文章标签： 数据库 网络

--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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2652628/

conghe6716
关注
mysql-1-mysqldump之--single-transaction
qq_35528593的博客
07-20 1157
innodb引擎的数据库，在dump时加--single-transaction参数用来保证数据完整一致。 实际工作原理是设定本次会话的隔离级别为：REPEATABLE READ，来确保本次dump时，不会看到其他会话提交的数据。 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ Creates a consistent snap
mysqldump数据库备份无法进行操作只能查询 --single-transaction
weixin_42578316的博客
08-27 712
mysqldump数据库备份无法进行操作只能查询 --single-transaction一、先把线程堵塞看一下二、看一下数据库整在执行的sql三、修改mysqldump 备份 前因 ：线上部署的项目客户反馈总是登录不上程序，提示链接超时，项目是前后端分离的 前端一直等待后端返回，等待超过10分钟前端就给出提示链接超时。 一、先把线程堵塞看一下 使用jstack 导出无法登录堵塞的地方 先搜代码里面调用的方法名 根据方法名找执行到哪一步 发现是卡到了数据库这块，可是代码里面的查询登录名是否存在都没问
深入理解mysqldump原理 --single-transaction --lock-all-tables --master-data
eagle89的专栏
10-21 370
在mysqldump过程中，之前其实一直不是很理解为什么加了--single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加--lock-all-tables，前段时间抽空详细地查看了整个mysqldump过程。 理解master-data和--dump-slave --master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行； --master-data=1表示在dump过程中记录主库的binlo
【转载】mysqldump的single-transaction和master-data
weixin_30481087的博客
01-29 265
原文地址：mysqldump的single-transaction和master-data作者：myownstars 先看一下--lock-tables和--lock-all-tables --lock-all-tables 一次性锁定所有数据库的所有表，在整个dump期间一直获取global read lock； 该选项自动关闭—lock-tables和—singl...
深入理解mysqldump原理 --single-transaction --lock-all-tables --master-data
热门推荐
cug_jiang126com的专栏
11-13 5万+
在mysqldump过程中，之前其实一直不是很理解为什么加了--single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加--lock-all-tables，前段时间抽空详细地查看了整个mysqldump过程。 理解master-data和--dump-slave --master-data=2表示在dump过程中记录主库的binlog和po
mysqldump 导出指定表_mysqldump命令详解 Part 8 --single-transaction 参数的使用
weixin_39624716的博客
12-05 255
实验环境:MySQL 5.7.25 Redhat 6.10前面我们建立了数据库并建立相关的对象数据库表存储过程函数触发器事件今天的内容为--single-transaction的使用1. --single-transaction该参数通过在一个事务中导出所有表从而创建一个一致性的快照当前版本的MySQL只可以对innodb 引擎保证一致性，导出过程中不会锁表其他引擎，如MyISAM 在导出期间会锁...
MySQL--当mysqldump --single-transaction遇到alter table（1）
weixin_33816821的博客
07-05 121
部分生产环境采用mysqldump --single-transaction的方式在夜间进行数据库备份，而同事恰好在备份期间执行了alter table操作，操作部分成功部分失败，为啥呢？ ##========================================================================## 以下测试在MySQL 5.6.36上执行，该问题存在...
深入理解mysqldump参数 --single-transaction --lock-all-tables
MySQL
04-24 3753
在mysqldump过程中，之前其实一直不是很理解为什么加了--single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加--lock-all-tables，前段时间抽空详细地查看了整个mysqldump过程。 理解master-data和--dump-slave --master-data=2表示在dump过程中记录主库的binlog和
mysqldump master-data 加与不加single-transaction的事务一致性区别
w1346561235的博客
10-14 1716
这个通过mysql debug版本很容易就能看出来。 例如：通过如下命令执行mysqldump： mysqldump_d -uroot -hlocalhost -P10001 --protocol=tcp --all-databases --master-data --debug=d:t:o,mysqldump_d_119.debug --log-error=mysqldump_d_119.l...
MySQL备份之mysqldump工具--lock-all-tables、--single-transaction以及--lock-tables参数
Qiuht的技术博客
04-01 6938
mysql 备份时需要注意的参数和影响
mysql dump 选项_mysqldump 的–single-transaction选项
weixin_33186486的博客
01-27 1475
8月 24, 2014 |Nix.Huang--single-transaction这个选项设置事务的隔离级别为REPEATABLE READ，在导出数据之前向服务器发送一个START TRANSACTION SQL语句。这个选项只对支持事务的表有效，比如innodb表，因为它导出的数据的状态和 START TRANSACTION 执行时数据库的状态是一致，且不会阻塞其他的应用程序。当使用这个选项...
--single-transaction 参数对应MyISAM引擎和InnoDB引擎
weixin_34401479的博客
01-11 109
结论：使用--single-transaction 备份含有MyISAM的表不会获得一致性备份，所有的innodb表可以获得事务开始时的一致性快照，但是MyISAM表获取的是备份该表时的最新快照，测试库：test，包含表t1,t2,t3,t4,t5,t6除t6是MyISAM表之外，其他均为innodb表表t1,t2 数据量相同，均为288W，其他t3,t4,t5,t6 均...
mysqldump命令之single-transaction
weixin_30481087的博客
03-06 292
=========================================================在mysqldump中指定single-transaction时，会使用可重复读(REPEATABLE READ)事务隔离级别来保证整个dump过程中数据一致性，该选项仅对InnoDB表有用，且不能与ALTER TABLE/CREATE TABLE/DROP TABLE/RE...
mysqldump 使用–single-transaction
zhaoyangjian724的专栏
06-10 2000
注意：innodb用 –single-transaction, myisam需要用 –lock-all-tables。 mysqldump -uroot -p'R00t,uHagt.0511' -R --single-transaction --master-data=2 --all-database >slave.sql -R, --routines Dump stored
Mysql备份原理与工具mysqldump之--single-transaction，--master-data
小楼一夜听春雨，深巷明朝卖杏花
06-16 1万+
MySQL备份原理 数据备份是数据安全的底线，在任何场景下面，无论是上线后的数据回滚，数据丢失都能够有效的止损，将损失降到最低程度。 衡量数据备份与恢复的两个重要指标： 对于
mysqldump命令详解 --single-transaction参数的使用
hbhe0316的专栏
12-04 1211
[root@mysql57 data]# mysqldump --help --single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works O...
Mysqldump --single-transaction 选项解析
weixin_33984032的博客
09-07 354
一、解析查看general_log： 206 Query SETSESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ##更改隔离级别（采取快照，保证了事务中一致性读） 206 Query START TRANSACTION/*!40100 WITH CONSISTENT SNAPSH...
mysqldump的single-transaction
雅冰石的专栏
04-06 7115
先看一下--lock-tables和--lock-all-tables  --lock-all-tables 一次性锁定所有数据库的所有表，在整个dump期间一直获取global read lock； 该选项自动关闭—lock-tables和—single-transaction； --lock-tables 以database为单位，dump前锁定其下所有表；如果是
热备份数据库 mysqldump -uroot -p -R --single-transaction --master-data=1 --databases test > master_2016080
snowpay的博客
08-03 2089
mysqldump -uroot -p -R --single-transaction --master-data=1 --databases test > master_20160803.sql 热备份主库的数据库   mysqldump是一款MySQL逻辑备份的工具，他将数据库里面的对象（表）导出成SQL脚本文件。有点类似于SQL SEVER的“任务-生成脚本”的逻辑备份功能。mysqld
--master-data与--single-transaction结合使用
最新发布
07-10
`--master-data` 和 `--single-transaction` 是两个 mysqldump 命令的选项，可以结合使用以实现更一致的备份。 `--master-data` 参数用于在备份文件中添加二进制日志文件和位置信息，以便在恢复时确定备份点。它可以通过设置不同的值来控制额外添加的信息量。 `--single-transaction` 参数用于在备份过程中开启一个事务，并使用一致性读取来获取数据。这意味着备份过程中的数据读取不会被其他事务的修改所影响，确保备份的一致性。 结合使用 `--master-data` 和 `--single-transaction` 可以达到以下效果： 1. 保证备份的一致性：使用 `--single-transaction` 可以确保备份过程中的数据读取一致，避免了其他事务对数据的修改。这对于需要备份大型数据库或者备份过程需要较长时间的情况非常有用。 2. 提供备份点信息：使用 `--master-data` 可以在备份文件中添加二进制日志文件和位置信息。结合 `--single-transaction`，可以在备份时获取一个一致性的备份点，并在恢复时可以方便地确定备份点进行恢复操作。 以下是使用 `--master-data` 和 `--single-transaction` 结合进行备份的示例命令： ``` mysqldump --master-data=2 --single-transaction -u <username> -p <database> > backup.sql ``` 在备份完成后，备份文件 backup.sql 中将包含二进制日志文件和位置信息的注释，并且备份点是在一个一致性的状态下获取的。 综上所述，结合使用 `--master-data` 和 `--single-transaction` 可以实现一致性备份，并提供备份点信息，以便在恢复时进行准确的还原。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值