[CentOS] MySQL - replication - how to restore master?

Wed Aug 13 11:53:24 UTC 2014
John Horne <john.horne at plymouth.ac.uk>


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?



John Horne                   Tel: +44 (0)1752 587287
Plymouth University, UK