Copying InnoDB tables between servers
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:
|
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!
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:
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.
@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.