It's quite simple really. Just make your master a slave of your slave. It's called Multi Master. see http://mysql-mmm.org/ On Wed, Aug 13, 2014 at 9:53 PM, John Horne <john.horne at plymouth.ac.uk> wrote: > 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. > > -- > John Horne Tel: +44 (0)1752 587287 > Plymouth University, UK > > _______________________________________________ > CentOS mailing list > CentOS at centos.org > http://lists.centos.org/mailman/listinfo/centos >