Mysql – percona xtrabackup to backup thesql database

backupMySQLpercona

I'm trying to use percona xtrabackup to backup a mysql database. in the restoring the database according to the documentation:

rsync -avrP /data/backup/ /var/lib/mysql/

this will copy the ibdata1 as well.
what if I have want to restore the backup into an existing mysql instance with some some existing databases? would this corrupt my other databases? clearly this will overwrite existing ibdata1.

Best Answer

It depends on your MySQL version that you're restoring. The short answer is: for anything under MySQL 5.6, yeah that would corrupt your existing dataset.

However, if you're using 5.6, there is a new feature to export/import individual tablespaces and Percona Xtrabackup supports it.

Some important caveats of the importable tablespace implementation of MySQL 5.6 are as follows:

  • You have to have innodb_file_per_table enabled.
  • DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported.
  • DISCARD TABLESPACE has some limitations regarding Foreign Keys

For other limitations, see the Tablespace Copying Limitations and Usage Notes section of the documentation.

Related Question