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

第一次遇到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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-1175855/