表索引损坏导致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.

表碎片整理:

ALTER TABLE `db_work_flow`.`t_work_flow_variable` ENGINE=INNODB;
root@tidb05 11:13: [(none)]> ALTER TABLE `db_work_flow`.`t_work_flow_variable` ENGINE=INNODB;
Query OK, 1828246 rows affected (15 min 21.92 sec)
Records: 1828246 Duplicates: 0 Warnings: 0
  • 1.
  • 2.
  • 3.
  • 4.

表索引计数重置:

ANALYZE TABLE `db_work_flow`.`t_work_flow_variable`;
  • 1.

检查表 表健康:

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文件到 测试库正常复制

START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'relay-bin.010272', RELAY_LOG_POS =548202203;
  • 1.

提示:以上环境是线上生产环境建议要在业务低峰期操作,OPTIMIZE TABLE;ANALYZE TABLE  要锁表

posted @   勤奋的蓝猫  阅读(2)  评论(0编辑  收藏  举报  
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示