大表快速迁移

大表快速迁移

参考资料：
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)
# 从上面可以看出，目前已经可以正常使用了。