A difficult XtraBackup restore

There was one MySQL server with a Adaptec Raid controller and 4 disks. One of the disks was having media errors and caused the whole SCSI bus to become unavailable.

This resulted in a corrupted InnoDB table.

Luckily we did have backups. A full backup and incrementals.

So to restore the backups I installed XtraBackup and MySQL 5.5 on another server.

Then the first step was to 'prepare' the backup. This worked okay for the full backup (redo only).

The second step to add the incremantals failed for the first incremental. This was easily resolved by specifying the full paths instead of relative paths.

Then the backup was fully prepared using the redo logs and undo logs.

As XtraBackup doesn't backup your my.cnf we copied the my.cnf from another server and adjusted it for this server. The my.cnf in your backup only contains everything needed for a restore, and some of those settings are Percona Server specific and will result in an error when used with MySQL.

So far everything went as expected.

Then we started MySQL with the newly restored backup by executing "service mysql start", this failed.

Then I tried to start it with a "mysqld_safe &", this worked. So I expected the init script to use wrong parameters. So I executed "bash -x /etc/init.d/mysql start" and expected the error to show, but MySQL started okay this time. Using "/etc/init.d/mysql stop" and then "/etc/init.d/mysql start" resulted in an error again.
Then I added some echo statements to the script to check which parameters it used for mysqld_safe. Then I tried to start mysqld_safe with the same parameters, this worked.

So mysqld_safe failed to start and didn't log anything to the error log when executed from the init script. It does work when called directly.

Then it hit me. The SELinux context for the MySQL files was wrong (ls -lZ).

This is something that must not be changed with chcon as that won't survive a relabel action. I changed the context for the datadir (not /var/lib/mysql in this case) and location for the socket and pid files with semanage. The I used restorecon to apply it to the files. And then MySQL started fine. This is explained in in detail in this blog post.

Conclusions:

• XtraBackup works great (and fast, disk I/O is quickly a bottleneck for restores)
• Use full paths instead of relative paths for XtraBackup
• Make sure to backup your my.cnf (or put in a CM tool like puppet)
• If you use SELinux, don't forget to make sure the file context is correct after a restore.

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.