mysql主从失败 错误got fatal error 1236解决方法
由于主服务器异外重启, 导致从报错, 错误如下:
show slave status错误:mysql> show slave status\gmaster_log_file: mysql-bin.000288read_master_log_pos: 627806304relay_log_file: mysql-relay-bin.000990relay_log_pos: 627806457relay_master_log_file: mysql-bin.000288slave_io_running: noslave_sql_running: yesexec_master_log_pos: 627806304relay_log_space: 627806663 
......last_io_error: got fatal error 1236 from master when  reading data from binary log:'client requested master to start  replication from impossible position'mysql错误日志:
tail /data/mysql/mysql-error.log111010 17:35:49 [error] error reading packet from server: client requested master to start replication from impossible position ( server_errno=1236)111010 17:35:49 [error] slave i/o: got fatal error 1236 from master when reading datafrom binary log: 'client requested master to start replication from impossibleposition', error_code: 1236111010 17:35:49 [note] slave i/o thread exiting, read up to log 'mysql-bin.000288',position 627806304按照习惯, 先尝试必改position位置.
mysql> stop slave;mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625751;mysql> start slave;错误依旧, 接下来登陆到主服务器查看binlog日志.
先按照错误点的标记去主服务器日志中查找:
[root@db1 ~]# mysqlbinlog --start-position=627655136 /data/mysql/binlog/mysql-bin.000288/*!40019 set @@session.max_insert_delayed_threads=0*/;/*!50003 set @old_completion_type=@@completion_type,completion_type=0*/;delimiter /*!*/;# at 4#111010 13:31:19 server id 4 end_log_pos 106 start: binlog v 4, server v 5.1.45-logcreated 111010 13:31:19# warning: this binlog is either in use or was not closed properly.binlog 'f1attg8eaaaazgaaagoaaaabaaqans4xljq1lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaauwaegggaaaaicagc'/*!*/;delimiter ;# end of log filerollback /* added by mysqlbinlog */;/*!50003 set completion_type=@old_completion_type*/;没有看到这个位置.
[root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > test.txtless text.txt看最后一部分# at 627625495#111010 16:35:46 server id 1 end_log_pos 627625631 query thread_id=45613333exec_time=32758 error_code=0set timestamp=1318289746/*!*/;delete from freeshipping_bef_update where part='ar-4006wlm' and code=''/*!*/;# at 627625631#111010 16:35:46 server id 1 end_log_pos 627625751 query thread_id=45613333exec_time=32758 error_code=0set timestamp=1318289746/*!*/;delete from shippingfee_special where part='ar-4006wlm'/*!*/;delimiter ;# end of log filerollback /* added by mysqlbinlog */;/*!50003 set completion_type=@old_completion_type*/;找到最接近错误标记627655136的一个position是627625631.
再回到slave机器上change master, 将postion指向这个位置.
mysql> stop slave;query ok, 0 rows affected (0.00 sec)mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625631;query ok, 0 rows affected (0.06 sec)mysql> start slave;query ok, 0 rows affected (0.00 sec)再次查看
mysql> show slave status\g*************************** 1. row ***************************slave_io_state: queueing master event to the relay logmaster_host: 192.168.21.105master_user: repmaster_port: 3306connect_retry: 10master_log_file: mysql-bin.000289read_master_log_pos: 25433767relay_log_file: mysql-relay-bin.000003relay_log_pos: 630relay_master_log_file: mysql-bin.000289slave_io_running: yesslave_sql_running: yes主从同步正常了, 同样的方法修复其它slave机器.
，