Mysql Incremental Backup
2012 April 17 at 15:51 » Tagged as :mysql, innobackupex, percona, xtrabackup,
for t in `echo "show tables" | mysql --batch --skip-column-names photos;`;
mysql photos -e "ALTER TABLE $t ENGINE = InnoDB;";
doneThe 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-48The '/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-45Before 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-48Then 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.