Not the answer you need?
Register and ask your own question!

Binary Copy of InnoDB Files Sufficient?

conixoconixo EntrantInactive User Role Beginner
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,

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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:

    https://www.percona.com/doc/percona-xtrabackup/2.1/howtos/recipes_ibkx_local.html

    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
  • conixoconixo Entrant Inactive User Role Beginner
    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!
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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:

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

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

    https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_individual_tables_ibk.html

    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.