关于mysqldump的实例详解-mysql教程-PHP中文网

关于mysqldump的实例详解

零下一度

发布： 2017-07-19 16:06:23

原创

1456 人浏览过

部分生产环境采用mysqldump --single-transaction的方式在夜间进行数据库备份，而同事恰好在备份期间执行了alter table操作，操作部分成功部分失败，为啥呢？

测试在MySQL 5.6.36上执行，该问题存在版本差异！

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

在mysqldump对single-transaction参数的解释为：

Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines.
 While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them.
 Option automatically turns off --lock-tables.

登录后复制

红色字体部分是重点，但是看得有些迷糊，还是动手测试下。

根据《mysqldump的几个主要选项探究》的介绍，我们备份执行的命令mysqldump --single-transaction --master-data相当于执行下面代码：

FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SHOW MASTER STATUS;
UNLOCK TABLES;

SHOW TABLES LIKE &#39;xxx&#39;SET OPTION SQL_QUOTE_SHOW_CREATE=1SHWO CREATE TABLE &#39;xxx&#39;SHOW FIELDS FROM &#39;xxx&#39;SHOW TABLE STATUS LIKE &#39;xxx&#39;SELECT /*!40001 SQL_NO_CACHE */ * FROM  xxx

QUIT

登录后复制

场景1：mysqldump开始但尚未备份到表tb001时，另外回话对表tb001进行alter操作，然后mysqldump对表tb001进行导出

alter操作顺利完成，但是mysqldump操作失败。

场景2：mysqldump开始备份并完成tb001的导出，在对其他表进行导出过程中，其他回话对表进行alter操作

alter table操作被阻塞直至mysqldump完成或失败后退出。

使用mysqldump备份时，模拟场景2的环境，报错信息为：

mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `tb1002` at row: 0

查看导出文件，最后内容为：

--
-- Dumping data for table `tb1002`--LOCK TABLES `tb1002` WRITE;/*!40000 ALTER TABLE `tb1002` DISABLE KEYS */;

登录后复制

总结：

single-transaction参数通过Innodb的多版本来获得数据一致性，而ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE等操作会破坏数据一致性，两种操作不能并发执行。

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

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

以上是关于mysqldump的实例详解的详细内容。更多信息请关注PHP中文网其他相关文章！

php8，我来也

30分钟学会网站布局

尚观Oracle入门到精通视频教程

你的第一行 UNI-APP 代码

Flutter 从头到应用启动

兄弟连Linux新版视频教程

AXURE 9视频教程（适合产品经理交互产品设计UI）

零基础PS视频教程

16天带你入门UI视频教程

PS技巧和切片技巧视频教程

阿里云环境搭建以及项目上线视频教程

计算机网络概述——程序员必须掌握的基础知识

程序员必备教程——HTTP协议讲解

Websocket视频教程

关于mysqldump的实例详解