1、故障现象
mysql> select count(*) from dede_archives;
ERROR 144 (HY000): Table '.xx' is marked as crashed and last (automatic?) repair failed
2、故障原因
Mysqld进程在数据写入时被意外关闭
意外关闭计算机
硬件故障
3、mysql官方解决方案
官方文档:https://dev.mysql.com/doc/refman/5.6/en/myisam-table-maintenance.html
3.1 check table 检查表的完整性
mysql> check table xx FAST QUICK; /*快速检查表*/
或者
mysql> check table xx EXTENDED; /*全表检查,耗时长*/
正常输出:
+-----------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| xx | check | status | OK |
+-----------------------+-------+----------+----------+
1 row in set (0.16 sec)
3.2 REPAIR TABLE 修复表 适用于MyISAM
mysql> REPAIR TABLE xx;
正常输出:
+-------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+--------+----------+----------+
| xxxx | repair | status | OK |
+-------------------------+--------+----------+----------+
1 row in set (0.24 sec)
3.3 myisamchk 快速修复表(恢复模式,并重建索引文件)
C:\>myisamchk -r xx
输出:
- recovering (with sort) MyISAM-table 'xx'
Data records: 11543
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
备注:3.2和3.3任选一个进行修复
在进行检查和修复表之前,需要对表和数据库进行备份
MyISAM
表维护也可以使用SQL语句完成,该语句执行与myisamchk可以执行的操作类似的 操作:
-
要检查
MyISAM
表,请使用CHECK TABLE
。 -
要修复
MyISAM
表,请使用REPAIR TABLE
。 -
要优化
MyISAM
表,请使用OPTIMIZE TABLE
。 -
要分析
MyISAM
表,请使用ANALYZE TABLE
。
3.4 迁移数据库(如果由于底层存储损坏导致的表故障)
使用mysqldump进行备份恢复
扩展
mysql错误代码查询
shell> perror 126 127 132 134 135 136 141 144 145 MySQL error code 126 = Index file is crashed MySQL error code 127 = Record-file is crashed MySQL error code 132 = Old database file MySQL error code 134 = Record was already deleted (or record file crashed) MySQL error code 135 = No more room in record file MySQL error code 136 = No more room in index file MySQL error code 141 = Duplicate unique key or constraint on write or update MySQL error code 144 = Table is crashed and last repair failed MySQL error code 145 = Table was marked as crashed and should be repaired