MySQL 复制表的方法

目录

• 1、mysqldump

• 执行过程：

• 特点

• 2、导出 CSV 文件（最灵活）

• 执行过程

• 特点

• 3、物理拷贝（最快）

• 过程

• 局限

• 总结

1、mysqldump

执行过程：

一、将数据导出为 sql 文件。

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

将数据导出为 sql 文件保存。上面几个参数的含义分别是：

1、–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

2、–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；

3、–no-create-info 的意思是，不需要导出表结构；

4、–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；

5、–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

二、执行文件，添加到表中

mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"

source 并不是一条 SQL 语句，而是一个客户端命令。也就是服务器端具体执行的是文件中的一条条 sql 语句，所以 binlog 记录的都是具体的 sql。

特点

1、生成的 sql 文件保存在客户端

2、默认保存数据方式是多个记录对，如下面格式

如果想要保存为一条语句只保存一条记录，那么可以加上参数–skip-extended-insert。

2、导出 CSV 文件（最灵活）

执行过程

一、导出为 CSV 文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

注意：

1、into outfile 指定了文件的生成位置（/server_tmp/），这个位置必须受参数 secure_file_priv 的限制。

参数 secure_file_priv 的可选值和作用分别是：

1）如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；

2）如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；

3）如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select … into outfile 操作。

2、如果同一个目录下存在同名文件，就会报错

3、一般情况下一条记录就对应 CSV 文件中的一行，但是如果某个字段值中有 "换行、制表符" 那么文件中也会包含，并且使用 "\" 来转义。

二、导入数据

load data infile '/server_tmp/t.csv' into table db2.t;

过程：

1、打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；

2、启动事务。

3、判断每一行的字段数与表 db2.t 是否相同：

1）若不相同，则直接报错，事务回滚；

2）若相同，则构造成一行，调用 InnoDB 引擎接口，写入到表中。

4、重复步骤 3，直到 /server_tmp/t.csv 整个文件读入完成，提交事务。

特点

1、文件保存在服务器端

2、关于 binlog 的记录，过程如下：

1）主库执行完成后，将 /server_tmp/t.csv 文件的内容直接写到 binlog 文件中。

2）往 binlog 文件中写入语句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0' INTO TABLE `db2`.`t`。

3）把这个 binlog 日志传到备库。

4）备库的 apply 线程在执行这个事务日志时：

a. 先将 binlog 中 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中；

b. 再执行 load data 语句，往备库的 db2.t 表中插入跟主库相同的数据。

关于 "local"：

1）不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；

2）加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端（其他会话涉及的操作），然后执行上述的 load data 流程。

3、上面的导出操作并不会导出表结构，所以，如果向导出表结构，可以使用 mysqldump 来同时导出 CSV 和表结构

mysqldump -h$host -P$port -u$user --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

会在$secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。

3、物理拷贝（最快）

在5.6之前，想要直接把.frm和.ibd文件拷贝到要拷贝的目录下是不行的，因为一个Innodb表除了需要这两个文件还需要在数据字典中注册。但是从 5.6 开始可以解决这一问题，在 5.6 引入了可传输空间，可以通过导出 + 导入表空间来实现拷贝

过程

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

1、执行 create table r like t，创建一个相同表结构的空表；

2、执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

3、执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

4、在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；

5、执行 unlock tables，这时候 t.cfg 文件会被删除；

6、执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

注意：

1、在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；

2、在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

局限

1、必须是全表拷贝，不能条件拷贝

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

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

总结

1、前两个都是逻辑备份，也就是可以跨引擎使用，最后一个不行

2、前两个可以条件拷贝，最后一个不行

3、第二个功能是最灵活的，但是在集群从库接收时会比较耗时（需要先拷贝 CSV 文件数据到本地临时文件），最后一个执行效率是最高的，但是不能跨引擎，且只能进行全量拷贝。

来源：https://www.cnblogs.com/mengxinJ/p/14387096.html