Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You're probably best off rebuilding the slave –  FreshPhilOfSO Aug 16 '12 at 16:14
add comment

1 Answer

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.

EXAMPLE

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 Feb 04, 2011 post MySQL master binlog corruption) Let's use MySQL 5.5 for this example. Here is the mysqlbinlog output of a empty binary log for MySQL 5.5 running on my Windows Desktop:

C:\MySQL_5.5.12\data>dir mysql-bin.000001
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.5.12\data

08/16/2012  02:29 PM               107 mysql-bin.000001
               1 File(s)            107 bytes
               0 Dir(s)  191,225,950,208 bytes free

C:\MySQL_5.5.12\data>mysqlbinlog mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120816 14:29:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.12-log created 120816 14:29:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IzwtUA8BAAAAZwAAAGsAAAABAAQANS41LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAjPC1QEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The file is exactly 107 bytes and there is header info within it that marks end_log_pos 107. If you were to setup a slave that is newly installed, you would run

CHANGE MASTER TO
MASTER_HOST='IP_of_Master',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Then, run START SLAVE;. That's it. MySQL Replication takes off from there.

NOW, YOUR SITUATION ...

The only reason I have ever encountered your situation (MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos) is this (Still Writing)...

MySQL Replication functions with two threads

  • IO Thread : Responsible for Maintaining a Connection Back to the Master. It is used to collect SQL entries recorded in the Master's Binary Logs and storing those entries in the Slave's Relay Logs.
  • SQL Thread : Responsible for Reading the Entries from the Slave's Local Relay Logs, Executing the SQL, and Rotating the Fully Used Relay Logs.

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 end_log_pos is read in the header of the SQL statement before the statement itself is actually read. This usually happens under these circumstances:

  • mysqld was restarted on the Master while replicating
  • Master DB Server crashes while replicating
  • possible dropped packets during the session between Master and Slave

REMEDY

The simplest remedy when this happens is to skip to the next binary log on the Master.

Suppose you have the following

  • running MySQL 5.5 on the Master
  • Read_Master_Log_File in SHOW SLAVE STATUS\G is mysql-bin.012345

The remedy is to run this (skip to the next binary log mysql-bin.012346)

STOP SLAVE;
CHANGE MASTER TO
master_log_file='mysql-bin.012346',
master_log_pos=107;
START SLAVE;

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.

BUT WAIT...

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 CHANGE MASTER TO command right now, it will erase any binary logs you have now. What can you do ???

Unfortunately, you must rebuilt the Slave from scratch.

You would have to do the following on the Slave

echo "STOP SLAVE;" > MySQLData.sql
MYSQLDUMP_OPTIONS="-hIP_of_Master -u... -p..."
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases --triggers --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1 --single-transaction"
mysqldump ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysqldump -uroot -prootpass < MySQLData.sql

This will stop the slave, reload the data, and start the slave. The option master-data=1 will write the CHANGE MASTER TO command at line 23 of MySQLData.sql.

Give it a Try !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.