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

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

成都創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比大冶網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式大冶網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋大冶地區(qū)。費用合理售后完善，十載實體公司更值得信賴。


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

  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

  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.

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

  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

  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

  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

  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的索引損壞導(dǎo)致的大量報錯，并在處理update語句時導(dǎo)致crash。
檢查binlog發(fā)現(xiàn)的確有很多對該表的update操作。
執(zhí)行一下check table，發(fā)現(xià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)



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

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

  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)

  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

  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)

  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ù)過程是怎樣的就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

分享名稱：MySQL表索引損壞致Crash及修復(fù)過程是怎樣的
本文網(wǎng)址：http://bm7419.com/article22/jdcjcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、定制開發(fā)、網(wǎng)站建設(shè)、網(wǎng)站改版、網(wǎng)站營銷、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都seo排名網(wǎng)站優(yōu)化