The feature I announced some time ago http://www.mysqlperformanceblog.com/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:
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 | xtrabackup --defaults-file=/etc/my.reg.cnf --backup --tables=tpcc.order_line* --target-dir=/data/vadim/mysql/export/ xtrabackup: tables regcomp(): Success xtrabackup Ver 0.8.1rc Rev 78 for 5.0.83 unknown-linux-gnu (x86_64) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /bench/mysqldata xtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 536870912 xtrabackup: use O_DIRECT >> log scanned up to (3 767617628) Copying ./ibdata1 to /data/vadim/mysql/export//ibdata1 >> log scanned up to (3 769009554) ...done Copying ./mysql/ibbackup_binlog_marker.ibd is skipped. Copying ./tpcc/stock.ibd is skipped. Copying ./tpcc/warehouse.ibd is skipped. Copying ./tpcc/new_orders.ibd is skipped. Copying ./tpcc/order_line.ibd to /data/vadim/mysql/export//tpcc/order_line.ibd >> log scanned up to (3 770393658) ... >> log scanned up to (3 844882683) ...done Copying ./tpcc/district.ibd is skipped. Copying ./tpcc/orders.ibd is skipped. Copying ./tpcc/item.ibd is skipped. Copying ./tpcc/customer.ibd is skipped. Copying ./tpcc/history.ibd is skipped. xtrabackup: The latest check point (for incremental): '3:763362037' >> log scanned up to (3 845737724) xtrabackup: Stopping log copying thread. xtrabackup: Transaction log of lsn (3 763355707) to (3 845737724) was copied. |
Now in
1 | /data/vadim/mysql/export |
we have backup but only with
1 | ibdata1 |
and
1 | order_line.ibd |
files
Second step is to prepare backup, but with special option “export”
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 | xtrabackup --defaults-file=/etc/my.reg.cnf --prepare --export --use-memory=8G --target-dir=/data/vadim/mysql/export/ xtrabackup Ver 0.8.1rc Rev 78 for 5.0.83 unknown-linux-gnu (x86_64) xtrabackup: cd to /data/vadim/mysql/export/ xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=92684288, start_lsn=(3 763355707) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 92684288 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 8589934592 bytes for buffer pool (set by --use-memory parameter) InnoDB: Log scan progressed past the checkpoint lsn 3 763355707 090730 23:22:43 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Doing recovery: scanned up to log sequence number 3 768598528 (6 %) .... InnoDB: Doing recovery: scanned up to log sequence number 3 845737724 (99 %) InnoDB: 12 transaction(s) which must be rolled back or cleaned up InnoDB: in total 107 row operations to undo InnoDB: Trx id counter is 0 1560320 .... id 0 1559932, 13 rows to undo InnoDB: Rolling back of trx id 0 1559932 completed 090730 23:23:35 InnoDB: Rolling back trx with id 0 1559890, 30 rows to undo InnoDB: Rolling back of trx id 0 1559890 completed 090730 23:23:35 InnoDB: Rollback of non-prepared transactions completed 090730 23:25:32 InnoDB: Shutdown completed; log sequence number 3 852825486 |
When it’s done we have two files
1 | order_line.ibd |
and
1 | order_line.exp |
in directory
1 | /data/vadim/mysql/export/tpcc |
. 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
1 | set global innodb_expand_import=1; |
to put XtraDB into extended import mode, and now we need to create empty table with the same table definition as on old servers:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE DATABASE "testimport"; USE "testimport" CREATE TABLE `order_line` ( `ol_o_id` int(11) NOT NULL, `ol_d_id` tinyint(4) NOT NULL, `ol_w_id` smallint(6) NOT NULL, `ol_number` tinyint(4) NOT NULL, `ol_i_id` int(11) default NULL, `ol_supply_w_id` smallint(6) default NULL, `ol_delivery_d` datetime default NULL, `ol_quantity` tinyint(4) default NULL, `ol_amount` decimal(6,2) default NULL, `ol_dist_info` char(24) default NULL, PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`), KEY `fkey_order_line_2` (`ol_supply_w_id`,`ol_i_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Next step is to remove created .ibd file, it’s done by command:
1 | alter table order_line discard tablespace; |
And now you copy both
1 | order_line.ibd |
and
1 | order_line.exp |
to
1 | MysqlDataDir/testimport |
dir. When it’s done, final import command:
1 | alter table order_line import tablespace; |
now some magic happens and you can see progress of import in error.log
1 2 3 | InnoDB: import: extended import of testexport/order_line is started. InnoDB: import: 2 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. |
When finished quick check
1 2 3 4 5 6 7 | mysql> select count(*) from order_line; +----------+ | count(*) | +----------+ | 32093604 | +----------+ 1 row in set (3 min 29.32 sec) |
New table has been imported!
About Vadim Tkachenko
that’s awesome!
Great feature !
Can we use it with an official InnoDB plugin anytime soon ?
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:
http://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)
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.
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.
@Dean Herbert
you are right ,i shutdown mysql ,and restart it ,the missing data appear !!
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.