1220 database data_error ai_generated true

ERROR 1220 (HY000): 执行命令 'PURGE BINARY LOGS' 时出错：在文件索引中找不到目标日志文件

ERROR 1220 (HY000): Error when executing command 'PURGE BINARY LOGS': Could not find target log file mentioned in the file index

ID: database/mysql-binlog-purge-failure

其他格式: JSON · Markdown 中文 · English
85%修复率
85%置信度
1证据数
2023-08-20首次发现

版本兼容性

版本状态引入弃用备注
MySQL 5.7 active — — —
MySQL 8.0 active — — —
MySQL 8.4 active — — —
MariaDB 10.5 active — — —
MariaDB 10.6 active — — —

根因分析

PURGE BINARY LOGS 命令中指定的二进制日志文件在二进制日志索引文件中不存在，通常是由于该文件被手动删除或索引文件损坏。

English

The binary log file specified in the PURGE BINARY LOGS command does not exist in the binary log index file, often because it was manually deleted or the index file is corrupted.

generic

官方文档

https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html

解决方案

  1. 通过列出所有现有 binlog 文件来重建二进制日志索引文件：`ls -1 /var/lib/mysql/mysql-bin.* > /var/lib/mysql/mysql-bin.index` 并确保文件权限正确。然后重试 PURGE BINARY LOGS。
  2. 谨慎使用 RESET MASTER（它会删除所有 binlog）来清理索引并重新开始。仅在不需要时间点恢复时执行：`RESET MASTER;`
  3. 如果特定 binlog 文件缺失，识别间隙并使用 FLUSH LOGS 创建新日志，然后清除到该新日志：`FLUSH LOGS; PURGE BINARY LOGS TO 'mysql-bin.000new';`

无效尝试

常见但无效的做法:

  1. Run PURGE BINARY LOGS TO 'mysql-bin.000999' again with a different filename 90% 失败

    If the index file is corrupted, any filename will fail with the same error until the index is rebuilt

  2. Delete the binary log index file manually and restart MySQL 95% 失败

    Deleting the index file without rebuilding it will cause MySQL to fail to start or lose track of all binlogs

  3. Set expire_logs_days to 0 to disable purging 70% 失败

    This only stops automatic purging; the manual PURGE command will still fail due to the index issue