Hello,
We have MySQL running as a master which is replicating to a single slave server. We are, however, considering what is required when a 'disaster' of some sort happens to either server. By disaster, this could be some event which requires the entire server to be rebuilt, and which would usually include restoring from nightly backups directories such as '/var/lib/mysql' and '/var/log/mysql' (as set in our my.cnf file). It could also refer to an event which only affects the mysql service, but requires us to stop the mysql master service. This may involve reinstalling the mysql package, and, again, restoring the '/var/lib/mysql' and '/var/log/mysql' directories.
In the case of losing the slave server, we have found instructions for rebuilding the slave database and restarting replication using a mysqldump backup taken from the master server. We have tested this and it works fine.
However, I am having trouble finding out what to do should we lose the master server. Typically mysqldump backups of the master are done overnight, so a failure during the day would mean that the slave is ahead of the master backup. So this poses two questions:
1) If the master fails, and we perform (at that time) a mysqldump of the slave, we could import the data into the master, but what commands do we need to tell the master (and slave?) to start replication based on the imported data? As far as I can gather the master replication data is held in the '/var/log/mysql' directory (in our case) in the bin log files, and these would typically be restored after a disaster.
2) If the master fails and we import the overnight backup data, what commands do we then need to issue on the master and slave to restart replication from the imported data? In particular, on the master do we just delete the bin log files and let replication start afresh? And on the slave, which at that time would be ahead of the master, how do we sort out the replication? Do we drop the existing database and import the backup data into the slave as well, so that both the master and slave start with the same data?
Thanks,
John.