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

[David Beveridge]

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
>