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

1.1. 场景

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

记得之前建议开放人员可以直接使用navicat去抽取测试库的数据。但是发现但遇到大表的时候，发现navicat会卡死。文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

1.2. 使用方法

使用拷贝*.ibd的方法。文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

注意：使用这种方法会锁表。因为是测试库，对服务器有一些影响还是可以接受的。文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

1.3. 先决条件

前提必须开启innodb_file_per_table选项，并且使用InnoDB存储引擎:文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

set global innodb_file_per_table = 1;

1.4. 制造大表

下面我们制造表数据，下面模拟的数据比较小，主要是为了节省时间:文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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;

查看数据大小情况（磁盘上的数据大小）文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_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

查看真实的大小情况文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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(估计值)，实际情况会比上面的数据大很多。文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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

1、test2库中创建和test.t1相同的表结构文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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

2、废弃test2.t1表空间，等待新表空间导入文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

USE test2;
ALTER TABLE t1 DISCARD TABLESPACE;

3、锁表导出test.t1表元数据文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

USE test;
FLUSH TABLES t1 FOR EXPORT;

4、将test.t1表*.ibd和*.cfg文件拷贝到test2库中文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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锁文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

USE test;
UNLOCK TABLES;

6、test2导入t1数据文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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

7、查看test2.t1数据文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

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

1.6. 总结

如果是数据量比较大，并且可以忍受对服务器有一点影响的，就可以考虑使用这种方法来处理。并且需要注意的是最好迁移的数据库版本是相同的。文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

 文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

昵称：HH
QQ：275258836
ttlsa群交流沟通(QQ群②：6690706 QQ群③：168085569 QQ群④：415230207（新） 微信公众号：ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

感觉本文内容不错，读后有收获？文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/

逛逛衣服店，鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/migration_tools_selected_3/