主页 / dba / 问题 / 345532
Accepted
peppy
Asked: 2025-03-03 07:48:55 +0800 CST

如何在 mysqldump 运行时截断 cron 作业中的表？防止出现错误 1412“表定义已更改”

  • 772

我有一个维护 cron 作业脚本，每 5 分钟运行一次，它会截断一个表，用维护数据重新填充它，然后在最后截断它。我还每天运行一个 mysqldump 脚本来备份我的所有表。我最近注意到备份中缺少一些表。经过进一步调查，我发现 mysqldump 抛出了这个错误：

Error 1412 "Table Definition Has Changed"

进一步研究之后，显然是因为我的 cron 作业脚本在 mysqldump 备份中间使用 TRUNCATE 清空表造成的。

我正在使用 --single-transaction 选项，但没有什么区别。

是否有某种方法可以让 mysqldump 继续运行，即使表在 mysqldump 运行时被截断（并且仍然备份该表）？这需要在没有 --ignore-table 选项的情况下完成。我知道如何做到这一点，但宁愿将所有维护表都包含在备份中，以防数据库必须从备份中完全恢复。

我能想到的唯一其他事情是使用“DELETE FROM table”而不是“TRUNCATE table”，但我必须检查所有网站上的所有脚本才能改变这一点。

mysql

1 个回答

  1. Best Answer

    当使用时mysqldump --single-transaction，有记录表明其他会话不应使用TRUNCATE TABLE或其他 DDL。

    https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html说：

    在转储过程中--single-transaction，为了确保转储文件有效（正确的表内容和二进制日志坐标），其他任何连接都不应使用以下语句：，，，，，。ALTER TABLE一致性读取与这些语句并不隔离，因此在要转储的表上使用它们可能会导致mysqldump 执行的检索表内容的操作获得不正确的内容或失败。CREATE TABLEDROP TABLERENAME TABLETRUNCATE TABLESELECT

    https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html说：

    一致性读取对某些 DDL 语句不起作用：

    • 一致性读取不适用于ALTER TABLE创建原始表的临时副本并在临时副本创建后删除原始表的操作。当您在事务中重新发出一致性读取时，新表中的行不可见，因为在创建事务快照时这些行并不存在。在这种情况下，事务将返回错误：ER_TABLE_DEF_CHANGED“表定义已更改，请重试事务”。

    本文档指定了创建临时副本的 DDL，但对于其他更改或截断表而不创建临时副本的 DDL，也会出现同样的错误。参见https://bugs.mysql.com/bug.php?id=116132

    使用 mysqldump 时有两种方法可以解决这个问题。

    1. 第一个解决方案是运行mysqldump --skip-lock-tables，而不使用--single-transaction选项。这会将每个表转储到其自己的快照中，因此如果另一个会话截断该表，则不会与 创建的较大快照发生冲突--single-transaction。

      此解决方案的缺点是您的备份将不一致。也就是说，您无法保证引用完整性，因为在转储期间表可能会被修改，并且转储中输出的一个表中的行可能会引用转储中不存在的另一个表中的行。

    2. 第二种解决方案是使用全局锁来阻止 DDL，就像TRUNCATE TABLE在转储运行时一样。您可以使用mysqldump --lock-all-tables来执行此操作。如果另一个会话尝试TRUNCATE TABLE，它将等到转储完成后全局锁被释放。

      此解决方案的缺点是所有其他 DDL 和 DML 也将被阻止。在转储运行时，您的数据库基本上处于停止服务状态（至少对于写入而言）。

    其他解决方案涉及使用不同的备份工具，或者需要一些代码更改，而您说您不想这样做。

    • 1

相关问题