replication problem after master crash

MySQL & MariaDB Other MySQL® Questions
1

Hi

I have a problem in replication after master server crash. I have one master mysql process and one slave mysql process running on one server. When this server rebooted (after several months uptime) I see the following situation:

On slave in error log:

070501 20:03:35 [Note] Slave SQL thread initialized, starting replication in log ‘database3-bin.000071’ at position 668379575, relay log ‘./database3-relay-bin.000001’ position: 665822307070501 20:03:35 [Note] Slave I/O thread: connected to master ‘slave@d3:3416’, replication started in log ‘database3-bin.000071’ at position 668379575070501 20:03:36 [ERROR] Error reading packet from server: Client requested master to start replication from impossibleposition (server_errno=1236)070501 20:03:36 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log070501 20:03:36 [ERROR] Slave I/O thread exiting, read up to log ‘database3-bin.000071’, position 668379575

On slave “SHOW SLAVE STATUS\G”:

Slave_IO_State: Master_Host: d3 Master_User: slave Master_Port: 3416 Connect_Retry: 1 Master_Log_File: database3-bin.000071 Read_Master_Log_Pos: 668379575 Relay_Log_File: database3-relay-bin.000002 Relay_Log_Pos: 4 Relay_Master_Log_File: database3-bin.000071 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: … Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 668379575 Relay_Log_Space: 4 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL

On master “SHOW MASTER LOGS;”:

…| database3-bin.000062 | 1617447 || database3-bin.000063 | 43913303 || database3-bin.000064 | 284939867 || database3-bin.000065 | 1073742101 || database3-bin.000066 | 110044733 || database3-bin.000067 | 1073742071 || database3-bin.000068 | 290598099 || database3-bin.000069 | 126 || database3-bin.000070 | 4 || database3-bin.000071 | 668358853 || database3-bin.000072 | 79 || database3-bin.000073 | 85353363 |

Have anyone this problem? It looks like master doesn’t flushed logs to disk or smth like this…

PS
sorry for my english I’m not a native speaker :wink:

2

I think you are correct.

For some reason your binary log file is shorter than what the slave is expecting.

You didn’t say what happened when the master crashed.
Was it just MySQL or was it the entire OS?

Either way you have ended up with that the slave is requesting a position in the binary log on the master that is of the end of the file.

So you must perform a re-syncronization between the master and slave to sort this out.

3
[B]sterin wrote on Thu, 03 May 2007 01:11[/B]
I think you are correct.

For some reason your binary log file is shorter than what the slave is expecting.

You didn’t say what happened when the master crashed.
Was it just MySQL or was it the entire OS?

It was a server reboot (power problems or smth like this)

[B]sterin wrote on Thu, 03 May 2007 01:11[/B]

Either way you have ended up with that the slave is requesting a position in the binary log on the master that is of the end of the file.

So you must perform a re-syncronization between the master and slave to sort this out.

JFYI:
I found this in mysql documentation may be it helps somebody else:

[B]Quote:[/B]

A crash on the master side can result in the master’s binary log having a final position less than the most recent position read by the slave, due to the master’s binary log file not being flushed. This can cause the slave not to be able to replicate when the master comes back up. Setting sync_binlog=1 in the master my.cnf file helps to minimize this problem because it causes the master to flush its binary log more frequently.

I'll try this.