Backup your sandbox with XtraBackup

Today I tried to make incremental backups of a MariaDB instance in a MySQL sandbox with Percona XtraBackup.
I used the recently released XtraBackup 2.0. And of course there is documentation about making incremental backups. 

MySQL sandbox makes it easy to run many different MySQL versions on one machine. It does this by changing the port number, data directory, UNIX socket location and a whole lot more.

So I first started with a full backup and after that I used that backup as a base for the incremental backups. To do that I had to specify the port number which is 5522 and the username and password for the msandbox account. As MySQL uses a UNIX socket instead of a TCP connection if the hostname is localhost I specified 127.0.0.1 as hostname to force a TCP connection. That worked!

Then I created the incremental backup by using the --incremental option and the --incremental-basedir option to specify the location of the full backup. That also worked!

Then I tried to make a backup while putting some load on my database. I did use  "INSERT INTO test1(col1) SELECT col1 FROM test1" to do this.

The full and incremental backups still worked, or at least that's what the backup script told me. But the size of the incremental backups was quite small. And I noticed that the LSN was very small and not increasing. The xtrabackup_checkpoints file also told me that the backups where all for exactly the same LSN. As the LSN is only for InnoDB, I verified the table type for my test tables. And my test tables were in fact InnoDB. A "SHOW ENGINE INNODB STATUS\G" told me that the LSN was in fact increasing.

It turned out that XtraBackup was making backups of /var/lib/mysql instead of ~/sandboxes/msb_5_5_22-mariadb/data/. Adding "--defaults-file=~/sandboxes/msb_5_5_22-mariadb/my.sandbox.cnf" to the innobackupex command did correct this.

After specifying the correct config file I did try to make backups under load again. It failed due to the logfiles being too small. So I stopped the database, removed the ib_logfile's and started the database with a larger InnoDB logfile size.

Then It all worked flawlessly!

So you should make sure that your backup completes without errors AND that your backups is from the right database. Of course testing restores regularly would also detect this.