错误日志出现:
[ERROR] mysqld: Table './db_name/tbl_name' is marked as crashed and should be repaired
常见原因:写入过程中进程崩溃、磁盘满、杀进程未清理、硬件故障。
CHECK TABLE tbl_name [, tbl_name] ... [option]
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
| 选项 | 适用引擎 | 行为 |
|---|---|---|
QUICK | MyISAM/InnoDB | 仅检查索引链接,不扫描数据行,最快 |
FAST | MyISAM | 仅检查未正常关闭的表 |
MEDIUM | MyISAM | 扫描行校验校验和,默认级别 |
EXTENDED | MyISAM | 全行全键校验,最彻底但最慢 |
CHANGED | MyISAM | 仅检查上次检查后修改过的表 |
mysql> CHECK TABLE tbl_name QUICK;
+------------------+-------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------------------------------------------------------+
| db.tbl_name | check | warning | Table is marked as crashed and last repair failed |
| db.tbl_name | check | warning | 1 client is using or hasn't closed the table properly |
| db.tbl_name | check | error | Size of datafile is: 268414976 Should be: 268871480 |
| db.tbl_name | check | error | Corrupt |
+------------------+-------+----------+----------------------------------------------------------+
Msg_type = error + Corrupt → 确认损坏,需修复。Size of datafile 不匹配 → 数据文件被截断或残留。REPAIR TABLE tbl_name [, tbl_name] ...
仅支持 MyISAM,InnoDB 请用 innodb_force_recovery 导出重建。
mysql> REPAIR TABLE tbl_name;
+---------------+--------+----------+--------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------+----------+--------------------------------------------------------+
| db.tbl_name | repair | info | Found block that points outside data file at 268414940 |
| db.tbl_name | repair | warning | Number of rows changed from 4377692 to 4370359 |
| db.tbl_name | repair | status | OK |
+---------------+--------+----------+--------------------------------------------------------+
info:发现坏块并修正。warning:行数变化(丢失损坏行),数据可能已部分丢失。status: OK:修复完成。mysql> CHECK TABLE tbl_name QUICK;
+---------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| db.tbl_name | check | status | OK |
+---------------+-------+----------+----------+
innodb_force_recovery = 1..6(从 1 递增尝试)。mysqldump 导出数据。innodb_force_recovery = 0 重启。CHECK TABLE(低峰期执行,加锁影响业务)。FLUSH TABLES WITH READ LOCK 做逻辑备份前锁表。