Author Topic: Incremental DB Backup Script  (Read 4805 times)

Offline kingcat

  • Junior Indexer
  • **
  • Posts: 20
  • Helpful: +5/-0
  • NZB.Cat
    • NZB.Cat
Incremental DB Backup Script
« on: 2016-03-19, 07:37:42 am »
Most if it is explained in the gist:

But here's the long and short of it:

This script is meant to run every 8 hours via cron, it assumes during the 4th running of it, a day has gone by (though it will work with a longer or shorter interval, you just won't get a nice single archive per day, you might get 1 every 2 days for a 12 hour interval or 2 per day with a 4 hour interval, look at the script and you'll see what i mean).

It uses Percona's excellent innobackupex perl script to take an initial snapshot of your database, it does this by doing a live copy of the bin logs and data directories of your mysql installation, it also backs up your my.cnf. This happens on the first and last interval, on the 3 intervals in between, it will run an incremental backup instead. This means it will only copy over the data that has changed since the base backup was taken. I've done it this way to allow for more frequent backups but without the additional drive storage or time it takes to do a full backup. 8 hours seems to be a good interval for my very active indexer. I generate around 3-5gb of changes in that time depending on activity.

On the fourth interval, it will apply the incremental backups to the base backup, tar.gz the base folder into a timestamped archive then delete the base and incremental backups. Then it will create a new base backup for the next interval.

Why not just have infinite incremental backups instead of rotating daily? Well the restore process is kind of annoying, you have to apply the incrementals to the base one by one in the order they were taken so if you had dozens of incremental backups it could take awhile to do a restore.


How to do a restore:

Here's the documentation on the innnobackupex incremental process straight from percona if you're interested: but the general gist is this:

Lets say you had 2 incremental backups done and something happened to your database after the last one was taken, you want to roll back to the latest incremental backup, this is how you would do it.

1. First, disable the cron job that runs this script just to be sure.
Code: [Select]
cd /path/to/backups
innobackupex --apply-log --redo-only ./base
innobackupex --apply-log --redo-only ./base --incremental-dir=./hourly.1
innobackupex --apply-log ./base --incremental-dir=./hourly.2
innobackupex --apply-log ./base
3. Now the base backup is ready to restore, running the following command will actually copy the backup back to your data directory:
Code: [Select]
cd /path/to/backups
innobackupex --copy-back ./base
4. You may need to fix permissions after the copy, usually it's mysql.mysql for /var/lib/mysql (or wherever you have your data dir in your my.cnf)

And thats it. If you had 3 incrementals instead of 2, you add an extra line in step 2, making sure to remove the "redo-only" flag only for the last incremental you run. It's kind of confusing but reading the official Percona docs will help clear it up.


1. Implement a restore function to make it more robust.
2. Cleanup old archives after N days.

I hope this helps, please email or contact me on IRC for questions/comments/suggestions. I don't really monitor the forums.

99% Completion, 2000 Days Retention