MySQL-当mysqldump -single-transaction碰到alter table（2）_mysql

mysql–当mysqldump –single-transaction遇到alter table（2）

在上篇《MySQL–当mysqldump –single-transaction遇到alter table》中测试发现，在MySQL 5.6版本中，如果在mysqldump期间修改表，可能会导致mysqldump报错，而该结论与同事的执行现象不符，因此在MySQL 5.5版本中进行下测试。

测试环境：

MySQL 5.5.14

在数据库testdb01下有表tb1001，当前表中存有两条数据：

##=========================================================##

发现竟然返回的是空集，没有任何报错。

这也就合理解释我同事操作的现象：在mysqldump过程中，修改表结构，修改操作没有被阻塞，mysqldump操作也”正常完成“。

由于SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1001`操作没有返回错误也没有返回数据，mysqldump进程会将tb1001当做一个空表来处理，然后继续导出后面的表直至导出所有的表然后返回执行成功的状态。但导出的备份已经缺失tb1001的数据，如果恰好采用该备份去恢复数据，那么必然最终导致“数据丢失”。

解决办法：

在对MySQL 5.5版本进行修改表操作前，先检查当前服务器是否在进行mysqldump操作，避免两者并行执行。

如果对mysqldump已经导出过的表进行修改操作，修改操作会被阻塞，直到mysqldump结束，该情况与MySQL 5.6版本一致。

##=========================================================================================##

总结：

对于MySQL 5.5版本，mysqldump与表修改操作同时执行：

如果修改表操作在 ”mysqldump开启后但还未导出修改表数据前“ 的时间段内开始，则修改表操作成功完成，而mysqldump不会执行失败，但是无法正常导出修改表的数据；

如果修改表操作在 “mysqldum已导出修改表数据但还未结束mysqldump操作前”的时间段内开始，则修改表操作被阻塞，mysqldum能成功完成，在mysqldump操作完成后修改表操作方可正常执行。

对于MySQL 5.5版本，应该避免mysqldump和修改表操作同时进行，以避免备份丢失修改表的数据，造成数据不一致！

##=========================================================================================##

2楼xiaohuazi

执行mysqldump的时候，2gt;gt;/tmp/mysqldumperror.txt，前面通过START TRANSACTION WITH CONSISTENT SNAPSHOT开启的事务只能通过commit或者rollback来结束，而不是ROLLBACK TO SAVEPOINT sp。，， 其实，这样做不会阻塞在备份期间对已经备份表的ddl操作。，，，/**， ROLLBACK TO SAVEPOINT in –single-transaction mode to release metadata， lock on table which was already dumped. This allows to avoid blocking， concurrent DDL on this table without sacrificing correctness, as we， won#39;t access table second time and dumps created by –single-transaction， mode have validity point at the start of transaction anyway.， Note that this doesn#39;t make –single-transaction mode with concurrent， DDL safe in general case. It just improves situation for people for whom， it might be working. */，，下面具体来测试一下：，，第一种情况：，，会话1发起事务，并查询test表的值，然后会话2进行添加列操作，该操作被hang住。，，，，第二种情况：，，会话1发起事务，然后会话2进行添加列操作，发现该操作成功。，，，，第三种情况：，，模仿mysqldump的备份原理，设置保存点。，，注意，DDL操作发起的时间是在执行了select * from test之后，如果是在之前，根据上面第二种情况的测试，是可以进行DDL操作的。，，此时，如果不执行ROLLBACK TO SAVEPOINT sp，DDL操作会一直hang下去，执行了该操作后，DDL操作可以继续执行了。，，由此可见，ROLLBACK TO SAVEPOINT确实可以提高DDL的并发性。，，但还有一点需要注意，如果DDL操作是发生在select * from test之前，正如第二种情况所演示的，DDL操作会成功，此时，查看test表的数据会报以下错误：，，root@test 04:32:49 gt; select * from test;，ERROR 1412 (HY000): Table definition has changed, please retry transaction， 对应mysqldump，会报如下错误：，，mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `test` at row: 0

Re: 笑东风

@xiaohuazi，你测试的是那个版本？

1楼MSSQL123

不是有lock-tables 是一次性锁定当前库的所有表的参数么？，http://www.cnblogs.com/kerrycode/p/6963880.html

Re: 笑东风

@MSSQL123，在mysqldump的文档中，对–single-transaction参数有这么Option automatically turns off –lock-tables.的描述。，，lock-tables会导致表被锁住，影响正在执行业务请求，因此我们线上更偏向于–single-transaction

欢迎大家阅读《MySQL-当mysqldump -single-transaction碰到alter table（2）_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)