MySQL master-slave failure error got fatal error 1236 workaround

--mysql Master-slave failure error got fatal error 1236 workaround

----------------------------------------------------2014/05/19

Due to an external restart of the main server, resulting from an error, errors are as follows:
Show Slave status error:

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:no slave_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 REPL Ication from impossible position '

MySQL error log:

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 Mas ter 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

In accordance with the habit, first try to change the position position.

mysql> Stop slave;mysql> Change master to master_log_file= ' mysql-bin.000288 ', master_log_pos=627625751;mysql > Start slave;

Error still, next login to the master server to view the Binlog log.
First, follow the mark of the error point to go to the main server log to find:

[Email protected] ~]# mysqlbinlog--start-position=627655136/data/mysql/binlog/mysql-bin.000288/*!40019 SET @@ session.max_insert_delayed_threads=0*/;/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;D Elimiter/*!*/;# 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 on use or were not closed properly. BINLOG ' F1attg8eaaaazgaaagoaaaabaaqans4xljq1lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaau WAEGGGAAAAICAGC '/*!*/;D elimiter; # End of log filerollback/* Added by Mysqlbinlog */;/*!50003 SET [email Protected]_compl etion_type*/;

Did not see this position.

627625631 627625751 Query thread_id=45613333exec_time=32758 error_code=0set timestamp=1318289746/*!*/;d elete from shippingfee_special where part= ' ar-4006wlm '/*!*/;D elimiter; # End of log filerollback/* Added by Mysqlbinlog */;/*!50003 SET [email protected]_completion_type*/;

Find a position that is closest to error marker 627655136 is 627625631.

Then go back to the slave machine change master and point postion to this position.

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)

Check Again

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

The master-slave synchronization is normal and the same method is used to repair other slave machines.

Today, the experiment on the machine encountered this problem, according to the above method to derive binlog observation.

# at327#140519  7: A: theServer ID1End_log_pos457CRC320xcd046be7Query thread_id= -Exec_time=0Error_code=0SET TIMESTAMP=1400508772/*!*/; insert into bin_test values (1,'D'),(2,'D'),(3,'4')/*!*/; #  at457--Change the position here to solve the problem. #140519  7: A: theServer ID1End_log_pos488CRC320xcfa07af8Xid =392COMMIT/*!*/; # at488#140519  7:Panax Notoginseng: inServer ID1End_log_pos573CRC320x6c35fa44Query thread_id= inExec_time=0Error_code=0SET TIMESTAMP=1400510249/*!*/; BEGIN/*!*/; # at573#140519  7:Panax Notoginseng: inServer ID1End_log_pos697CRC320x6747d19aQuery thread_id= inExec_time=0Error_code=0SET TIMESTAMP=1400510249/*!*/; insert into bin_test values (777777777,'WI')/*!*/; # at697#140519  7:Panax Notoginseng: inServer ID1End_log_pos728CRC320x32dde783Xid =426COMMIT/*!*/;D Elimiter; # End of Log Filerollback/*added by Mysqlbinlog*/;/*!50003 SET [email protected]_completion_type*/;/*!50530 SET @ @SESSION. Pseudo_slave_mode=0*/;