Copying InnoDB tables between servers

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 ).
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:

PLAIN TEXT

CODE:

1. xtrabackup  --defaults-file=/etc/my.reg.cnf --backup --tables=tpcc.order_line* --target-dir=/data/vadim/mysql/export/
2.  
3. xtrabackup: tables regcomp(): Success
4. xtrabackup  Ver 0.8.1rc Rev 78 for 5.0.83 unknown-linux-gnu (x86_64)
5. xtrabackup: uses posix_fadvise().
6. xtrabackup: cd to /bench/mysqldata
7. xtrabackup: Target instance is assumed as followings.
8. xtrabackup:   innodb_data_home_dir = ./
9. xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
10. xtrabackup:   innodb_log_group_home_dir = ./
11. xtrabackup:   innodb_log_files_in_group = 3
12. xtrabackup:   innodb_log_file_size = 536870912
13. xtrabackup: use O_DIRECT
14. >> log scanned up to (3 767617628)
15. Copying ./ibdata1
16.      to /data/vadim/mysql/export//ibdata1
17. >> log scanned up to (3 769009554)
18.         ...done
19. Copying ./mysql/ibbackup_binlog_marker.ibd is skipped.
20. Copying ./tpcc/stock.ibd is skipped.
21. Copying ./tpcc/warehouse.ibd is skipped.
22. Copying ./tpcc/new_orders.ibd is skipped.
23. Copying ./tpcc/order_line.ibd
24.      to /data/vadim/mysql/export//tpcc/order_line.ibd
25. >> log scanned up to (3 770393658)
26. ...
27. >> log scanned up to (3 844882683)
28.         ...done
29. Copying ./tpcc/district.ibd is skipped.
30. Copying ./tpcc/orders.ibd is skipped.
31. Copying ./tpcc/item.ibd is skipped.
32. Copying ./tpcc/customer.ibd is skipped.
33. Copying ./tpcc/history.ibd is skipped.
34. xtrabackup: The latest check point (for incremental): '3:763362037'
35. >> log scanned up to (3 845737724)
36. xtrabackup: Stopping log copying thread.
37. xtrabackup: Transaction log of lsn (3 763355707) to (3 845737724) was copied.

Now in /data/vadim/mysql/export we have backup but only with ibdata1 and order_line.ibd files

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

PLAIN TEXT

CODE:

1. xtrabackup  --defaults-file=/etc/my.reg.cnf --prepare --export --use-memory=8G --target-dir=/data/vadim/mysql/export/
2. xtrabackup  Ver 0.8.1rc Rev 78 for 5.0.83 unknown-linux-gnu (x86_64)
3. xtrabackup: cd to /data/vadim/mysql/export/
4. xtrabackup: This target seems to be not prepared yet.
5. xtrabackup: xtrabackup_logfile detected: size=92684288, start_lsn=(3 763355707)
6. xtrabackup: Temporary instance for recovery is set as followings.
7. xtrabackup:   innodb_data_home_dir = ./
8. xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
9. xtrabackup:   innodb_log_group_home_dir = ./
10. xtrabackup:   innodb_log_files_in_group = 1
11. xtrabackup:   innodb_log_file_size = 92684288
12. xtrabackup: Starting InnoDB instance for recovery.
13. xtrabackup: Using 8589934592 bytes for buffer pool (set by --use-memory parameter)
14. InnoDB: Log scan progressed past the checkpoint lsn 3 763355707
15. 090730 23:22:43  InnoDB: Database was not shut down normally!
16. InnoDB: Starting crash recovery.
17. InnoDB: Reading tablespace information from the .ibd files...
18. InnoDB: Doing recovery: scanned up to log sequence number 3 768598528 (6 %)
19. ....
20. InnoDB: Doing recovery: scanned up to log sequence number 3 845737724 (99 %)
21. InnoDB: 12 transaction(s) which must be rolled back or cleaned up
22. InnoDB: in total 107 row operations to undo
23. InnoDB: Trx id counter is 0 1560320
24. ....
25.  
26. id 0 1559932, 13 rows to undo
27.  
28. InnoDB: Rolling back of trx id 0 1559932 completed
29. 090730 23:23:35  InnoDB: Rolling back trx with id 0 1559890, 30 rows to undo
30.  
31. InnoDB: Rolling back of trx id 0 1559890 completed
32. 090730 23:23:35  InnoDB: Rollback of non-prepared transactions completed
33. 090730 23:25:32  InnoDB: Shutdown completed; log sequence number 3 852825486

When it's done we have two files order_line.ibd and order_line.exp in directory /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 .

On new server we run
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:

PLAIN TEXT

CODE:

1. CREATE DATABASE "testimport";
2. USE "testimport"
3. CREATE TABLE `order_line` (
4.   `ol_o_id` int(11) NOT NULL,
5.   `ol_d_id` tinyint(4) NOT NULL,
6.   `ol_w_id` smallint(6) NOT NULL,
7.   `ol_number` tinyint(4) NOT NULL,
8.   `ol_i_id` int(11) default NULL,
9.   `ol_supply_w_id` smallint(6) default NULL,
10.   `ol_delivery_d` datetime default NULL,
11.   `ol_quantity` tinyint(4) default NULL,
12.   `ol_amount` decimal(6,2) default NULL,
13.   `ol_dist_info` char(24) default NULL,
14.   PRIMARY KEY  (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
15.   KEY `fkey_order_line_2` (`ol_supply_w_id`,`ol_i_id`)
16. ) ENGINE=InnoDB DEFAULT CHARSET=latin1

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

PLAIN TEXT

CODE:

1. alter table order_line discard tablespace;

And now you copy both order_line.ibd and order_line.exp to MysqlDataDir/testimport dir. When it's done, final import command:

PLAIN TEXT

CODE:

1. alter table order_line import  tablespace;

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

PLAIN TEXT

CODE:

1. InnoDB: import: extended import of testexport/order_line is started.
2. InnoDB: import: 2 indexes are detected.
3. 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

PLAIN TEXT

CODE:

1. mysql> select count(*) from order_line;
2. +----------+
3. | count(*) |
4. +----------+
5. | 32093604 |
6. +----------+
7. 1 row in set (3 min 29.32 sec)