1

I've a mission critical MySQL Master/Slave config, which I'm backing up with innobackupex. I want to speed it up a little as the backup is about 60GB, so I'm considering something like a full backup on Sunday's and incremental backups from Monday to Saturday, or something like that.

My questions, as I've master and slave:

  • Is a good idea to backup from slave?
  • Am I going to be able to spin up a new slave from this backups? (if not, I'll backup from master)
  • Do you recommend any other incremental backup strategy?

Thanks.

2

It's a good idea to take a backup from a slave as long as you make sure the slave is consistent with the master. Usually a regular pt-table-checksum job gives you a good level of confidence.

Yes, you can create clones from a backup taken from the slave. Just make sure you add --slave-info option so xtrabackup records the binary log position. You can use it in CHANGE MASTER when setting up a new slave.

At TwinDB we decided to go with differential backups. It's when you take a full copy on Sunday and each other day you save the difference between Sunday and the current day. That has some benefits:

  • Faster recovery time. You need to prepare the full copy and only one incremental copy.

  • Lower probability of failure. If you have a chain of incremental copies every copy must be good otherwise you can't restore. With differential backups there is only one copy to apply on top of the full copy. So it's less likely to fail.

  • In most of cases total size of backup copies is still reasonable. But of course with incremental copies save more space.

Between daily backup jobs it makes sense to store binary logs. So you can do point in time recovery and recovery time can be reasonable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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