mysql快速的复制迁移一张超级大表
所谓可传输表空间就是将一个大的innodb表从一个实例传输到另一个实例的过程，或者相同实例不同库之间进行表的拷贝的过程。表的拷贝或者复制有很多种方法，比如通过物理备份或者逻辑备份来实现，在5.6版本以后MySQL可以利用可传输表空间的这个特性来快速实现单个表的拷贝和复制，类似Oracle数据库的TTS功能。
可传输表空间使用场景
1. 生产环境单表很大，有需求对该表进行复杂查询而担心影响生产环境的负载的情况。
2. 修复从库数据表不一致的情况（复制表数据库到从库）。
3. 从一个备份的环境还原数据的情况
4. 一种更快速的方式重建表（替代逻辑导出导入，避免重新插入数据，重新建索引）的情况
可传输表空间使用细节举例:
1. 在数据库中建立测试表，并插入数据
root@127.0.0.1:pdb 03:23:59 > use testdb
Database changed
root@127.0.0.1:testdb 03:27:14 >show tables;
Empty set (0.00 sec)
## 建立测试表 t_product
root@127.0.0.1:testdb 03:27:17 >create table t_product(id int not null primary key);
Query OK, 0 rows affected (0.01 sec)
## 建立存储过程：插入10万条数据
root@127.0.0.1:testdb 04:09:10 >DELIMITER $
root@127.0.0.1:testdb 04:09:18 >CREATE PROCEDURE proc_initData()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i<=100000 DO
-> INSERT INTO t_product(id) VALUES(i);
-> SET i = i+1;
-> END WHILE;
-> END $
Query OK, 0 rows affected (0.01 sec)
root@127.0.0.1:testdb 04:09:18 >DELIMITER ;
root@127.0.0.1:testdb 04:09:20 >
root@127.0.0.1:testdb 03:30:01 >CALL proc_initData();
Query OK, 1 row affected (1 min 19.95 sec)
## 表数据准备完成
root@127.0.0.1:testdb 04:09:20 >select count(*) from t_product;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)
## 展示建表语句
root@127.0.0.1:testdb 04:12:06 >show create table t_product;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t_product | CREATE TABLE `t_product` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2. 在目标端新建一张空表，直接可以使用之前的建表语句进行建表,建表后进行discard操作。
## 当前所在数据库为pdb 这里方便测试，直接在同一实例上不同数据库之间进行表传输
root@127.0.0.1:pdb 04:25:06 >select database();
+------------+
| database() |
+------------+
| pdb |
+------------+
1 row in set (0.01 sec)
## 建一张与源端一模一样的表t_product
root@127.0.0.1:pdb 04:25:38 >CREATE TABLE `t_product` (
-> `id` int NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1:pdb 04:26:26 >show tables like '%product%';
+---------------------------+
| Tables_in_pdb (%product%) |
+---------------------------+
| t_product |
+---------------------------+
1 row in set (0.01 sec)
## discard掉.idb文件
root@127.0.0.1:pdb 04:30:51 >show global variables like '%datadir%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| datadir | /lvdata/mysql/3832/data/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
## discard前，表ibd文件信息
root@127.0.0.1:pdb 04:30:55 >\! ls -l /lvdata/mysql/3832/data/pdb/t_product*
-rw-r-----. 1 mysql mysql 114688 Jun 15 16:25 /lvdata/mysql/3832/data/pdb/t_product.ibd
## 执行discard命令
root@127.0.0.1:pdb 04:31:53 >alter table t_product discard tablespace;
Query OK, 0 rows affected (0.00 sec)
## discard后 ibd文件被删除
root@127.0.0.1:pdb 04:33:02 >\! ls -l /lvdata/mysql/3832/data/pdb/t_product*
ls: cannot access /lvdata/mysql/3832/data/pdb/t_product*: No such file or directory
root@127.0.0.1:pdb 04:33:06 >
3. 在源端对表进行FLUSH TABLES 操作，此时会多一个cfg文件
## flush tables表
root@127.0.0.1:testdb 04:36:21 >flush tables t_product for export;
Query OK, 0 rows affected (0.00 sec)
## 生成cfg文件
root@127.0.0.1:testdb 04:37:10 >\! ls -l /lvdata/mysql/3832/data/testdb/t_product*
-rw-r-----. 1 mysql mysql 638 Jun 15 16:37 /lvdata/mysql/3832/data/testdb/t_product.cfg
-rw-r-----. 1 mysql mysql 10485760 Jun 15 15:31 /lvdata/mysql/3832/data/testdb/t_product.ibd
root@127.0.0.1:testdb 04:37:37 >
4. 将表数据文件拷贝至目标路径中
## 拷贝两个文件（t_product.ibd, t_product.cfg）至目标目录中
[root@c1 testdb]# scp /lvdata/mysql/3832/data/testdb/t_product.{ibd,cfg} 192.168.139.128:/lvdata/mysql/3832/data/pdb/
The authenticity of host '192.168.139.128 (192.168.139.128)' can't be established.
ECDSA key fingerprint is SHA256:2/K2t2WFsWejN+6yhhGrGvs/yAYngo7bovg7z5Q21Uw.
ECDSA key fingerprint is MD5:ec:ec:95:10:d5:f6:eb:88:d4:3c:ab:57:58:b3:76:ad.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.139.128' (ECDSA) to the list of known hosts.
root@192.168.139.128's password:
t_product.ibd 100% 10MB 88.4MB/s 00:00
t_product.cfg 100% 638 1.7MB/s 00:00
[root@c1 testdb]#
## 查看目标目录，确认文件存在
[root@c1 testdb]# ls -l /lvdata/mysql/3832/data/pdb/t_product.*
-rw-r-----. 1 root root 638 Jun 15 16:42 /lvdata/mysql/3832/data/pdb/t_product.cfg
-rw-r-----. 1 root root 10485760 Jun 15 16:42 /lvdata/mysql/3832/data/pdb/t_product.ibd
[root@c1 testdb]#
5. 将源表做UNLOCK操作（cfg文件消失）
root@127.0.0.1:testdb 04:47:08 >UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1:testdb 04:47:16 >\! ls -l /lvdata/mysql/3832/data/testdb/t_product*
-rw-r-----. 1 mysql mysql 10485760 Jun 15 15:31 /lvdata/mysql/3832/data/testdb/t_product.ibd
root@127.0.0.1:testdb 04:47:19 >
6. 修改目标端表的权限
## 修改权限
[root@c1 pdb]# chown mysql.mysql t_product.*
[root@c1 pdb]# ls -lhrt t_product.*
-rw-r-----. 1 mysql mysql 10M Jun 15 16:42 t_product.ibd
-rw-r-----. 1 mysql mysql 638 Jun 15 16:42 t_product.cfg
7. 将上述ibd文件import到t_product表中
root@127.0.0.1:pdb 04:49:36 >alter table t_product import tablespace;
Query OK, 0 rows affected (0.07 sec)
root@127.0.0.1:pdb 04:49:57 >select count(*) from t_product;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
至此，已经将t_product表从 testdb库快速迁移至目标库pdb中复制
总结：
可传输表空间作为在日常运维过程中的一个小特性还是比较实用的，尤其是适合在特定场景下对大表的迁移场景中使用，速度更快，省去了直接insert数据库或者重建表的时间，但是实际使用中也应该注意以下限制：
1. 必须开启独立表空间 innodb_file_per_table
2. 两端的innodb_page_size大小必须一致
3. MySQL版本必须一致，且为 GA版（General Availability）
4. MySQL5.6及以上版本适用
发表评论