Trying XtraBackup, but no success.

MySQL & MariaDB Percona XtraBackup
1

I’m testing out XtraBackup for use on a fairly large (100G+) database, but so far I have not been able to even produce good results on a small one. I’m hoping someone can tell me what I might be doing wrong.

I’m running MySQL 5.1.49 on CentOS 5. I installed XtraBackup 1.5.1 via the Yum repository. I ran a full backup using the innobackupex command:

innobackupex /opt/backups/mysql_backups

This seemed to work fine. It created an appropriately timestamped directory name and let me know that everything “completed OK”. So, next I prepared the backup:

innobackupex --apply-log /opt/backups/mysql_backups/2011…

Again, this appeared to work fine. Now is where the trouble begins. I shut down MySQL. Then, when I run the command to restore the backup:

innobackupex --copy-back /opt/backups/mysql_backups/2011…

I get an error “mkdir: cannot create directory ‘/var/lib/mysql/innodb’: File exists”. Of course, it exists! So, I also tried just moving the old “mysql” datadir away, creating a new, empty directory. Innobackupex isn’t happy with that, either, because “Original InnoDB data directory ‘/var/lib/mysql/innodb’ does not exist!”

Well, either it wants the directory or it doesn’t, but I can’t figure out which.

I was only able to restore the backup by copying the prepared files into place then issuing a “chown” command to make sure that the files were all owned by ‘mysql’ before restarting the service.

I wanted to ask about these errors and my final solution, though, because it does not match with the documented way of performing a backup/restore.

Thanks,
Dave

2

As a follow-up, I re-ran my backup and tried restoring by copying the files just like I had previously. This time, I have a severe problem. I had created a table called “test” in a database and was entering data into it while the backup was proceeding. I can see the table in the database, but when I try to select from it, I get the error “Table ‘test.test’ doesn’t exist”. Something is definitely corrupted.

3

Another follow-up. I discovered that version 1.6.3 was available, just not in the Yum repo. Same problems with it, though.

So, does XtraBackup actually work? Sorry to sound so cynical, but I have yet to get a single successful test on even a tiny database.