【MySQL】錯誤資訊寫入slave_relay_log.index 案例一則

楊奇龍發表於2014-06-04
MySqlIndex
第一次遇到MySQL 將錯誤資訊寫入 slave-relay-log.index 中，slave io thread 啟動成功，而sql thread 失敗的案例，記錄下來。
【現象】
生產環境突然報警，slave sql程式停止，登陸伺服器檢查，master-error.log 包含如下資訊：
  1. #tail -f /home/mysql/data3008/mysql/master-error.log
  2. 140507 20:59:29 [ERROR] log 10:44:23 UTC - mysqld got signal 11 ; listed in the index, but failed to stat
  3. 140507 20:59:29 [ERROR] Error counting relay log space
  4. 140507 21:04:29 [ERROR] log 10:44:23 UTC - mysqld got signal 11 ; listed in the index, but failed to stat
  5. 140507 21:04:29 [ERROR] Error counting relay log space
  6. 140507 21:09:29 [ERROR] log 10:44:23 UTC - mysqld got signal 11 ; listed in the index, but failed to stat
  7. 140507 21:09:29 [ERROR] Error counting relay log space
  8. 140507 21:14:29 [ERROR] log 10:44:23 UTC - mysqld got signal 11 ; listed in the index, but failed to stat
  9. 140507 21:14:29 [ERROR] Error counting relay log space
  10. 140507 21:15:29 [ERROR] log 10:44:23 UTC - mysqld got signal 11 ; listed in the index, but failed to stat
  11. 140507 21:15:29 [ERROR] Error counting relay log space
關於 mysqld got signal 11 的錯誤案例 請見 《mysqld got signal 11 案例一則 》 ，這裡遇到Error counting relay log space 報錯，於是檢查 slave-relay-log.index 檔案
  1. #more slave-relay-log.index
  2. /home/mysql/data3008/mysql/slave-relay.023481
  3. /home/mysql/data3008/mysql/slave-relay.023482
  4. 10:44:23 UTC - mysqld got signal 11 ;
  5. This could be because you hit a bug. It is also possible that this binary
  6. or one of the libraries it was linked against is corrupt, improperly built,
  7. or misconfigured. This error can also beay fail.
  8. key_buffer_size=16777216
  9. read_buffer_size=262144
  10. max_used_connections=10
  11. max_threads=230
  12. thread_count=8
  13. connection_count=7
  14. It is possible that mysqld could use up to
  15. key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 136546 K bytes of memory
  16. Hope that's ok; if not, decrease some variables in the equation.
  17. Thread pointer: 0x5580000
  18. Attempting backtrace. You can use the following information to find out
  19. where mysqld died. If you see no messages after this, something went
  20. terribly wrong...
  21. stack_bottom = 2b74017d3e58 thread_stack 0x40000
  22. /u01/mysql/bin/mysqld(my_print_stacktrace+0x29) [0x903c24]
  23. /u01/mysql/bin/mysqld(handle_fatal_signal+0x3f6) [0x703916]
  24. /lib64/libpthread.so.0() [0x313f80f4a0]
  25. /u01/mysql/bin/mysqld(Query_cache::free_memory_block(Query_cache_block*)+0x58) [0x73c5e0]
  26. /u01/mysql/bin/mysqld(Query_cache::free_query_internal(Query_cache_block*)+0x164) [0x73ca8a]
  27. /u01/mysql/bin/mysqld() [0x73db00]
  28. /u01/mysql/bin/mysqld(query_cache_insert(st_net*, char const*, unsigned long)+0x1e7) [0x740b2b]
  29. /u01/mysql/bin/mysqld(net_real_write+0x39) [0x5e2531]
  30. /u01/mysql/bin/mysqld() [0x5e29a3]
  31. /u01/mysql/bin/mysqld(my_net_write+0xda) [0x5e2f91]
  32. /u01/mysql/bin/mysqld(Protocol::write()+0x1e) [0x5e4192]
  33. /u01/mysql/bin/mysqld(select_send::send_data(List&)+0x17c) [0x5dd204]
  34. /u01/mysql/bin/mysqld() [0x652f13]
  35. /u01/mysql/bin/mysqld() [0x654a73]
  36. /u01/mysql/bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x81) [0x65a2c3]
  37. /u01/mysql/bin/mysqld() [0x65f5ed]
  38. /u01/mysql/bin/mysqld(JOIN::exec()+0x466) [0x675e58]
  39. /u01/mysql/bin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long lon
  40. g, select_result*, st_select_lex_unit*, st_select_lex*)+0x700) [0x671ff7]
  41. /u01/mysql/bin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x18b) [0x677f22]
  42. /u01/mysql/bin/mysqld() [0x601685]
  43. /u01/mysql/bin/mysqld(mysql_execute_command(THD*)+0x18ee) [0x6066a4]
  44. /u01/mysql/bin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x419) [0x60a81c]
  45. /u01/mysql/bin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xe94) [0x60b6c5]
  46. /u01/mysql/bin/mysqld(do_command(THD*)+0x107) [0x60c16c]
  47. /u01/mysql/bin/mysqld(handle_one_connection+0x237) [0x5fe1de]
  48. /lib64/libpthread.so.0() [0x313f8077f1]
  49. /lib64/libc.so.6(clone+0x6d) [0x313f4e570d]
由於MySQL slave 在啟動時需要檢查relay log index 檔案中的relay log資訊，並進行applay到本地，由於該檔案包含無誤資訊導致MySQL無法識別讀取不到relay log ，sql thread 啟動報錯。
【解決】
檢查發現錯誤資訊寫入relay-log.index檔案中，清理slave-relay-log.index 中的異常資訊,MySQL 會自動將slave sql thread起來 ，除非剛剛開始問題定位不準導致誤判。   
(none)@3008 21:24:19>
(none)@3008 21:24:51>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
               .........
              Master_Log_File: mysql-bin.001221
          Read_Master_Log_Pos: 319331681
               Relay_Log_File: slave-relay.023504
                Relay_Log_Pos: 319331826
        Relay_Master_Log_File: mysql-bin.001221
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             .....
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)
【疑問】
什麼導致MySQL 將錯誤日誌寫入relay_log.index 中的？

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1175855/，如需轉載，請註明出處，否則將追究法律責任。

相關文章