EmergencyEMERGENCY? Get 24/7 Help Now!

Copying InnoDB tables between servers

 | July 31, 2009 |  Posted In: Percona Software

PREVIOUS POST
NEXT POST

The feature I announced some time ago https://www.percona.com/blog/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on percona.tv).
Let’s take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

To export table you need XtraBackup, and you can just specify table or table by mask:

Now in

we have backup but only with

and

files

Second step is to prepare backup, but with special option “export”

When it’s done we have two files

and

in directory

. ibd is regular InnoDB ® file, and exp is file with special export information. Both files should be copied to remote server.

Now on remote server you have to run MySQL server ® with XtraDB6 storage engine, you can take one of our binary builds with 5.1.36.

On new server we run

to put XtraDB into extended import mode, and now we need to create empty table with the same table definition as on old servers:

Next step is to remove created .ibd file, it’s done by command:

And now you copy both

and

to

dir. When it’s done, final import command:

now some magic happens and you can see progress of import in error.log

When finished quick check

New table has been imported!

PREVIOUS POST
NEXT POST
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.

11 Comments

  • Farid,

    You can export tables from InnoDB-plugin and from standard plugin already. It does not depend on InnoDB version.

    Only for Import you need XtraDB, it requires patching of InnoDB, so it is question to Oracle/InnoDB team if they want to include it. You may ask them on their official forums.

  • I just installed percona server for the 1st time on a Debian (via apt-get install percona-server-server)
    Once mysqld is running and I login via the mysql client tool,
    I get the following error when setting the “innodb_expand_import” variable.
    “Unknown system variable ‘innodb_expand_import'”

    Is the XtraDB plugin installed and loaded by default?
    How can I see my MySQL software is actually the one patched by percona?

  • I get the same error Unknown system variable ‘innodb_expand_import’. I wonder if the name has changed? Their docs still mention it though – frustrating!

  • Just thought I’d point out that the variable has been renamed in 5.5.10 as per:
    https://www.percona.com/doc/percona-server/5.5/release-notes/Percona-Server-5.5.10-20.1.html#5.5.10-20.1

    “Old system variable innodb_expand_import_page was renamed to innodb_import_table_from_xtrabackup. (Yasufumi Kinoshita)”

  • i try it on percona server 5.5.20-55,but failed,here is my process:

    [redacted — this question belongs on a forum, not in this blog’s comments. – BPS]

    InnoDB: Progress in %: 93 95 96 98 100 done.

    but when i check the table t1 i find nothing !! why??
    import-server> select * from t1;
    Empty set (0.00 sec)

    • Justlooks, please do not use this blog as a forum. Use forum.percona.com for that. I’m going to edit your comment to make it briefer because a huge comment full of error log text is not a good experience for other readers.

  • in reply to the previous comment: is there anything earlier in the error log about mismatching log sequence numbers? i found i had to shutdown mysqld and purge existing log files to make my tables contain data, as their log sequences were too far in the future.

  • Can this be used with normal *.idb files that are not generated by xtrabackup? We recently had a hardware failure that severely corrupted our ibdata1 file; so I had to discard it along with ib_logfile0 and ib_logfile1. The only stuff left to us are the *.frm and *.ibd file of each table.

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.