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.

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)?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
This method kind of presupposes you're already in a pinch and did it wrong from the start. If for what ever reason start-over-from-scratch and either manually note the slave's position or use the newer --dump-slave mysqldump flag is not an option it seems check summing against the original slave is in order once it's "caught up". pt-table-checksum and pt-table-synch might be of use. You say to skip dupe key errors but what about the replaying of statements that would not collide; just create dupes or relative updates that leave inconsistent values? –  atxdba Jun 16 '12 at 18:47

Risks/downsides, especially if using statement based replication:

  • insert into table (pk_auto_inc_col, value_column) values (null, 123); No collision there, it will just create a duplicate record. Subsequent inserts are now all offset
  • update tbl set col=col+1; The col value gets incremented again
share|improve this answer

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.