mysql 1236错误解决方法
由于主服务器异外重启, 导致从报错, 错误如下:
show slave status错误:
mysql> show slave status/g
master_log_file: mysql-bin.000288
read_master_log_pos: 627806304
relay_log_file: mysql-relay-bin.000990
relay_log_pos: 627806457
relay_master_log_file: mysql-bin.000288
slave_io_running: no
slave_sql_running: yes
exec_master_log_pos: 627806304
relay_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.log
111010 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 data
from binary log: 'client requested master to start replication from impossible
position', error_code: 1236
111010 17:35:49 [note] slave i/o thread exiting, read up to log 'mysql-bin.000288',
position 627806304
[root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > mysql-bin.000288.sql
less mysql-bin.000288.sql
看最后一部分
# at 627625495
#111010 16:35:46 server id 1 end_log_pos 627625631 query thread_id=45613333
exec_time=32758 error_code=0
set 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=45613333
exec_time=32758 error_code=0
set timestamp=1318289746/*!*/;
delete from shippingfee_special where part='ar-4006wlm'
/*!*/;
delimiter ;
# end of log file
rollback /* 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 log
master_host: 192.168.21.105
master_user: rep
master_port: 3306
connect_retry: 10
master_log_file: mysql-bin.000289
read_master_log_pos: 25433767
relay_log_file: mysql-relay-bin.000003
relay_log_pos: 630
relay_master_log_file: mysql-bin.000289
slave_io_running: yes
slave_sql_running: yes
主从同步正常了, 同样的方法修复其它slave机器.
bitscn.com