阮胜昌的技术记录站-LinuxMySQL数据库运维

如果有一张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)


以上就是通过传输表空间，基于物理的数据文件，只复制一个表的一个分区数据,速度就是快，哈哈^_^