mysql replication 故障处理一则

今早收到报警邮件,来至一台mysql slave

***_db : Slave io may not running.

 

 登陆到那台服务器上查看

mysql> show slave status\G

的确看到

Slave_IO_Running: No

 

不解,遂继续查看mysql slave 的error log

090107 11:57:41 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
090107 11:57:41 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
090107 11:57:41 [Note] Slave I/O thread exiting, read up to log '***-bin.000147', position 660737192

 

slave 去读取binary log,但是超出了master 上实际log的大小, 奇怪,我再登陆到master上查看


-rw-rw----  1 mysql mysql  660726317 Jan  7 08:04 ***-bin.000147
-rw-rw----  1 mysql mysql    7791850 Jan  7 08:54 ***-bin.000148
-rw-rw----  1 mysql mysql   19955513 Jan  7 11:59 ***-bin.000149

明白了,***-bin.000147 只有660726317  bytes,但是slave 要读取的是第660737192个byte, 当然失败了.估计是master 曾经出错重启了,丢失了部分的binary log.


接下来是如何处理, 安全可靠的办法当然是重新copy一份master数据到slave上,初始化一遍,从头开始.
在数据完整性要求不是这么强的前提下,可以用另外一个方法.
强制slave读取下一个log文件

mysql> CHANGE MASTER TO MASTER_LOG_FILE = '***-bin.000148' , MASTER_LOG_POS = 0;
mysql> slave start;

 
搞定!