大表快速迁移
参考资料:
https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/#copying-transportable-tablespaces
需求描述: mysql在不同数据之间迁移表
源数据库: 10.100.19.214 testdb214.t2
目标数据库:10.100.19.215 testdb215.t2
# 该方法先决条件 mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec)
第一步:10.100.19.214锁表 mysql> FLUSH TABLES t2 FOR EXPORT; Query OK, 0 rows affected (0.00 sec)
# 把数据拷贝一份 # ll -th /data/mysql/mysql_3306/data/testdb214/* -th -rw-r----- 1 mysql mysql 948 Feb 2 18:02 /data/mysql/mysql_3306/data/testdb214/t2.cfg -rw-r----- 1 mysql mysql 14G Feb 2 18:01 /data/mysql/mysql_3306/data/testdb214/t2.ibd -rw-r----- 1 mysql mysql 144K Feb 2 10:16 /data/mysql/mysql_3306/data/testdb214/info_area.ibd root@k8s-02-19-214-bigdata-test /data/mysql/mysql_3306/data/testdb214 eth0 10.100.19.214 # cp /data/mysql/mysql_3306/data/testdb214/t2.ibd /tmp/t2.ibd # 10.100.19.214解锁 mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
第二步:在目标库中创建同样的表结构 CREATE TABLE `t2` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID', `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称', `rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID', `pid` int unsigned NOT NULL DEFAULT '0' COMMENT '父ID', `level` int NOT NULL DEFAULT '0' COMMENT '类别,1、省份 2、市 3、区 4、县', PRIMARY KEY (`id`), KEY `idx_pid` (`rel_id`) ) ENGINE=InnoDB AUTO_INCREMENT=166337815 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='地区表'; mysql> ALTER TABLE t2 DISCARD TABLESPACE; Query OK, 0 rows affected (0.08 sec)
# 从源数据服务器上传输数据到目标机器相应目录 # scp /tmp/t2.ibd m@10.100.19.215:/home/m/ # mv /home/m/t2.ibd /data/mysql/mysql_3306/data/testdb215/ # chown mysql.mysql /data/mysql/mysql_3306/data/testdb215/t2.ibd mysql> ALTER TABLE t2 IMPORT TABLESPACE; Query OK, 0 rows affected, 1 warning (2 min 35.91 sec) mysql> select * from t2 limit 5; +-------+-----------------------+-----------------+-------+-------+ | id | name | rel_id | pid | level | +-------+-----------------------+-----------------+-------+-------+ | 10000 | 北京 | 10000 | 10000 | 1 | | 10001 | 三环以内 | 100001000110001 | 10001 | 3 | | 10002 | 三环到四环之间 | 100001000110002 | 10002 | 2 | | 10004 | 三环到四环之间 | 100001000110004 | 10004 | 4 | | 10005 | 北京 | 10000 | 10000 | 1 | +-------+-----------------------+-----------------+-------+-------+ 5 rows in set (0.03 sec) # 从上面可以看出,目前已经可以正常使用了。