Mysql Incremental Backup

2012 April 17 at 15:51 » Tagged as :mysql, innobackupex, percona, xtrabackup,

Still using mysql? about time you gave up on it and switched to Percona. It is a drop in replacement for mysql. That means your existing data, and stored procedures, or data retrieval methods don't need to undergo any changes. Even if you don't switch to Percona, you can still use their xtrabackup and innobackupex tools to perform incremental backups on your mysql database(s). Innobackupex is actually a wrapper for xtrabackup and in the rest of this discussion we will only look at the former.  My initial attempts at using Xtrabackup wasn't very successul due to what turned out to be a bug.  But had better luck since then.   Though innobackpex can handle myisam, it works best with innodb tables. Whether to use innodb or myisam is often a matter of religious debate but really you should consider converting all your tables to innodb if you want smooth trouble free backup. Conversion is really easy. This is how I did it on my photo blog:

for t in `echo "show tables" | mysql --batch --skip-column-names photos;`;


    mysql photos -e "ALTER TABLE $t ENGINE = InnoDB;";


 The above is really a one liner, it's inspired by a comment on Kevin van Zonneveld's blog. Though we are planning to do incremental backups, we need a full backup to start with. Here is how I did it.

innobackupex /root/innoback --databases='venice photos lampblog'

Notice that I am backing up three different databases. Also worth mentioning that the innobackupex documentation isn't very clear about how to use the --databases options. I figured out how to use it after trial and error. If you don't include the --databases options everything will be backed up, which means the databases named 'test' and 'mysql' will also be included. If you need to backup just one db, use --databases=dbname. I have also ommited the --user=USER --password=PASSWORD parameters from the commands to reduce clutter.   The full back up will be rather large, much larger than what a good old fashioned mysqldump might produce. That's because some innodb files are copied as is. They are almost always much larger than the size of all the data they contain. Once the incremental backup is ready, make the first incremental backup, this will be the 'delta'  or the differences between what's in the full backup and whatever changes that have happened to your data since then. This is what my first incremental backup command looks like

innobackupex --incremental /root/innobak --databases='venice photos lampblog'\ --incremental-basedir=/root/innobak/2012-04-16_23-57-48

  The '/root/innobak/2012-04-16_23-57-48' directory is the timestamped location where the full backup was placed by innobackupex. Then you can make some changes to your databases and make another incrementable dump. Now the base dir changes to the location where the first incremental dump placed it's files. Which in my case was '/root/innobak/2012-04-17_00-29-45'

innobackupex --incremental /root/innobak --databases='venice photos lampblog'\ --incremental-basedir=/root/innobak/2012-04-17_00-29-45

  Before sweating any more over this we should try to do a restore to see if things have worked according to plan. If you don't want to mess up any of your existing installations the best way to try out the restoration would be to launch a new amazon EC2 instance.  (Percona installation instructions are here).   In order to restore the database, you need to process the dumps in reverse order. Starting with the full backup. The first step is
innobackupex --apply-log --redo-only /root/innobak/2012-04-16_23-57-48

            Then you need to apply the incremental backups one by one starting with the oldest and finishing with the most recent dump.
innobackupex --apply-log /root/innobak/2012-04-16_23-57-48 \
Then the innobakup recipe suggests that you 'prepare the full backup' again to rollback the pending transactions. The final step is to start up mysql with the prepared data.
rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' \
   ./ /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql/

            Make sure you change into the correct directory when you do the rsync. As you can see restoring the backups into a machine that already has some databases in it will not be a very good idea.