Link to home
Start Free TrialLog in
Avatar of Thyagaraj03
Thyagaraj03

asked on

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate Slave for each master on Ubuntu servers.

What is the procedure of taking the backup of Masters and Slaves with mysqldump?. What happens if I directly issue mysqldump on master servers?. I have to schedule to run at some specific time.


Thanks in advance!.
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Thyagaraj03
Thyagaraj03

ASKER

You are running two MySQL instances on each server?
Yes, master-master replication between two different servers but connecting any one master for database changes. Also each master has a slave on different machines again(totally 4).


Not quite sure what your distinction is. A slave instance can be reconfigured to be a master in a short nstance.
Are you looking to backup the master frm slave instance?
Not sure. I just want to backup all databases on all the servers(masters & slaves) to increase the recovery chances. What's in my mind is, as I heard somewhere that using mysqldump on masters may not be safe. Not sure it's true, if it's true aim to know the reason. Just want to know the best procedure for backing up master servers with no disturbance to masters(sync) and I want to schedule the procedure.
A slave instance can be reconfigured to be a master in a short nstance.
I used two types of replication, master-master and master-slave. I used master-slave because, thinking if any problem with master-master replication I can recover them from their slaves. I might not look for reconfigure slave to be master in case of failure as I have two masters to switch quickly.
Overall, backup is required as everybody already know that accidental removal of db or tables remove them on all the servers in any replication type. So looking for the best way to backup databases without causing any disturbance to replication especially master-master.

Thank you!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So what I understood is, it's safe running mysqldump with --master-data option on masters and slaves without locking the tables. Or else I can schedule mysqldump only on slaves for backup. I'll also give a try of Percona XtraBackup.


The frequency at which you will run the mysqldump will govern the maximum possible data loss.
I didn't really didn't get this what should be done.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, I'll write a script and schedule to check slave status. However, how to backup with binary logs?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I thought with "log-slave-updates" in mysql configuration bin files are also transferred and updated. And I just dump from master and restore on slave changing the master postion.
Could you paste mysqldump syntax to dump along with the bin files?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I mean to say mysql slave configuration option, not mysqldump option. The below link form mysql tells about "log-slave-updates" somewhere in the middle.
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html
log-slave-updates deals with whether the sale instance will maintain a binary log for transactions it processed.
This option is needed in a circular replication with multiple nodes.
As a final note, could the place down the best way to backup bin logs as well while or after mysqldump. I am expecting some syntax and step by step procedure for master-slave.

Thanks!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Only mysqldump I use for backup. How do I flush logs safely? directly flushing logs have any bad affect on sync? else I can directly flush logs on master and take the dump on slave?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial