MySQL 同时使用DDL时如何使用mysqldump和single-transaction获取正确的dump？

How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?

提问by Valentino Miazzo

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

我是 MySQL 的新手，正在寻找使用mysqldump. 此页面建议使用此命令行：

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:

但是...如果您仔细阅读文档，您会发现：

While a --single-transactiondump 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 SELECTperformed by mysqldumpto retrieve the table contents to obtain incorrect contents or fail.

在--single-transaction转储过程中，为确保有效的转储文件（正确的表内容和二进制日志位置），任何其他连接都不应使用以下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. 一致读不会与这些语句隔离，因此在要转储的表上使用它们可能会导致SELECT执行通过mysqldump检索表内容获取不正确的内容或失败。

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

那么，有没有办法防止这种可能的转储损坏情况？即可以暂时阻止这些语句的命令。

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

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

回答by Bill Karwin

Open a mysqlcommand window and issue this command:

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

mysql> FLUSH TABLES WITH READ LOCK;

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

这将锁定此 MySQL 实例上所有数据库中的所有表，直到您发出（或终止持有这些读锁的客户端连接）。UNLOCK TABLES

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

为了证实这一点，你可以打开另一个命令窗口，并尝试做一个ALTER，DROP，RENAME或TRUNCATE。这些命令挂起，等待读锁被释放。按 Ctrl-C 终止等待。

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

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

The FLUSH TABLES WITH READ LOCKcommand maybe the same as using the --lock-all-tablesoption of mysqldump. It's not totally clear, but this docseems to support it:

该FLUSH TABLES WITH READ LOCK命令可能与使用--lock-all-tables选项相同mysqldump。尚不完全清楚，但此文档似乎支持它：

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

UNLOCK TABLES 的另一个用途是释放通过 FLUSH TABLES WITH READ LOCK 获得的全局读锁。

Both FLUSH TABLES WITH READ LOCKand --lock-all-tablesuse 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 mysqldumpand protect against concurrent ALTER, DROP, RENAME, and TRUNCATE.

两者FLUSH TABLES WITH READ LOCK并--lock-all-tables用短语“全球读锁，”所以我认为这是有可能的是，这些做同样的事情。因此，您应该能够使用该选项来mysqldump防止并发 ALTER、DROP、RENAME 和 TRUNCATE。

---

---

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

关于。您的评论：以下内容来自您链接到的 MySQL 错误日志中的 Guilhem Bichot：

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.

你好。--lock-all-tables 调用 FLUSH TABLES WITH READ LOCK。因此，预计会阻止 ALTER、DROP、RENAME 或 TRUNCATE（除非存在错误或我错了）。但是，--lock-all-tables --single-transaction 不能工作（mysqldump 抛出错误消息）：因为 lock-all-tables 在备份期间锁定服务器的所有表以防止写入，而 single-transaction 是旨在让写入在备份期间发生（通过在事务中使用一致读取 SELECT），它们本质上是不兼容的。

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

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

回答by CrandellWS

I thought the same thing reading that part of the documentation though, I found more information:

我认为阅读文档的那部分也是同样的事情，但我发现了更多信息：

4.5.4. mysqldump — A Database Backup Program http://dev.mysql.com/doc/en/mysqldump.html

4.5.4. mysqldump — 数据库备份程序 http://dev.mysql.com/doc/en/mysqldump.html

For InnoDB tables, mysqldump provides a way of making an online backup:

shell> mysqldump --all-databases --single-transaction > all_databases.sql

This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

对于 InnoDB 表，mysqldump 提供了一种进行在线备份的方法：

shell> mysqldump --all-databases --single-transaction > all_databases.sql

此备份在转储开始时获取所有表的全局读锁（使用 FLUSH TABLES WITH READ LOCK）。一旦获得了这个锁，就会读取二进制日志坐标并释放锁。如果在发出 FLUSH 语句时正在运行长更新语句，则 MySQL 服务器可能会停止，直到这些语句完成。之后，转储变为无锁并且不会干扰对表的读取和写入。如果 MySQL 服务器收到的更新语句很短（就执行时间而言），那么即使有很多更新，初始锁定期也不应该很明显。

There is a conflict with the --optand --single-transactionoptions:

--opt和--single-transaction选项有冲突：

--opt

This option is shorthand. It is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

The --opt option is enabled by default. Use --skip-opt to disable it.

- 选择

此选项是速记。它与指定 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset 相同。它应该为您提供快速转储操作并生成一个可以快速重新加载到 MySQL 服务器的转储文件。

默认情况下启用 --opt 选项。使用 --skip-opt 禁用它。

If I understand your question correctly you want the actual data and the DDL (Data Definition Language) together, because if you only want the DDL you would use --no-data. More information about this can be found at:

如果我正确理解您的问题，您需要将实际数据和 DDL（数据定义语言）放在一起，因为如果您只需要 DDL，您将使用--no-data. 可以在以下位置找到有关此的更多信息：

http://dev.mysql.com/doc/workbench/en/wb-reverse-engineer-create-script.html

http://dev.mysql.com/doc/workbench/en/wb-reverse-engineer-create-script.html

Use the --databases option with mysqldump if you wish to create the database as well as all its objects. If there is no CREATE DATABASE db_name statement in your script file, you must import the database objects into an existing schema or, if there is no schema, a new unnamed schema is created.

如果您希望创建数据库及其所有对象，请在 mysqldump 中使用 --databases 选项。如果脚本文件中没有 CREATE DATABASE db_name 语句，则必须将数据库对象导入现有模式，或者如果没有模式，则创建新的未命名模式。

As suggested by The Definitive Guide to MySQL 5 By Michael KoflerI would suggest the follow options:

正如Michael Kofler的The Definitive Guide to MySQL 5所建议的那样，我建议使用以下选项：

--skip-opt
--single-transaction
--add-drop-table
--create-options
--quick
--extended-insert
--set-charset
--disable-keys

Additionally, not mentioned is --order-by-primaryAlso if you are using the --databasesoption, you should also use --add-drop-databaseespecially if combined with this answerIf you are backing up databases that are connect on different networks you may need to use the --compressoption.

此外，未提及的是--order-by-primary如果您正在使用该--databases选项，您还应该使用--add-drop-database特别是如果与此答案结合使用如果您要备份连接在不同网络上的数据库，您可能需要使用该--compress选项。

So a mysqldump command (without using the --compress, --databases, or --add-drop-databaseoptions) would be :

因此，一个mysqldump的命令（不使用--compress，--databases或--add-drop-database期权）是：

mysqldump --skip-opt --order-by-primary --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset -h db_host -u username --password="myPassword" db_name | mysql --host=other_host db_name

I removed the reference to --disable-keysthat was given in the book as it is not effective with InnoDB as i understand it. The MySql manualstates:

我删除了--disable-keys书中给出的参考，因为它在我理解的 InnoDB 中无效。在MySQL手册状态：

For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS /; and /!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables.

对于每个表，用 /*!40000 ALTER TABLE tbl_name DISABLE KEYS /将 INSERT 语句括起来；和 /!40000 ALTER TABLE tbl_name ENABLE KEYS */; 声明。这使得加载转储文件更快，因为索引是在插入所有行之后创建的。此选项仅对 MyISAM 表的非唯一索引有效。

I also found this bug report http://bugs.mysql.com/bug.php?id=64309which has comments on the bottom from Paul DuBois who also wrote a few booksto which I have no reference on this specific issue other than those comments found within that bug report.

我还发现了这个错误报告http://bugs.mysql.com/bug.php?id=64309，其中对Paul DuBois的底部有评论，他还写了几本书，除此之外，我对这个特定问题没有参考在该错误报告中发现的那些评论。

Now to create the "Ultimate Backup" I would suggest to consider something along the lines of this shell script

现在要创建“终极备份”，我建议考虑与此 shell 脚本类似的内容

1. https://github.com/red-ant/mysql-svn-backup/blob/master/mysql-svn.sh

1. https://github.com/red-ant/mysql-svn-backup/blob/master/mysql-svn.sh

回答by user2375103

You can't get a consistent dump without locking tables. I just do mine during a time of day that the 2 minutes it takes to do the dump isn't noticed.

不锁定表就无法获得一致的转储。我只是在一天中的某个时间做我的，没有注意到进行转储所需的 2 分钟。

One solution is to do replication, then back up the slave instead of the master. If the slave misses writes during the backup, it will just catch up later. This will also leave you with a live backup server in case the master fails. Which is nice.

一种解决方案是进行复制，然后备份从站而不是主站。如果从属在备份期间未写入，它会在稍后赶上。这也将为您提供一个实时备份服务器，以防主服务器出现故障。这很好。