MySQL表索引損壞致Crash及修復過程是怎樣的

這篇文章給大家介紹MySQL表索引損壞致Crash及修復過程是怎樣的，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

監控到一臺MySQL實例在早上發生過Crash，上去看了一下，已經被mysqld_safe成功拉起。
上去檢查一下錯誤日志，發現錯誤日志如下（已對表名，庫名，路徑做脫敏處理）：

1. ……………………………………（大量相同的報錯）…………………………………………
   

2. 2017-08-31T11:11:04.291424Z 32394522 [ERROR] InnoDB: Record in index `t_idx` of table `$db_name`.`$tb_name` was not found on update: TUPLE (info_bits=0, 9 fields): {[12]121098369601(0x010201000908030609060001),[9] (0x000000000000010000),[4]KOWA(0x0B0F0701),[4]AYNA(0x01090E01),[6]STAT44(0x030401040404),[4]AYNA(0x01090E01),[1]0(0x00),[1]0(0x00),[32]8f2a39b44fe74cd781527d856342d834(0x0806020103090204040605070403040708010502070408050603040204080304)} at: COMPACT RECORD(info_bits=0, 9 fields): {[12]121098369601(0x010201000908030609060001),[9] (0x000000000000010000),[4]KOWA(0x0B0F0701),[4]AYNA(0x01090E01),NULL,NULL,[1]0(0x00),[1]0(0x00),[32]8f2a39b44fe74cd781527d856342d834(0x0806020103090204040605070403040708010502070408050603040204080304)}
   

3. 2017-08-31T03:11:04.291454Z 32394522 [Note] InnoDB: GIS MBR INFO: 1.31506e-47 and 1.02964e-71, 2.8816e-306, 1.93059e+53
   

4. 
   

5. 2017-08-31 03:11:04 0x7fcaf04be700  InnoDB: Assertion failure in thread 140509591627520 in file row0ins.cc line 282
   

6. InnoDB: Failing assertion: !cursor->index->is_committed()
   

7. InnoDB: We intentionally generate a memory trap.
   

8. InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
   

9. InnoDB: If you get repeated assertion failures or crashes, even
   

10. InnoDB: immediately after the mysqld startup, there may be
    

11. InnoDB: corruption in the InnoDB tablespace. Please refer to
    

12. InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
    

13. InnoDB: about forcing recovery.
    

14. 03:11:04 UTC - mysqld got signal 6 ;
    

15. This could be because you hit a bug. It is also possible that this binary
    

16. or one of the libraries it was linked against is corrupt, improperly built,
    

17. or misconfigured. This error can also be caused by malfunctioning hardware.
    

18. Attempting to collect some information that could help diagnose the problem.
    

19. As this is a crash and something is definitely wrong, the information
    

20. collection process might fail.
    

21. 
    

22. …………………………………………………………………………………………………………
    

23. 
    

24. Trying to get some variables.
    

25. Some pointers may be invalid and cause the dump to abort.
    

26. Query (7fca7c0dbaa0): is an invalid pointer
    

27. Connection ID (thread ID): 32394522
    

28. Status: NOT_KILLED
    

29. 
    

30. The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
    

31. information that should help you find out what is causing the crash.
    

32. …………………………………………………………………………………………………………
    

33. （重啟中）
    

34. …………………………………………………………………………………………………………
    

35. 2017-08-31T03:11:08.925622Z 0 [Note] $basedir/bin/mysqld: ready for connections.
    

36. Version: '5.7.12-log' socket: '$datadir/mysqld.sock' port: 3306  Source distribution
    

37. 
    

38. 2017-08-31T03:31:10.232145Z 1704 [ERROR] InnoDB: Record in index `t_idx` of table `$db_name`.`$tb_name` was not found on update: TUPLE (info_bits=0, 9 fields): {[12]198051077411(0x010908000501000707040101),[9] 7 (0x000000000000020700),[4]AOGA(0x010F0701),[4]AQGA(0x01010701),[6]STAT44(0x030401040404),NULL,[1]0(0x00),[1]0(0x00),[32]c6f98a358ace4897a11a27d689bb6884(0x0306060908010305080103050408090701010101020704060809020206080804)} at: COMPACT RECORD(info_bits=0, 9 fields): {[12]198051077411(0x010908000501000707040101),[9] 7 (0x000000000000020700),[4]AOGA(0x010F0701),[4]AQGA(0x01010701),NULL,NULL,[1]0(0x00),[1]0(0x00),[32]c6f98a358ace4897a11a27d689bb6884(0x0306060908010305080103050408090701010101020704060809020206080804)}
    

39. 2017-08-31T03:31:10.232168Z 1704 [Note] InnoDB: GIS MBR INFO: 7.26084e-43 and 1.08604e-42, 2.8823e-306, 132832
    

40. 
    

41. 2017-08-31T03:35:51.201716Z 2208 [ERROR] InnoDB: Flagged corruption of `t_idx` in table `$db_name`.`$tb_name` in CHECK TABLE; Wrong count

初步確定為因為名為t_idx的索引損壞導致的大量報錯，并在處理update語句時導致crash。
檢查binlog發現的確有很多對該表的update操作。
執行一下check table，發現的確有問題：

1. mysql> CHECK TABLE `$db_name`.`$tb_name`;
   

2. +--------------------+-------+----------+-------------------------------------------------------+
   

3. | Table              | Op    | Msg_type | Msg_text                                               |
   

4. +--------------------+-------+----------+-------------------------------------------------------+
   

5. | $db_name.$tb_name  | check | Warning  | InnoDB: Index t_idx is marked as corrupted            |
   

6. | $db_name.$tb_name  | check | error    | Corrupt                                               |
   

7. +--------------------+-------+----------+-------------------------------------------------------+
   

8. 2 rows in set (0.83 sec)

因該庫為高可用主庫，檢查到備庫狀態正常，準備先手動做failover，再對該表進行修復。

因為表小，也比較幸運，修復過程十分順利：

1. mysql> OPTIMIZE TABLE `$db_name`.`$tb_name`;
   

2. +--------------------+----------+----------+-------------------------------------------------------------------+
   

3. | Table              | Op       | Msg_type | Msg_text                                                          |
   

4. +--------------------+----------+----------+-------------------------------------------------------------------+
   

5. | $db_name.$tb_name  | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
   

6. | $db_name.$tb_name  | optimize | status   | OK                                                                |
   

7. +--------------------+----------+----------+-------------------------------------------------------------------+
   

8. 2 rows in set (3.42 sec)
   

9. 
   

10. mysql> ALTER TABLE `$db_name`.`$tb_name` ENGINE=INNODB;
    

11. Query OK, 0 rows affected (3.09 sec)
    

12. Records: 0  Duplicates: 0  Warnings: 0
    

13. 
    

14. mysql> ANALYZE TABLE `$db_name`.`$tb_name`;
    

15. +--------------------+---------+----------+----------+
    

16. | Table              | Op      | Msg_type | Msg_text |
    

17. +--------------------+---------+----------+----------+
    

18. | $db_name.$tb_name  | analyze | status   | OK       |
    

19. +--------------------+---------+----------+----------+
    

20. 1 row in set (0.00 sec)
    

21. 
    

22. mysql> CHECK TABLE `$db_name`.`$tb_name`;
    

23. +--------------------+-------+----------+----------+
    

24. | Table              | Op    | Msg_type | Msg_text |
    

25. +--------------------+-------+----------+----------+
    

26. | $db_name.$tb_name  | check | status   | OK       |
    

27. +--------------------+-------+----------+----------+
    

28. 1 row in set (0.98 sec)

關于MySQL表索引損壞致Crash及修復過程是怎樣的就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。