EmergencyEMERGENCY? Get 24/7 Help Now!

Impossible – possible, moving InnoDB tables between servers

 | June 8, 2009 |  Posted In: Percona Software


This is probably the feature I missed most from early days when I started to use InnoDB instead of MyISAM. Since that I figured out how to survive without it, but this is first question I hear from customers who migrated from MyISAM to InnoDB – can I just copy .ibd files from one server to another and answer “use mysqldump” is quite disappointed.
Jokes aside, I see real needs in this:
– when we need to restore only single table from backup (sometimes developers kill only single table, not whole database 🙂 )
– to copy single table from production to QA environment. It may sound not so important, but I see needs in this quite often. QA boxes may have their own setup, not so powerful and with not enough space, but QA still needs to have some tables in fresh status.
– resharding databases, moving some shards from one server to another.

So long story short end – we made new mode for XtraBackup, now it can copy and prepare InnoDB/XtraDB tables, which later can be imported into XtraDB.

To be objective the process is still not so easy as with MyISAM tables when you just copy table.frm, table.MYD, table.MYI files, but so nice here – just can copy InnoDB tables in fully non-blocking mode, you do not need to shutdown neither source nor destination server (if destination server runs XtraDB).

In short, process looks like (ah, yes you also need innodb_file_table mode, which ones love, and ones hate)
1) do backup of specific tables on sources server with xtrabackup (server can run InnoDB or XtraDB, it does not matter)
2) do prepare of tables with –export options
3) create table on destination server with exactly the same CREATE TABLE statement as on source. (on destination you should have XtraDB with innodb_expand_import extension and with innodb_expand_import=1 setting)
4) run alter table IMPORTEDTABLE discard tablespace;
5) copy .exp and .ibd files to destination server
6) run alter table IMPORTEDTABLE import tablespace;
7) enjoy progress in error.log like:
InnoDB: import: extended import of test/img_out59
InnoDB: import: 3 indexes are detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.

Some more information available on

If you want some internals: I expected the biggest problem with implementation would be merging insert buffer or handling undo records, stored in ibdata file, but it was easy – you just need to shutdown instance with innodb_fast_shutdown=1 (done in xtrabackup –prepare –export call). The more complex problem was that InnoDB stores pointers on root leafs of secondary indexes also in system tablespace. And that’s why we maintain .exp files – to keep information from system tablespace that related to exported table.

Both xtrabackup –export mode and XtraDB innodb_expand_import available only in source code for now, you can get them from Launchpad projects https://launchpad.net/percona-xtradb and https://launchpad.net/percona-xtrabackup. Binary releases will be ready as soon as we have done with testing of this feature. You are welcome to try!

Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.


  • So, I’m reading this. Does this mean that the eventual table it ends up as is an XtraDB table, not an InnoDB table?

  • Singer,

    For now XtraDB is almost fully compatible with InnoDB table format and backward. So you can import table to XtraDB and after that start InnoDB instance on database.

  • Can you copy innodb tables across schemas on the same db?

    Ex: Copy schema1.test_table to schema2.test_table

  • Vadim,

    When some feature is comming so one can do EXPORT TABLESPACE or something similar from MySQL/MariaDB directly ?
    I think this will make such feature much more useful

  • Hi Vadim,

    Thank you for this excellent post and some really exciting work with XtraDB.

    Would there be any way to achieve the same transfer of a database from one server to another without making an explicit duplicate (backup) copy of the InnoDB data files on the first server in the process?

    I’m considering this in the context of a cluster of machines running XtraDB where we want to “stream” backups of databases from one node (A) to another (B) by using ZFS snapshots rather than query-based MySQL replication. I’d be happy with a short write-block period on A during which the .ibd and .ext files are prepared *in-situ* (so we don’t need to wait while we copy gigabytes of data) and then a snapshot of them is made, at which point the database can un-block and the snapshot can be transmitted to B. Then upon receipt of the snapshot B can do steps 3-7 to get a consistent backup.

    I guess my question is: can XtraBackup be configured / easily patched to do its –export –prepare magic on live XtraDB files rather than a backup copy of them, on the assumption that it’s okay to initiate a write lock on that database for the duration of its action? Also, could XtraDB cope with these files being modified while it’s running?

    I’d be very interested to hear any thoughts you have on this.

    Best Regards,
    Luke Marsden
    Hybrid Logic Ltd.

    www.hybrid-cluster.com – Next-generation cloud web hosting

  • Luke,

    I think I got you idea, and it should be possible to implement, but it is not easy ..

    You may contact us https://www.percona.com/contacts.html
    for discussion how we can proceed.

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.