Tuesday, April 24, 2007

Client asks master to start replication from impossible position

I get this question a lot. Why does a slave report that it's trying to replicate from an impossible position? 9 times out of 10 it's because the master crashed and when it came back online a new binlog file was made.

mySQL caches binlog events in the binlog cache, basically events are stored in memory and flushed to disk when the dirty buffer fills up. I believe the variable is called binlog_cache_size.

Here are some steps to recover from this:
Go onto the master execute


Look at the output and find the log that the slave is pointing to. Look at the File size field.

Next look at the slave output from the slave reporting the issue. Look at Exec_Master_Log_Pos, is that value greater then the File Size on the master if so issue

slave start;

Now if your super sensitive of lost events because a row or two could of been lost from this replication event, do some spot testing for tables written to often. Look for anything that has changed within the outage window, if the data doesn't match the slave then you're master and slave are out of sync, and will require a full clone to get the data back in sync.

The other 1 out of 10 is usually user error.


webguy said...

Thanks for the help. Saved us some hassle.

davidoff said...

THANKS! I was looking at the mysql website/forum for this straight to the point, easy and quick solution, and none!

Boni said...

Puh.... Thx a lot for this info!!!
You save my day!!