mysql 1236 bug_MySQL1236错误的恢复

从库出现问题

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State:

Master_Host:192.168.220.141Master_User: rep

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000005 I/O线程当前正在读取的主服务器二进制日志文件的名称Read_Master_Log_Pos:764 在当前的主服务器二进制日志中，I/O线程已经读取的位置Relay_Log_File: relay-bin.000018 SQL线程当前正在读取和执行的中继日志文件的名称Relay_Log_Pos:4 在当前的中继日志中，SQL线程已读取和执行的位置Relay_Master_Log_File: mysql-bin.000005 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0 最后的错误号，0表示没有错误Last_Error: 　　　　　　　　　　　　　　　　　 最后错误的描述

Skip_Counter:0 最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值Exec_Master_Log_Pos:764 来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置(Relay_Master_Log_File)。在主服务器的二进制日志中的(Relay_Master_Log_File,Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)Relay_Log_Space:107　　　　　　　　　　　　　 所有原有的中继日志结合起来的总大小Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1

1 row in set (0.00sec)

ERROR:

No query specified

mysql>

查看mysql的错误日志

大致的意思就是说，从库不能读取主库的binlog日志文件了，造成这种故障的原因可能是：

从库不能连接主库

主库的binlog日志被删除了

.......

[root@mysql ~]# tail /data/3307/mysql.err //查看错误日志151129 23:14:41 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000005' at position 764, relay log '/data/3307/relay-bin.000018' position: 4

151129 23:14:41 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)151129 23:14:41 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236

151129 23:14:41 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000005', position 764[root@mysql3307]# ping 192.168.220.141 //测试和主库的连通性PING192.168.220.141 (192.168.220.141) 56(84) bytes of data.64 bytes from 192.168.220.141: icmp_seq=1 ttl=64 time=0.197ms64 bytes from 192.168.220.141: icmp_seq=2 ttl=64 time=0.029ms64 bytes from 192.168.220.141: icmp_seq=3 ttl=64 time=0.025ms^C--- 192.168.220.141 ping statistics ---

查看主库的日志

Slave I/O thread exiting, read up to log 'mysql-bin.000005', position 764提示读取mysql-bin.000005错误，查看主库的binlog日志后发现没有mysql-bin.0000005日志文件

这是因为长时间没有开启数据库，binlog的存活时间是7天，所以binlog00000005日志应该是被删除了

规定binlog日志存活时间的参数在my.cnf中

expire_logs_days = 7 //单位是天，0表示不自动删除

[root@mysql ~]# ll /data/3306/total36drwxr-xr-x 5 mysql mysql 4096 Nov 29 23:08data-rw-r--r-- 1 mysql mysql 1899 Oct 16 00:46my.cnf-rwx------ 1 root root 1307 Oct 16 01:44mysql-rw-rw---- 1 mysql mysql 107 Nov 29 23:08 mysql-bin.000006

-rw-rw---- 1 mysql mysql 28 Nov 29 23:08 mysql-bin.index

srwxrwxrwx1 mysql mysql 0 Nov 29 23:08mysql.sock-rw-r----- 1 mysql root 10993 Nov 29 23:08mysql_oldboy3306.err-rw-rw---- 1 mysql mysql 5 Nov 29 23:08mysqld.pid

[root@mysql~]#

调整从库的同步位置：

change master to master_log_file='mysql-bin.000006',master_log_pos=4

mysql>stop slave;

Query OK,0 rows affected (0.00sec)

mysql> change master to master_log_file='mysql-bin.000006',master_log_pos=4;

Query OK,0 rows affected (0.32sec)

mysql>start slave;

Query OK,0 rows affected (0.00sec)

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.220.141Master_User: rep

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000006Read_Master_Log_Pos:107Relay_Log_File: relay-bin.000002Relay_Log_Pos:253Relay_Master_Log_File: mysql-bin.000006Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:107Relay_Log_Space:403Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0 //没有IO错误了Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1

1 row in set (0.00sec)

ERROR:

No query specified

mysql>