1

I have a MySQL master-master replication with second master in read-only mode. My questions reg backup are:

  1. I have 10 databases so should I take daily backup as one dump containing all the databases, if yes when restoring will I be able to restore each database from the full dump which has all the databases? Or taking one db at a time is the good way to go?

  2. Should I take backup from active master or read-only master? If read-only master is the case, then what if before the replication had broken and the backup has been taken from read-only - it will not have the updated data until the replication is active again right? So backing up from active master is fine?

  3. How to go about backing binary logs since im not aware of it much.? What is the process to take binary log backup.?

Thanks!!

Improve this question

1 Answer 1

Reset to default
4

A1.

How large are the databases?

If you need logical backup, you can use mysqldump (--single-transaction, --master-data are some options you need to review and use). For speed, you want to go with mydumper/myloader tool. It will also make sure of taking individual dumps.

Advantage here is you can grab the database you need to restore without having touch other databases of the instance. If you choose to go with mysqldump, you still can use mysqldumpsplitter to extract the tables/databases of your choice to export from full dump.

Alternatively, you might want to use the physical backup using Percona's Xtrabackup which provides hotbackup. Here is a post to setup Xtrabackup using Holland framework.

A2.

Backup from Active Master? No!

If we have replication failure on read-only master than priority is to get it fixed and then take the backup. But try to avoid backsups from Active Master. It's also advised to make sure your backups are stored on a different location other than read-only master!

A3.

Binary log backups provide point-in-time restore. You can store binary logs to a separate partition and have it backedup/snapshot or scripted to copy files to a separate location.

Check out these two articles: 1 & 2

Hope this is helpful!!

Improve this answer
2
  • 1. Databases are very small in size. Im going to use mysqldump but should I take as one file containing all the databases or individual? 2. How to take binary logs backup as in frequency? say I take daily full backups after which it has to flush the logs so that new binary log is created and we backup the new binary log. is that right way?
    – tesla747
    Commented Sep 2, 2015 at 7:27
  • 1. You need individual db backups if: A. You see frequent need to re-load individual tables from backups. (I wonder why!!) B. Are all your databases not inter-related. Considering a small database it's okay to use single file. You can always split your dump. 2. You can use --delete-master-logs to purge binlogs with backups but binlog-backup frequency is upto you to decide. You can copy binlogs every hour or two or twice a day or less frequently... Depends on how close you want to be for point-in-time restores! dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
    – mysql_user
    Commented Sep 2, 2015 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.