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
You have only two vaiable options
Step 1) Make sure binary logs are activated on db1
Logical position 0 for MySQL 3.x/4.x/5.0 is 98
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:
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
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.