MySQL表索引損壞致Crash及修復(fù)過(guò)程是怎樣的

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

監(jiān)控到一臺(tái)MySQL實(shí)例在早上發(fā)生過(guò)Crash，上去看了一下，已經(jīng)被mysqld_safe成功拉起。
上去檢查一下錯(cuò)誤日志，發(fā)現(xiàn)錯(cuò)誤日志如下（已對(duì)表名，庫(kù)名，路徑做脫敏處理）：

1. ……………………………………（大量相同的報(bào)錯(cuò)）…………………………………………
   

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

初步確定為因?yàn)槊麨閠_idx的索引損壞導(dǎo)致的大量報(bào)錯(cuò)，并在處理update語(yǔ)句時(shí)導(dǎo)致crash。
檢查binlog發(fā)現(xiàn)的確有很多對(duì)該表的update操作。
執(zhí)行一下check table，發(fā)現(xiàn)的確有問(wèn)題：

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)

因該庫(kù)為高可用主庫(kù)，檢查到備庫(kù)狀態(tài)正常，準(zhǔn)備先手動(dòng)做failover，再對(duì)該表進(jìn)行修復(fù)。

因?yàn)楸硇。脖容^幸運(yùn)，修復(fù)過(guò)程十分順利：

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)

關(guān)于MySQL表索引損壞致Crash及修復(fù)過(guò)程是怎樣的就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。