表索引损坏导致MySQL服务挂掉
故障场景:
在利用mysql binlog文件 模拟从库 进行还原数据时
每次开启 START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'relay-bin.010272', RELAY_LOG_POS =548202203;
MySQL服务就行挂了,错误日志提示如下:
2022-03-08T10:33:54.862985+08:00 4 [ERROR] InnoDB: Index ft_index_word of table db_work_flow.t_work_flow_variable is corrupted2022-03-07T20:24:37.890808+08:00 3 [Note] InnoDB: Load corrupted index ft_index_word of table db_work_flow.t_work_flow_variable2022-03-07 20:24:37 0x7f3a8336d700 InnoDB: Assertion failure in thread 139889286240000 in file pars0pars.cc line 822InnoDB: Failing assertion: sym_node->table != NULL
报错 ft_index_word索引损坏
检查表发现此表的索引确实被损坏了,需要修复下:
root@tidb05 10:34: [(none)]> CHECK TABLE `db_work_flow`.`t_work_flow_variable`;
+-----------------------------------+-------+----------+----------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+-------+----------+----------------------------------------------------+
| db_work_flow.t_work_flow_variable | check | Warning | InnoDB: Index ft_index_word is marked as corrupted |
| db_work_flow.t_work_flow_variable | check | error | Corrupt |
+-----------------------------------+-------+----------+----------------------------------------------------+
2 rows in set (1 min 20.63 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
修复表:
root@tidb05 10:37: [(none)]> OPTIMIZE TABLE `db_work_flow`.`t_work_flow_variable`;
+-----------------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+----------+----------+-------------------------------------------------------------------+
| db_work_flow.t_work_flow_variable | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| db_work_flow.t_work_flow_variable | optimize | status | OK |
+-----------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (15 min 26.57 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
表碎片整理:
表索引计数重置:
检查表 表健康:
root@tidb05 11:29: [(none)]> CHECK TABLE `db_work_flow`.`t_work_flow_variable`;
+-----------------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+-------+----------+----------+
| db_work_flow.t_work_flow_variable | check | status | OK |
+-----------------------------------+-------+----------+----------+
1 row in set (2 min 17.17 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
重启sql_thread 线程进行同步relaylog文件到 测试库正常复制
提示:以上环境是线上生产环境建议要在业务低峰期操作,OPTIMIZE TABLE;ANALYZE TABLE 要锁表
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤