【MySQL】错误信息写入slave_relay_log.index 案例一则

  • 杨奇龙

    2014-06-04 23:32:27
  • MySQL
  • 原创
第一次遇到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 中的？

请使用浏览器的分享功能分享到微信等