Restoring a single database using innobackupex

MySQL & MariaDB Percona XtraBackup
1

Hi,

I think I am trying to do quite a simple thing, but I am hitting a block. I want to take a backup of a single database schema from a server and apply it in another instance of (Percona) MySQL 5.6.

I have followed carefully the instructions here: [url]http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html#restoring-partial-backups[/url]

The part that does not work for me is:
“It can also be done by copying back the prepared backup to a “clean” datadir (in that case, make sure to include the mysql database). System database can be created with:
$ sudo mysql_install_db --user=mysql”

I am able to copy back OK, but I am unable to create the system databases properly and start the MySQL service. What does “make sure to include the mysql database” actually mean? Just take a copy from the remoter server or move the local database out of the datadir, copy back then move it back in again? Everything I have tried has not worked, the MySQL service will not start and the logs state:

: #007/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
/etc/init.d/mysql[11410]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’
/etc/init.d/mysql[11410]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!

The restoring individual tables method is not really open to me as the schema has nearly 300 tables.

This is a trivial operation using mysqldump but it is proving hard to get working using innobackupex.

Any advice welcome.

Thanks,
Stephen

2

Hi LostInTheWoods;

Restoring a partial backup to a “clean” datadir, i.e. a new DB server, is easier. Restoring a partial backup to an existing DB, is where you then have to import it table by table as you mentioned, which is not so great (though you can script it to be better).

For what you are looking for, use the --databases=“mysql performance_schema yourdatabase” when you do the backup. Then run the apply log step as you normally would, then move the prepared backup to the empty datadir, make sure the ownership on the directories and files is correct, and then start MySQL.

-Scott

3

Scott,

Thanks for providing this information as it has perplexed me too. So is it really as simple as turning off the MySQL service, creating a new folder w/ the backup contents in it (and chown mysql), and then starting MySQL?

4

Hi randygrolemund;

If you get the mysql and performance_schema databases along with the other database(s) you want, then yes it should work. When you do the initial apply-log step it will complain about the missing tables from any excluded databases, but then it should work as any other backup after that.

-Scott

5

Scott,

Thanks for your reply, before embarking on writing a table by table import script I will check the requirements. It might well be that restoring a partial backup in to a clean datadir like you suggested might be sufficient. It would be amazing if this was managed by innobackupex though.

I will try this out soon.

Thanks,
Stephen