表迁移工具的选型-复制ibd的方法

1.1. 场景

有的时候开放人员自己的库需要帮忙导一些数据，但是表的数据量又很大。虽然说使用mysqldump或mysqlpump也可以导。但是这耗时需要比较久。

记得之前建议开放人员可以直接使用navicat去抽取测试库的数据。但是发现但遇到大表的时候，发现navicat会卡死。

1.2. 使用方法

使用拷贝*.ibd的方法。

注意：使用这种方法会锁表。因为是测试库，对服务器有一些影响还是可以接受的。

1.3. 先决条件

前提必须开启innodb_file_per_table选项，并且使用InnoDB存储引擎:

1	set global innodb_file_per_table = 1 ;

1.4. 制造大表

下面我们制造表数据，下面模拟的数据比较小，主要是为了节省时间:

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

USE test ; — 创建表 t1 DROP TABLE IF EXISTS t1 ; CREATE TABLE t1 (    id BIGINT unsigned NOT NULL AUTO_INCREMENT ,    x VARCHAR ( 500 ) NOT NULL ,    y VARCHAR ( 500 ) NOT NULL ,    PRIMARY KEY ( id ) ) ; — 创建添加数据存储过程 DROP PROCEDURE insert_batch ; DELIMITER // CREATE PROCEDURE insert_batch ( ) begin    DECLARE num INT ;    SET num = 1 ;    WHILE num < 1000000 DO      IF ( num % 10000 = 0 ) THEN        COMMIT ;      END IF ;      INSERT INTO t1 VALUES ( NULL , REPEAT ( ‘X’ , 500 ) , REPEAT ( ‘Y’ , 500 ) ) ;      SET num = num + 1 ;    END WHILE ;    COMMIT ; END // DELIMITER ; — 添加数据 CALL insert_batch ( ) ; DROP PROCEDURE insert_batch ;

查看数据大小情况（磁盘上的数据大小）

1 2 3

ls – lh / u02 / data / test / t1 . * – rw – r — — – 1 mysql mysql 8.5K Mar 10 13 : 54 / u02 / data / test / t1 . frm – rw – r — — – 1 mysql mysql 1.2G Mar 10 14 : 20 / u02 / data / test / t1 . ibd

查看真实的大小情况

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

select count ( * ) from t1 ; + — — — — — + | count ( * ) | + — — — — — + |    999999 | + — — — — — + SELECT table_name ,    data_length / 1024 / 1024 AS ‘data_length(MB)’ ,    index_length / 1024 / 1024 AS ‘index_length(MB)’ ,    ( data_length + index_length ) / 1024 / 1024 AS ‘total(MB)’ FROM information_schema . tables WHERE table_schema = ‘test’    AND table_name = ‘t1’ ; + — — — — — — + — — — — — — — — – + — — — — — — — — — + — — — — — — — – + | table_name | data_length ( MB ) | index_length ( MB ) | total ( MB )      | + — — — — — — + — — — — — — — — – + — — — — — — — — — + — — — — — — — – + | t1          |    1048.00000000 |        0.00000000 | 1048.00000000 | + — — — — — — + — — — — — — — — – + — — — — — — — — — + — — — — — — — – +

从上面可以看出在磁盘上的数据大小是1.2G，而实际的大小才1048MB(估计值)，实际情况会比上面的数据大很多。

1.5. 将test.t1迁移到test2.t1中

1、test2库中创建和test.t1相同的表结构

1 2 3

CREATE DATABASE test2 ; USE test2 ; CREATE TABLE t1 LIKE test . t1 ;

2、废弃test2.t1表空间，等待新表空间导入

1 2	USE test2 ; ALTER TABLE t1 DISCARD TABLESPACE ;

3、锁表导出test.t1表元数据

1 2	USE test ; FLUSH TABLES t1 FOR EXPORT ;

4、将test.t1表*.ibd和*.cfg文件拷贝到test2库中

1 2 3 4 5 6

cp t1 . cfg / u02 / data / test2 / cp t1 . ibd / u02 / data / test2 / ll – h / u02 / data / test2 / – rw – r — — – 1 root   root    424 Mar 10 14 : 41 t1 . cfg – rw – r — — – 1 mysql mysql 8.5K Mar 10 14 : 33 t1 . frm – rw – r — — – 1 root   root    1.2G Mar 10 14 : 41 t1 . ibd

5、释放test.t1锁

1 2	USE test ; UNLOCK TABLES ;

6、test2导入t1数据

1 2 3

chown – R mysql : mysql / u02 / data / test2 USE test2 ; ALTER TABLE t1 IMPORT TABLESPACE ;

7、查看test2.t1数据

1 2 3 4 5 6 7

USE test2 ; SELECT COUNT ( * ) FROM t1 ; + — — — — — + | COUNT ( * ) | + — — — — — + |    999999 | + — — — — — +

1.6. 总结

如果是数据量比较大，并且可以忍受对服务器有一点影响的，就可以考虑使用这种方法来处理。并且需要注意的是最好迁移的数据库版本是相同的。

 

 

 

文章转载来自：trustauth.cn