当同时使用DDL时，如何使用mysqldump和single-transaction获得正确的转储? [英] How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?

问题描述

我是MySQL的新手，正在寻找使用mysqldump执行在线热逻辑备份的最佳方法. 此页面建议使用以下命令行:

I'm new to MySQL and I'm figuring out the best way to perform an on-line hot logical backup using mysqldump. This page suggests this command line:

mysqldump --single-transaction --flush-logs --master-data=2
          --all-databases > backup_sunday_1_PM.sql

但是...如果您仔细阅读了文档您会发现:

but... if you read the documentation carefully you find that:

正在执行--single-transaction转储时，以确保有效的转储文件 (正确的表内容和二进制日志位置)，不应使用其他连接 以下语句:ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.一种 一致的读取不是与这些语句隔离的，因此可以在表上使用它们来 被转储会导致mysqldump执行的SELECT检索表内容 以获得不正确的内容或失败.

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. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

那么，有什么方法可以防止这种可能的转储损坏情况? IE.可能会暂时阻止这些语句的命令.

So, is there any way to prevent this possible dump corruption scenario? I.e. a commands that could block those statements temporarily.

PS:关于此主题的MySQL错误条目 http://bugs.mysql.com/bug.php?id = 27850

PS: MySQL bug entry on this subject http://bugs.mysql.com/bug.php?id=27850

推荐答案

打开mysql命令窗口并发出以下命令:

Open a mysql command window and issue this command:

mysql> FLUSH TABLES WITH READ LOCK;

这将锁定此MySQL实例上 all 数据库中的 all 表，直到您发出UNLOCK TABLES(或终止拥有这些读取锁的客户端连接)为止.

This will lock all tables in all databases on this MySQL instance until you issue UNLOCK TABLES (or terminate the client connection that holds these read locks).

要确认这一点，您可以打开另一个命令窗口，然后尝试执行ALTER，DROP，RENAME或TRUNCATE.这些命令挂起，等待读取锁定被释放.按Ctrl-C终止等待.

To confirm this, you can open another command window and try to do an ALTER, DROP, RENAME or TRUNCATE. These commands hang, waiting for the read lock to be released. Hit Ctrl-C to terminate the waiting.

但是，尽管表具有读取锁，但是您仍然可以执行mysqldump备份.

But while the tables have a read lock, you can still perform a mysqldump backup.

FLUSH TABLES WITH READ LOCK命令 可能与使用mysqldump的--lock-all-tables选项相同.尚不清楚，但是此文档似乎支持它:

The FLUSH TABLES WITH READ LOCK command may be the same as using the --lock-all-tables option of mysqldump. It's not totally clear, but this doc seems to support it:

UNLOCK TABLES的另一个用途是 释放获得的全局读取锁 带有带读锁的冲洗表.

Another use for UNLOCK TABLES is to release the global read lock acquired with FLUSH TABLES WITH READ LOCK.

FLUSH TABLES WITH READ LOCK和--lock-all-tables都使用短语全局读取锁定"，因此我认为它们可能执行相同的操作.因此，您应该能够使用该选项来mysqldump并防止并发ALTER，DROP，RENAME和TRUNCATE.

Both FLUSH TABLES WITH READ LOCK and --lock-all-tables use the phrase "global read lock," so I think it's likely that these do the same thing. Therefore, you should be able to use that option to mysqldump and protect against concurrent ALTER, DROP, RENAME, and TRUNCATE.

重新.您的评论:以下是您链接到的MySQL错误日志中的Guilhem Bichot:

Re. your comment: The following is from Guilhem Bichot in the MySQL bug log that you linked to:

嗨. --lock-all-tables调用FLUSH 带有读锁的表.因此，这是 预计会阻止ALTER，DROP，RENAME， 或TRUNCATE(除非存在错误或 我错了).但是，--lock-all-tables --single-transaction无法正常工作(mysqldump抛出错误消息): 因为lock-all-tables锁定所有 服务器的写表 在备份期间， 而单笔交易是有意的 在备份过程中进行写操作 (通过在中使用一致读取的SELECT 交易)，它们是不兼容的 本质上.

Hi. --lock-all-tables calls FLUSH TABLES WITH READ LOCK. Thus it is expected to block ALTER, DROP, RENAME, or TRUNCATE (unless there is a bug or I'm wrong). However, --lock-all-tables --single-transaction cannot work (mysqldump throws an error message): because lock-all-tables locks all tables of the server against writes for the duration of the backup, whereas single-transaction is intended to let writes happen during the backup (by using a consistent-read SELECT in a transaction), they are incompatible in nature.

由此看来，您在备份期间无法获得并发访问，同时阻止了ALTER，DROP，RENAME和TRUNCATE.

From this, it sounds like you cannot get concurrent access during a backup, and simultaneously block ALTER, DROP, RENAME and TRUNCATE.

这篇关于当同时使用DDL时，如何使用mysqldump和single-transaction获得正确的转储?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！