show slave status错误:

1. mysql> show slave status\G

2. Master_Log_File: mysql-bin.000288

3. Read_Master_Log_Pos: 627806304

4. Relay_Log_File: mysql-relay-bin.000990

5. Relay_Log_Pos: 627806457

6. Relay_Master_Log_File: mysql-bin.000288

7. Slave_IO_Running: No

8. Slave_SQL_Running: Yes

9. Exec_Master_Log_Pos: 627806304

10. Relay_Log_Space: 627806663

11. ......

12. Last_IO_Error: Got fatal error 1236 from master when  reading data frombinary log:

13. 'Client requested master to start  replication from impossible position'

mysql错误日志:

1. tail /data/mysql/mysql-error.log

2. 111010 17:35:49 [ERROR] Error reading packet from server: Client requested master

3. to start replication from impossible position ( server_errno=1236)

4. 111010 17:35:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data

5. frombinary log: 'Client requested master to start replication from impossible

6. position', Error_code: 1236

7. 111010 17:35:49 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000288',

8. position 627806304

按照习惯, 先尝试必改position位置.

1. mysql> stop slave;

2. mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625751;

3. mysql> start slave;

错误依旧, 接下来登陆到主服务器查看binlog日志.
先按照错误点的标记去主服务器日志中查找:

1. [root@db1 ~]# mysqlbinlog --start-position=627655136 /data/mysql/binlog/mysql-bin.000288

2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;

3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

4. DELIMITER /*!*/;

5. # at 4

6. #111010 13:31:19 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log

7. created 111010 13:31:19

8. # Warning: this binlog is either in use or was not closed properly.

9. BINLOG '

10. F1aTTg8EAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

11. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

12. '/*!*/;

13. DELIMITER ;

14. # Endof log file

15. ROLLBACK /* added by mysqlbinlog */;

16. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

没有看到这个位置.

1. [root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > test.txt

3. less text.txt

4. 看最后一部分

5. # at 627625495

6. #111010 16:35:46 server id 1 end_log_pos 627625631 Query thread_id=45613333

7. exec_time=32758 error_code=0

8. SETTIMESTAMP=1318289746/*!*/;

9. deletefrom freeshipping_bef_update where part='AR-4006WLM'and code=''

10. /*!*/;

11. # at 627625631

12. #111010 16:35:46 server id 1 end_log_pos 627625751 Query thread_id=45613333

13. exec_time=32758 error_code=0

14. SETTIMESTAMP=1318289746/*!*/;

15. deletefrom shippingFee_special where part='AR-4006WLM'

16. /*!*/;

17. DELIMITER ;

18. # Endof log file

19. ROLLBACK /* added by mysqlbinlog */;

20. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

找到最接近错误标记627655136的一个position是627625631.

再回到slave机器上change master, 将postion指向这个位置.

1. mysql> stop slave;

2. Query OK, 0 rows affected (0.00 sec)

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

5. Query OK, 0 rows affected (0.06 sec)

7. mysql> start slave;

8. Query OK, 0 rows affected (0.00 sec)

再次查看:

1. mysql> show slave status\G

2. *************************** 1. row ***************************

3. Slave_IO_State: Queueing master event to the relay log

4. Master_Host: 192.168.21.105

5. Master_User: rep

6. Master_Port: 3306

7. Connect_Retry: 10

8. Master_Log_File: mysql-bin.000289

9. Read_Master_Log_Pos: 25433767

10. Relay_Log_File: mysql-relay-bin.000003

11. Relay_Log_Pos: 630

12. Relay_Master_Log_File: mysql-bin.000289

13. Slave_IO_Running: Yes

14. Slave_SQL_Running: Yes

主从同步正常了