I have a slave db (db1) that I moved into the master position after my master db (db2) died. I brought up a new slave db (db3) from a day old snapshot of db1, my problem is trying to figure out which log file and position db3 should start replication at. Where should I look to find this info?


If you didn't save the log file and position when you took the snapshot, I don't believe there's a way to figure that out. Some of the snapshot tools, however, do save that data. How did you take the snapshot?

For example, if it's a filesystem snapshot taken after flushing / locking all tables, the correct data should be in master.info in the slaves data dir.

  • That is allowing me to replicate from the previous master (db2), now im catching up to that, will the binlog file and pos on this new slave end up in the same state as the old slave would have after catching up to the old master? – trobrock Mar 25 '11 at 17:52

You have only two vaiable options


Step 1) Make sure binary logs are activated on db1 Remember:

Logical position 0 for MySQL 3.x/4.x/5.0 is 98
Logical position 0 for MySQL 5.1 is 106
Logical position 0 for MySQL 5.5 is 107

For the sake of this example, we'll pretend you are running MySQL 5.1

Step 2) Try to retrieve the binary logs on db2 from the date and time of the day-old db1 snapshot up to the time db2 crashed and load the data in those logs.You can find out the date to start from by one of the following:
a) Looking at the time and date stamp of the last relay log on db1 before you cut db1 over to become master.
b) Looking at the time and date stamp of master.info on db1 before you cut db1 over to become master.
c) Run this query: SELECT MAX(update_time) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql');

Step 3) Run mysqlbinlog against all the binary logs you found in Step 2. The first of the binary logs should be based on the time of the db1 snapshot. Run the log in order and load that output to a text SQL file.

Step 4) Run the resulting SQL script(s) against db3

Step 5) On db3, do CHANGE MASTER TO MASTER_HOST='IP of db1',...,MASTER_POSITION=106;

OPTION 2) This is much easier, but requires a little downtime.

Step 1) Just mysqldump db1 and load it on db3
Step 2) Activate binary logs on db1
Step 3) On db3, do CHANGE MASTER TO MASTER_HOST='IP of db1',...,MASTER_POSITION=106;

Give it a Try !!!

PS I would recommend doing circular replication between db1 and db3 after doing this recovery. In addition, made hourly or nightly mysqldump backups of the db3.

  • Would the logical position of 106 still be correct if db1 had previously been set as a master box and already had binlogging turned on even while it was a slave? – trobrock Mar 25 '11 at 18:16
  • You would have to start with new binary logs on db1. You would thus run RESET MASTER; on db1. Then run SHOW MASTER STATUS; and you will be the beginning binary log. As I said before, 98 for pre-MySQL 5.1, 106 for MySQL 5.1, and 107 for MySQL 5.5. – RolandoMySQLDBA Mar 25 '11 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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