Mysql – Using an older MySQL binlog filename (with position zero)? Risks? Downsides

MySQLmysqldumprestore

Let's say I'm setting up a new slave. I start with a mysqldump after running mysql -e "STOP SLAVE", transfer that and restore it to the new server.

If I didn't capture the BINLOG filename and position after the STOP SLAVE and before running the mysqldump then what happens if I simply use a previous/older binlog filename (verified to be present on the master and older than the backup), with an position of zero?

It seems to just force the new master to replay all of the events to the new slave, obviously including a number of INSERTs, UPDATEs, and DELETEs which were already represented in the backup (those which happened during the overlapping time period).

Is there anything wrong or risky about that? Is there a downside other than the extra wasted time and CPU cycles (replaying perhaps a day's worth of overlapping transactions)?

Best Answer

I have a surprise for you

Every position within a binary log is based on the filesize of the binary log as of the moment an SQL statement was written.

What is even more interesting about this concept is the fact that header for binary logs differ in start position (There is no such thing as a literal position 0)

These are the starting points for replication from a Master's empty binary log

  • 107 for MySQL 5.5
  • 106 for MySQL 5.1
  • 98 for MySQL 5.0 and back

I first wrote about this back on Feb 4, 2011

Here is a suggested course of action

  • If the relay logs still exists on the Slave
    • find the last relay log
    • run mysqlbinlog against that relay log
    • locate the timestamp of the moment you performed the mysqldump
    • Take that timestamp and locate the same timestamp in the master's binary logs\
    • Near the master's copy of the timestamp in the appropriate should be the position
    • Whatever master binary log you selected, use that binary log in the CHANGE MASTER TO command on the Slave
    • Whatever position you found in the master binary log you selected, use that position in the CHANGE MASTER TO command on the Slave

That's the best way to minimize any data collision due to duplcaiet keys

Just to be sure, on the Slave add this to my.cnf

[mysqld]
slave-skip-errors=1062

Then restart mysql. This will bypass all duplicate key collisions after running START SLAVE;. When Seconds_Behind_Master reaches 0, remove that line and restart mysql on the Slave. Everything in terms of MySQL Replication should be fine from there.

Related Question