Mysql Replication restart

Question

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?

Answer 1

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.

Answer 2

You have only two vaiable options

OPTION #1

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.