I am trying to repair one MySQL replication setup. One of the database's table at slave side is deleted and the updates from master side are received but because of missing table these updates are not written to the database. This setup is not repaired since many months and the slave has much to catch up. I observed that Exec_Master_Log_Pos value is greater than Read_Master_Log_Pos which should not the case. Is this some kind of bug? I am guessing that it will be back to actual normal when the slave catches up with the master. Anybody familiar with this situation?
This is a classical scenario I have seen dozens of times in my DBA career. Before I explain this, I want to share something with you. I learned something a long time ago about positions within a binary log. It actually represents the number of bytes up to a specific place when recording a SQL transaction.
When you first startup a mysql instance with binary logging enabled, you get an empty binary log. Each version of MySQL has a different size for an empty binary log (See my
The file is exactly 107 bytes and there is header info within it that marks
NOW, YOUR SITUATION ...
The only reason I have ever encountered your situation (
MySQL Replication functions with two threads
Your problem has to do with the IO Thread. If there was any intermittency experienced by the IO Thread, it was most likely figuring out the size of the incoming SQL statement from the Master but never had the chance to actually read the SQL statement. I can say with absolute certainty because in the example posted above, the
The simplest remedy when this happens is to skip to the next binary log on the Master.
Suppose you have the following
The remedy is to run this (skip to the next binary log
This will pick up from where the Slave last left off. This should be your solution in a low-write environment and if the Master has all the binary logs the Slave needs.
you said it had been months since the Slave was broken. The next binary log on the Master may no longer exist. If you run
Unfortunately, you must rebuilt the Slave from scratch.
You would have to do the following on the Slave
This will stop the slave, reload the data, and start the slave. The option
Give it a Try !!!