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.
-----Original Message----- From: John Horne [mailto:john.horne@plymouth.ac.uk] Sent: Wednesday, August 13, 2014 7:53 AM To: CentOS list Subject: [CentOS] MySQL - replication - how to restore master?
Hello,
<SNIP>
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:
<SNIP>
Note: I have not been running MySQL or HA systems for a very good while now. Salt appropriately.
Perhaps the following URL can help: http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html
I believe useful sets of key words for the searching the web would be: mysql master slave promotion and mysqldump slave
http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-mysqldu...
Even when this disclaimer is not here: I am not a contracting officer. I do not have authority to make or modify the terms of any contract.
It's quite simple really. Just make your master a slave of your slave. It's called Multi Master.
On Wed, Aug 13, 2014 at 9:53 PM, John Horne john.horne@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:
- 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.
- 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@centos.org http://lists.centos.org/mailman/listinfo/centos
-
David Beveridge -
Denniston, Todd A CIV NAVSURFWARCENDIV Crane -
John Horne