Xtrabackutp restore on another server with another DB running on the same server

MySQL & MariaDB Percona XtraBackup
1

Hi Guys,

We have a task to migrate a database to a new hardware where there is a active database on it already.

  1. We have xtrabackup taking daily backups.
  2. We have option to start/stop DB on the new hardware to complete the restoration.

I had followed the following process to test the migration ( Apparently it did not succeed )

  1. Copy the .qp file to new hardware, uncompressed it .
  2. Stop the mysql on the new server
  3. Copy the data file of the database to be migrated to the datadir of the new server.
  4. Start the mysql.

After this process I can see the database / tables but when try to access the tables I get an error

mysql> desc ;
ERROR 1146 (42S02): Table '" doesn’t exist
mysql>

I am looking at mysqldump option but that is taking way long time which is not good as downtime cannot be that long.

Any ideas on how I can use the existing xtrabackup file to restore it on existing DB ???

Any suggestions would be appreciated.

Thanks
-Rajesh

2

In order to restore database/tables on active database. You need to use partial backups i.e. backup selected database/tables first. You need to use --include, --tables-file or --databases option to backup selected database/tables.
In order to restore partial backup couple of prerequisites you need to meet. Tables should be created with innodb_file_per_table option enabled. i.e. each table data should reside on it’s own tablespace (.ibd) file and enable innodb_expand_import or innodb_import_table_from_xtrabackup depends on percona server version for the the last option. Further, those options need to be enable where you want to import the schema/tables.
After that you need to prepare backup with --export option, then create tables manually where you need to import, discard the existing tablespace, Copy the .exp & .ibd files to the datadir and finally import the tablespace. Also, you can check this thread for help on it [url]http://www.percona.com/forums/questions-discussions/percona-xtrabackup/8315-restore-one-table-from-xtrabackup-s-full-backup[/url] and check the Xtrabackup partial backup documentation.
I recommend to test it before applying it to production and create backup of active database.

Hope that helps.

3

Thanks for the response Irfan, will be testing your suggestion and get back to you…

-Rajesh