MySQL传输表空间-让你的数据飙起来
如果有一张100G的单表或分区表，研发要求在1-2个小时内将表数据迁移到另一台数据库上，你会怎么做？
如果用传统的导入导出，基本完成不了任务，还好有传输表空间这个工具,可以帮上忙：
普通表数据迁移：
1.目标端创建与源同样的表结构
CREATE TABLE `test` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.目标端保留.frm文件，删除.ibd文件
alter table test.test discard tablespace;
3.源端对表加一个读锁
flush table test.test for export; #注意此操作会将数据库的purge线程全部停止
4.拷贝.cfg和.ibd文件到目标端数据文件位置
scp test.ibd root@192.168.10.1:/usr/local/mysql/data/test
scp test.cfg root@192.168.10.1:/usr/local/mysql/data/test
5.源端释放锁
unlock tables;
6.目标端文件赋予权限
chown -R mysql:mysql *
chmod -R 755 *
7.目标端导入表
alter table test.test import tablespace;
8.验证数据
分区表数据迁移：
源表结构：
CREATE TABLE `employees` (
`emp_no` INT(11) NOT NULL,
`birth_date` DATE NOT NULL,
`first_name` VARCHAR(14) NOT NULL,
`last_name` VARCHAR(16) NOT NULL,
`gender` ENUM('M','F') NOT NULL,
`hire_date` DATE NOT NULL,
PRIMARY KEY (`emp_no`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (emp_no)
PARTITIONS 4 */;
源分区信息：
mysql> select table_schema,table_name,partition_name,table_rows from information_schema.partitions where table_name='employees' and table_schema='test';
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| test | employees | p0 | 74552 |
| test | employees | p1 | 74405 |
| test | employees | p2 | 74325 |
| test | employees | p3 | 74422 |
+--------------+------------+----------------+------------+
4 rows in set (0.03 sec)
目的：
通过传输表空间，将表中的一个分区传输到其它服务器上对应表的分区上
在目标服务器上，创建跟源表结构一样的表：
目标端：
CREATE TABLE `employees` (
`emp_no` INT(11) NOT NULL,
`birth_date` DATE NOT NULL,
`first_name` VARCHAR(14) NOT NULL,
`last_name` VARCHAR(16) NOT NULL,
`gender` ENUM('M','F') NOT NULL,
`hire_date` DATE NOT NULL,
PRIMARY KEY (`emp_no`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (emp_no)
PARTITIONS 4 */;
mysql> select table_schema,table_name,partition_name,table_rows from information_schema.partitions where table_name='employees' and table_schema='test';
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| test | employees | p0 | 0 |
| test | employees | p1 | 0 |
| test | employees | p2 | 0 |
| test | employees | p3 | 0 |
+--------------+------------+----------------+------------+
4 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employees |
| zz |
+----------------+
2 rows in set (0.00 sec)
mysql> alter table employees discard partition p0 tablespace;
Query OK, 0 rows affected (0.11 sec)
源端：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employees |
| zz |
+----------------+
2 rows in set (0.00 sec)
mysql> flush table test.employees for export;
Query OK, 0 rows affected (0.02 sec)
在每个分区下产生了cfg文件：
[root@c7-mysql-master-101 test]# ll
总用量 28844
-rw-r-----. 1 mysql mysql 65 8月 18 09:01 db.opt
-rw-r-----. 1 mysql mysql 8768 8月 27 16:57 employees.frm
-rw-r-----. 1 mysql mysql 669 8月 27 17:01 employees#P#p0.cfg
-rw-r-----. 1 mysql mysql 7340032 8月 27 16:59 employees#P#p0.ibd
-rw-r-----. 1 mysql mysql 669 8月 27 17:01 employees#P#p1.cfg
-rw-r-----. 1 mysql mysql 7340032 8月 27 16:59 employees#P#p1.ibd
-rw-r-----. 1 mysql mysql 669 8月 27 17:01 employees#P#p2.cfg
-rw-r-----. 1 mysql mysql 7340032 8月 27 16:59 employees#P#p2.ibd
-rw-r-----. 1 mysql mysql 669 8月 27 17:01 employees#P#p3.cfg
-rw-r-----. 1 mysql mysql 7340032 8月 27 16:59 employees#P#p3.ibd
-rw-r-----. 1 mysql mysql 8578 8月 27 10:13 zz.frm
-rw-r-----. 1 mysql mysql 114688 8月 27 10:13 zz.ibd
将employees#P#p0.cfg employees#P#p0.ibd这两个文件复制到目标服务器的mysql数据目录下：
[root@c7-mysql-master-101 test]# scp employees#P#p0.cfg employees#P#p0.ibd root@192.168.67.102:/var/lib/mysql/test/
employees#P#p0.cfg 100% 669 625.3KB/s 00:00
employees#P#p0.ibd 100% 7168KB 56.4MB/s 00:00
[root@c7-mysql-master-101 test]#
释放锁：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
在目标端：
以下文件已从原服务器传输过来：
[root@c7-mysql-slave-102 test]# ll
总用量 7408
-rw-r-----. 1 mysql mysql 65 8月 18 09:01 db.opt
-rw-r-----. 1 mysql mysql 8768 8月 27 16:50 employees.frm
-rw-r-----. 1 root root 669 8月 27 17:05 employees#P#p0.cfg
-rw-r-----. 1 root root 7340032 8月 27 17:05 employees#P#p0.ibd
-rw-r-----. 1 mysql mysql 65536 8月 27 16:50 employees#P#p1.ibd
-rw-r-----. 1 mysql mysql 65536 8月 27 16:50 employees#P#p2.ibd
-rw-r-----. 1 mysql mysql 65536 8月 27 16:50 employees#P#p3.ibd
-rw-r-----. 1 mysql mysql 8578 8月 27 10:13 zz.frm
-rw-r-----. 1 mysql mysql 114688 8月 27 10:13 zz.ibd
目标端文件赋予权限
chown -R mysql:mysql *
chmod -R 755 *
目标端导入表
mysql> alter table test.employees import partition p0 tablespace;
Query OK, 0 rows affected (0.14 sec)
查看导入的分区数据：
mysql> select table_schema,table_name,partition_name,table_rows from information_schema.partitions where table_name='employees' and table_schema='test';
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| test | employees | p0 | 74833 |
| test | employees | p1 | 0 |
| test | employees | p2 | 0 |
| test | employees | p3 | 0 |
+--------------+------------+----------------+------------+
4 rows in set (0.00 sec)
以上就是通过传输表空间，基于物理的数据文件，只复制一个表的一个分区数据,速度就是快，哈哈^_^
