使用mysqldump备份MariaDB恢复时出现表错误求助

问题概述 #

使用mysqldump备份MariaDB多年，近期恢复时先后出现两个异常：

• ERROR 1032 (HY000) at line 9413: Can't find record in 'xu33gc_dictionary_letters'
• ERROR 1005 (HY000) at line 21042: Can't create table mydb.xu33gc_tj_notification_user_exclusions (errno: 150 "Foreign key constraint is incorrectly formed")
  备份日志无报错，此前恢复均正常。

针对ERROR 1032的解决步骤 #

1. 检查表的导出/插入顺序 #

1032错误本质是依赖表的数据插入早于被依赖表。mysqldump默认按字母顺序导出表，若数据库存在外键关联，可能出现依赖表先被执行插入的情况。

• 打开备份文件，定位xu33gc_dictionary_letters的INSERT语句位置，再查找依赖该表的表（含指向它的外键）的INSERT语句，确认后者是否在前者之后。
• 解决方式：
  • 重新备份时添加--order-by-primary参数，强制按主键顺序导出数据，保证被依赖表先完成数据插入；
  • 手动调整备份文件中表的顺序，将被依赖表的CREATE和INSERT语句移到依赖表之前。

2. 验证备份文件完整性 #

备份文件可能存在截断或损坏，导致xu33gc_dictionary_letters的INSERT语句不完整：

• 用wc -l backup-mydb-2022-10-29-2240418.sql查看总行数，对比历史正常备份的行数；
• 重新备份时添加--checksum参数，生成备份校验和，恢复前验证完整性；
• 在备份源数据库执行mysqlcheck -c mydb xu33gc_dictionary_letters，确认原表数据无损坏。

针对ERROR 1005的解决步骤 #

1. 排查外键约束定义 #

ERROR 1005（errno:150）是外键约束配置错误，常见原因：

• 外键字段与被关联字段的数据类型、长度、是否允许空、字符集不完全匹配（比如INT(11)和INT(10)、CHAR(20)和VARCHAR(20)都不兼容）；
• 被关联的表未提前创建（mysqldump导出顺序导致依赖表先创建）；
• 表存储引擎为MyISAM（不支持外键）。
• 解决方式：
  • 打开备份文件中xu33gc_tj_notification_user_exclusions的CREATE TABLE语句，对比外键关联的表和字段，修正不匹配的定义；
  • 调整备份文件中表的创建顺序，确保被关联表先创建；
  • 将表引擎改为InnoDB（若当前为MyISAM）。

2. 临时禁用外键约束恢复 #

若只是恢复顺序导致的外键校验错误，可临时禁用外键检查：

1. 创建临时恢复脚本temp_recover.sql，内容如下：

   SET FOREIGN_KEY_CHECKS=0;
   SOURCE backup-mydb-2022-10-29-2240418.sql;
   SET FOREIGN_KEY_CHECKS=1;
   

2. 执行恢复命令：

   mysql -u root -ppass mydb < temp_recover.sql
   

优化备份参数避免后续问题 #

调整mysqldump命令，添加以下参数减少恢复异常：

• --single-transaction：针对InnoDB引擎，无需锁表即可获得一致性备份（替代--lock-tables，避免锁表导致的导出顺序问题）；
• --order-by-primary：按主键顺序导出数据，保证依赖关系的正确性；
• --disable-keys：全局禁用索引，加快恢复速度，恢复完成后自动启用；
• --hex-blob：避免二进制字段导出时出现乱码或损坏。

优化后的备份命令：

/usr/bin/mysqldump --complete-insert --compress --add-drop-table --single-transaction --order-by-primary --disable-keys --verbose -u root -ppass --log-error=backup-mydb-$(date +%Y-%m-%d-%H%M%S)-error.log --result-file=backup-mydb-$(date +%Y-%m-%d-%H%M%S).sql mydb

内容的提问来源于stack exchange，提问作者Alex Regan