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!.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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!
Thank you!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, I'll write a script and schedule to check slave status. However, how to backup with binary logs?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Could you paste mysqldump syntax to dump along with the bin files?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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/r efman/5.0/ en/replica tion-optio ns-slave.h tml
http://dev.mysql.com/doc/r
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.
This option is needed in a circular replication with multiple nodes.
ASKER
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!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.