mysql 复制迁移表的三种方法对比

造数据

create database test;
use test;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
create database test2;create table test2.t like test.t;

[root@centos7-mysql5 mysql_random]# ./mysql_random_data_load test t 5000 --user=root --password=123456
INFO[2022-05-07T00:06:29-07:00] Starting
0s [====================================================================] 100%
INFO[2022-05-07T00:06:29-07:00] 5000 rows inserted
[root@centos7-mysql5 mysql_random]#

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|     5000 |
+----------+
1 row in set (0.00 sec)

一.select … into outfile

用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份  

mysql> select * from test.t where a>90000 into outfile '/mysql/backup/t.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这里报错因为mysql5.7增加了参数secure_file_priv用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

• secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

• secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

• secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

• 在my.cnf中加入参数后重启生效
  

secure_file_priv='/mysql/backup'

导出

mysql> select * from test.t where a>90000 into outfile '/mysql/backup/t.csv';
Query OK, 5000 rows affected (0.00 sec)

该命令不会覆盖文件，因此要确保/mysql/backup/t.csv不存在才不会报错

导入

mysql> load data infile '/mysql/backup/t.csv' into table test2.t;
Query OK, 5000 rows affected (0.04 sec)
Records: 5000  Deleted: 0  Skipped: 0  Warnings: 0

load data 命令有两种用法：
1. 不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；
2. 加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data流程  

二.mysqldump方式

用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的where 条件写法。 

导出

[root@centos7-mysql5 backup]# mysqldump -uroot -p123456 --databases test --tables t  --single-transaction --add-locks --set-gtid-purged=off>/mysql/backup/t.txt
[root@centos7-mysql5 backup]# ls
t.txt

--single-transaction :对事务引擎执行热备

-add-locks  :备份数据库表时锁定数据库表

--set-gtid-purged=off: 不导出gtid号（事物号）

--tab ：可以同时导出表结构定义文件和 csv 数据文件

导入

登录要导入的库

mysql> use test2;
Database changed
mysql> source /mysql/backup/t.txt
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
······mysql> select count(*) from test2.t;
+----------+
| count(*) |
+----------+
|     5000 |
+----------+
1 row in set (0.00 sec)

三. 物理拷贝

在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能  .

物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：

1.需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用  

2.必须是全表拷贝，不能只拷贝部分数据；

3.由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。

执行步骤如下：
1. 执行 create table test2.r like test.t，创建一个相同表结构的空表；
2. test2执行 alter table test2.r discard tablespace，这时候 t.ibd 文件会被删除；
3. test执行 flush table test.t for export，这时候 test 目录下会生成一个 t.cfg 文件；
4. 在 test 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令到相应库里并修改权限；
5. test执行 unlock tables，这时候 t.cfg 文件会被删除；
6. test2执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由
于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
至此，拷贝表数据的操作就完成了。  

session1mysql> use test2;
Database changed
mysql> drop table r;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test2.r like test.t;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test2.r discard tablespace;
Query OK, 0 rows affected (0.01 sec)
session2
mysql> use test;
Database changed
mysql> flush table test.t for export;
Query OK, 0 rows affected (0.00 sec)session3
[root@centos7-mysql5 test]# cp {t.cfg,t.ibd} ../test2/
[root@centos7-mysql5 test2]# chown mysql.mysql t.*[root@centos7-mysql5 test2]# mv t.cfg r.cfg
[root@centos7-mysql5 test2]# mv t.ibd r.ibd
session2
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
session1
mysql> alter table r import tablespace;
Query OK, 0 rows affected (0.01 sec)

导入完成mysql> select count(*) from r;
+----------+
| count(*) |
+----------+
|     5000 |
+----------+
1 row in set (0.01 sec)