3

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 ?

  • You can minimize the risk as of first question if you gracefully stop the server before loading it as this may avoid breakage. – Nawaz Sohail Nov 25 '14 at 15:45
  • But I mean If I replace the ib_logfile from the server A into the server B which has different databases. Even if the server is stopped, it will corrupt the other databases no ? – sf_tristanb Nov 25 '14 at 16:02
  • I don't think it's going to work. if ib_logfile does kill it, replacing ibdata1 will. (unless all existing tables are MyISAM). – Manny Calavera Nov 25 '14 at 19:53
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.