与mysqldump一致的InnoDB转储

Question

MySQL文档建议，当使用mysqldump转储InnoDB时，使用--单事务获得数据库事务一致的快照，同时尽量减少对其他事务的阻塞。不幸的是，只有在转储过程中可以阻止任何其他连接执行模式更改时，此操作才能奏效：

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

当一个单事务转储正在进行时，为了确保一个有效的转储文件(正确的表内容和二进制日志坐标)，其他任何连接都不应该使用以下语句: ALTER、CREATE、DROP、RENAME TABLE、TRUNCATE。一致的读取不会与这些语句分离，因此在要转储的表上使用它们会导致mysqldump执行的SELECT检索表内容以获得不正确的内容或失败。

如何确保在转储过程中不会发生模式更改？我的用例是，我每小时使用mysqldump实用程序定期备份我的数据库。

我认为使用mysqldump的默认-锁表可能会给我提供事务的一致性，但是：

-单事务选项和--锁表选项是相互排斥的，因为锁表会导致隐式提交任何挂起的事务。

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

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

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

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

这在单个DB会话的上下文中是有意义的。如果您处于事务的中间并执行此操作，则它首先隐式地提交您打开的事务。现在还不清楚它对其他会话上的事务做了什么，如果有的话。我希望它不会做任何事情，或者更确切地说，对于其他会话中的任何正在进行的事务，它将被一致地命令。

我只是过多地阅读mysqldump文档并使用--锁表来实现事务的一致性吗？

mysqldump文档是否简单地解释了这样做--单事务和--锁表是荒谬的，因为--锁表会立即隐式地关闭单个事务？但是，否则mysqldump -锁表在事务上是一致的吗？

谢谢!

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 -锁表的可能性，不是吗？只有当您不使用事务性表时，才应该使用它。我好多年没用过锁表选项了。

如果使用-锁表，任何DDL语句都会被阻塞，因为DDL语句也需要独占元数据锁。

我不知道在使用单事务时阻止DDL语句的任何方法。您只需在备份运行时避免这样做。

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