Mysql – use xtrabackup to import a database without altering others

backupinnodbMySQLxtrabackup

Context:

Each time a demo branch is built, I want to download a copy of the prod database and setup it onto the demo server. This way under the MySQL I have X database corresponding to X demos.

I achieved it using the mysql_import but it takes 3 ~ 5 minutes when importing the DB. So to speed up I wanna use Xtrabackup.

Problem:

The problem is that during the full backup, xtrabackup copies all the necessary files and also the ib_logfilefiles.

So let's say I want to import the database into database named demo_1874.

  1. I do a full backup of the database, then I run --prepare twice on the same server, and I compress the result.
  2. Download on the other server, uncompress all the backup files
  3. Paste them under /var/lib/mysql which will override the other files (ib_logfile)
  4. Rename the folder original_db to demo_1874

Question

  • By copying files like ib_logfile, will it break all the other databases I have on this MySQL server (which I don't want this to happen) ?

  • What are the other options using Xtrabackup to import a database among others without altering the others ?

Best Answer

What you need is partial backups. innodb_file_per_table=ON is a prerequisite to make it work.

In your case procedure would be like following:

Take a partial backup from the production server

innobackupex --databases="mydatabase" /path/to/backup

On the destination server prepare the backup copy for export:

innobackupex --apply-log --export /path/to/backup

For each table repeat these steps:

create a new table with the same structure (get it from the production server with mysqldump -t production or SHOW CREATE TABLE t)

demo_1874> CREATE TABLE t (...) ENGINE=InnoDB;

Discard tablespace that InnoDB has

demo_1874> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE; created.

Copy t.* files from partial backup to /var/lib/mysql/demo_1874, chown mysql:mysql them, and import the tablespace from the partial backup

demo_1874> ALTER TABLE t IMPORT TABLESPACE;

Consult http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html for more details.

Related Question