与mysqldump一致的InnoDB转储

Question

MySQL文档建议在转储带有mysqldump的InnoDB时使用--单事务，以获得数据库的事务一致快照，同时尽量减少对其他事务的阻塞。不幸的是，只有在您能够以某种方式阻止任何其他连接在转储期间执行模式更改的情况下，这才起作用：

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

当一个--single-transaction转储正在进行时，为了确保一个有效的转储文件（正确的表内容和二进制日志坐标），任何其他连接都不应使用以下语句：ALTER table、CREATE table、DROP table、RENAME table、TRUNCATE table。一致的read不是与这些语句隔离的，因此在要转储的表上使用它们会导致mysqldump执行的检索表内容的SELECT获得不正确的内容或失败。

确保在转储过程中不发生架构更改的好方法是什么？我的用例是，我使用mysqldump实用程序每小时定期（cronjob）备份数据库。

我认为也许使用mysqldump默认的--lock-tables可以为我提供事务一致性，但是：

--single-transaction选项和--lock-tables选项是互斥的，因为锁表导致隐式提交任何挂起的事务。

这是说，如果有人对InnoDB运行mysqldump--lock-table，那么它实际上可以导致另一个会话中正在进行的事务不再是事务性的？我希望不会--这似乎很疯狂。其他地方：

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-table-and-transactions

锁定表和解锁表与事务的使用交互如下：

LOCK TABLES不是事务安全的，并且在尝试锁定表之前隐式提交任何活动事务。

这在单个DB会话的上下文中是有意义的。如果您正在事务处理过程中并且执行了此操作，那么它将首先隐式提交您的打开事务。不清楚它对其他会话上的事务有什么作用。我希望它不会执行任何操作，或者更确切地说，它将与其他会话上的任何正在进行的事务一致地排序。

我是不是对mysqldump文档读得太多了，并使用锁表来实现事务一致性？

mysqldump文档仅仅是在解释--单事务与--锁表一起执行是没有意义的，因为--锁表会立即并隐式地关闭单事务吗？但在其他方面mysqldump--lock-tables在事务上是一致的？

谢谢！

Answer 1

锁表强制在同一会话中提交事务。它不会在其他会话中强制提交。

如果您在mysqldump中使用了这两个选项，则mysqldump客户机打开的会话将启动一个事务，然后在发生锁表时立即提交该事务。所以没有必要把它们一起使用。

此外，不会提交任何其他客户端的事务，但它们会阻止mysqldump的锁表操作。任何查询（即使是只读选择）都将获得一个元数据锁，并且锁表将等待该元数据锁被释放。因此mysqldump将尝试获取表锁，并且必须等待。

同时，任何试图在mysqldump开始等待后启动新事务的客户机也会等待，就好像它们在mysqldump后面的队列中一样。这势必会阻止应用程序的进程。

这种类型的等待不像常规的行锁定在50秒后超时。元数据锁定等待的默认超时为1年（请参见https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lock_wait_timeout)。

这几乎排除了在生产站点上使用mysqldump--锁表的可能性，不是吗？只有在不使用事务性表的情况下才应使用它。我已经好几年没有使用--lock-tables选项了。

如果使用--lock-tables，任何DDL语句都将被阻止，因为DDL语句也需要一个独占的元数据锁。

我不知道在使用--single-transaction时有什么方法可以阻止DDL语句。您只需在备份运行时避免这样做。

我还要评论一下，每小时制作一个完整的mysqldump可能不是最好的备份策略。您应该考虑进行不那么频繁的备份，然后使用二进制日志来获得时间点恢复。参见https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html