We have a production MySQL installation with about 20 databases containing 100+ tables each. The sizes of the databases vary between 5 and 30 GB of tablespace. We have to copy individual production databases to test servers regularly, which we have been doing with mysqldump so far, but that takes several days in some cases. I read a lot of documentation on XtraBackup, innobackupex, transportable tablespaces, etc., but still haven’t found a suitable solution. Our main concern is restore time, not backup time. The production system runs a dedicated backup slave that can be taken off replication and even powered off if needed. I read in the manual (https://www.percona.com/doc/percona-xtrabackup/2.3/innobackupex/partial_backups_innobackupex.html) that XtraBackup has the --databases option, which we could use, but do I understand correctly that to restore the database(s), you have to do it individually for each table instead of using --copy-back? With the number of tables in our databases, I don’t think that’s feasible, particularly if we have to manually create tables on the target systems before restoring them from the backup. But on the other hand, transferring full backups of the entire set of databases to the test systems is not what is desired either; it has to happen independently. Any ideas?
Did you tried transportable tablespace feature of Oracle MySQL/Percona Server 5.6 ? It has capability of freely copying table.ibd file between servers. Check my blog post https://www.percona.com/blog/2014/12/09/mysql-5-6-transportable-tablespaces-best-practices/
Regarding, you question yes you can’t use --copy-back option for partial database backup to restore. I have process defined for this too to restore individual database tables in above blogpost.