we are building a simple master/slave MySQL configuration using asynchronous replication, with MySQL enterprise 5.5.17 on both servers and innoDB based tables. In case of a crash of the master server we would like to offer to our users the possibility of recovering the master using the most-up-to date database contents of the slave. On the master server, the database and the binary logs are stored in different disk devices, for better reliability.
What would be the best way to do this? I was trying to outline a procedure for this, but I'm not sure this is correct:
- Ensure that all the statements contained in the relay log of the slave are executed. Ideally, I could execute a STOP SLAVE IO_THREAD, even if it shouldn't be necessary as the master has supposingly crashed and no other statements are coming to the slave, and wait for the remaining relay events to be completed.
- Shut down the database on the slave and copy over the files to the database files to the master.
- From the relay-log.info and the master.info on the slave I should be able to find out what is the latest binary log on the master from which the slave was reading and at which position.
- I could replay the binary logs on the master from the last statement executed by the slave before the master crashed up to the last statement available in the logs.
- I should reset the SLAVE and restart the replication from the last statement executed by the slave before the master crashed.
Is this OK?