[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 950
  • Last Modified:

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!.
0
Thyagaraj03
Asked:
Thyagaraj03
  • 9
  • 7
  • 2
8 Solutions
 
arnoldCommented:
Mysqldump will do the backup of the data present at the time.
The .bin are the transactional files that you would use to narrow the data loss.

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?

You are running two MySQL instances on each server?
Master/master deals with having an instance on each system with increment and offset set to have a circular replication where serverA database is configured both as a master and a slave to serverB
0
 
Thyagaraj03Author Commented:
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.
0
 
Thyagaraj03Author Commented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Thyagaraj03Author Commented:
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!
0
 
arnoldCommented:
Mysqldump without locking will not interfere with operation.

Since your setup have so many redundancies, you can run the mysqldump on the slave the difficulty with that is that you will have difficulties determining the .bin files from the master/master instances for purposes of restoring to a point in time.

The frequency at which you will run the mysqldump will govern the maximum possible data loss.

Avoiding deletes by using a delete column to mark a record deleted might help. This way you add another sanity check. The difficulty though, is that if there are many records that are being marked for deletion, eventually the sanity check will become similar to a direct deletion with a delay.
0
 
johanntagleCommented:
The --master-data option (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_master-data) allows you to determine the binary logs and position needed for point in time recovery.  

Having said that, I don't use mysqldump for backups.  I use Percona XtraBackup (http://www.percona.com/software/percona-xtrabackup) on a slave.  The resulting backup is not a dumpfile but a replica of the data directory and also has options for recording the binary log file and position needed for point in time recovery.  I find restoring using this much faster than restoring from an SQL dump file.
0
 
Thyagaraj03Author Commented:
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.
0
 
arnoldCommented:
If you backup once a day, without having the timing and the binary log it would be difficult to restore data to a point in time prior to an erroneous issued delete.
Setup monitoring of slave status to detect the first instance of replication failure.
A similar process monitoring the status of the masters should be implemented.
0
 
Thyagaraj03Author Commented:
Yes, I'll write a script and schedule to check slave status. However, how to backup with binary logs?
0
 
arnoldCommented:
Binary logs are the transactions that run on the server.
These can be used in the replay the transactions
i.e. you use the mysqldump to restore the database.
You would then use the .bin files to rerun the transactions that occurred after the backup was taken, but before the wrong command was issued.
http://dev.mysql.com/doc//refman/5.0/en/binary-log.html
0
 
Thyagaraj03Author Commented:
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?
0
 
johanntagleCommented:
I don't think such an option exists.  I backup binary logs separately - every nn hours I have a script that runs "mysqladmin -u root -p flush-logs" then backs up the binary logs, except for the latest one that was generated by the mysqladmin command.
0
 
Thyagaraj03Author Commented:
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
0
 
arnoldCommented:
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.
0
 
Thyagaraj03Author Commented:
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!
0
 
arnoldCommented:
bin logs are files on a filesystem and will be backed up as a filesystem backup (excluding the currently active one).i.e.
mysqldump mysql data into file
binlogs are files that have the performed transaction by the database.
having a filesystem backup of the dump file and the binlogs is what you need.
what is your backup setup?
do you have mysqldump filename.date?
or do you have other external backups, bacula, amanda, zmanda, etc.
0
 
Thyagaraj03Author Commented:
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?
0
 
arnoldCommented:
Flushing logs tellsmysql to attach to a new one.
You can control it using the variables parameter defines the maximum size of the binary log.
The real reason of issuing the flash right after a mysqldump is to have a clearer chain of which transactions occurred after the full backup.

You also have to deal with issuing the purge master bin log to avoid the buildup of logs leading to the filesystem running out of space.
The same for the slave logs if any.
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now