MySQL复制

发布于 2019-06-20 · 本文总共 2343 字 · 阅读大约需要 7 分钟

快速复制表的三种方法

为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表

创建一个表 db1.t，并插入 1000 行数据，同时创建一个相同结构的表 db2.t。

create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
  create procedure idata()
  begin
    declare i int;
    set i=1;
    while(i<=1000)do
      insert into t values(i,i,i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call idata();

create database db2;
create table db2.t like db1.t;

假设，我们要把 db1.t 里面 a>900 的数据行导出来，插入到 db2.t 中

mysqldump 方法

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

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

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

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

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

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

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

source 并不是一条 SQL 语句，而是一个客户端命令。 mysql 客户端执行这个命令的流程：

打开文件，默认以分号为结尾读取一条条的 SQL 语句；

将 SQL 语句发送到服务端执行。

导出 CSV 文件

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

得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中:

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

物理拷贝方法

直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下，是否可行？

不行。因为，一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的。

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

在 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 相同的数据。

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

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

当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

总结

三种方法的优缺点。

1.物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。

如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。

但是，这种方法的使用也有一定的局限性：必须是全表拷贝，不能只拷贝部分数据；需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。

2.用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。

这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。

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

MySQL复制

快速复制表的三种方法

mysqldump 方法

导出 CSV 文件

物理拷贝方法

总结

文章评论