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

​修复表:​

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)

​表碎片整理:​

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

​表索引计数重置:​

ANALYZE TABLE  `db_work_flow`.`t_work_flow_variable`;

​检查表 表健康：​

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)

​重启sql_thread 线程进行同步relaylog文件到 测试库正常复制​

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

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