Binary Copy of InnoDB Files Sufficient?

MySQL & MariaDB Other MySQL® Questions
1

I have two databases running based on mysql. One is prod and the other one is test/dev. From time-to-time I need to align these environments. Since the database is quite large, the export and import takes a while (the import takes around 60 minutes). Hence, can I stop the prod mysql then copy the relevant database InnoDB Files from prod to test/dev environment? Would this work? or are there any other dependencies which I need to consider?

Thanks!

Best regards,

2

Hi conixo;

Yes you could could do it that way, by coping over the entire data directory and ib_logfiles (if they are not in the data directory). Or you could use Xtrabackup to create a hot backup so you would not have to take your master host down. Having a backup is critical for disaster recovery anyway, so would be good to get that process down for multiple reasons:

[url]Percona XtraBackup

As for dependencies, you basically need the same things you would need on your master as far as all the MySQL related binaries. If you use the same version on both, then it is pretty straight forward. If you use a different version on the dev copy, then you would need to run mysql_upgrade at minimum (though if the version difference is different you probably want to make them the same for testing purposes, unless you are intentionally testing an upgrade).

-Scott

3

Hi Scott,

thanks for help.

I was looking for innobackupex, especially to use it for partial backup. However I read that the partial backups can only be restored on a percona mysql server. Do you have any experience with innobackupex partial backups?

Thanks!

4

Hi conixo;

To import the individual tables (partial backups), the target must be Percona Server with XtraDB or MySQL 5.6. However if you are already running 5.6, then there is a built-in process to copy individual tables already:

[url]https://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html[/url]

Otherwise you can use Xtrabackup if you are running Percona Server or MySQL 5.6:

[url]Percona XtraBackup

If you are not running Percona Server or MySQL 5.6, then the easiest method would be to use mysqldump to dump the individual table(s) and then restore them on the target. You could also use a parallel dump tool like mydumper to speed up the dump and myloader to speed up the import.

-Scott