Mysql 系列 | 复制表数据

需要复制的数据较少的场合，可以控制源表扫描行数且加锁范围很小的情况下，直接用 insert...select 语句即可实现少量数据的复制。

数据量较大的情况，为了避免源表加读锁，需要先将数据放入外部文件，再写入目标表。

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=dbbk.sql

• 用 mysqldump 命令将数据导出成 insert 语句

• 可以通过添加 where 条件，实现导出部分数据

• 文件保存在客户端机器，会自动覆盖之前的同名文件

• -add-locks=0 表示输出的文件结果中没有 LOCK TABLES t WRITE;

• -no-create-info 表示不导出表结果

• -single-transaction 表示导出时不对表 db1.t 加锁，使用 START TRANSACTIONWITH CONSISTENTSNAPSHOT 的方法

• -set-gtid-purged=off 表示不输出 gtid 相关的信息

• -result-file 表示输出文件的路径

• 将数据写入目标数据库 db2 中，mysql -h$host -P$port -u$user db2 -e "source dbbk.sql"
  
  

导出 CSV 文件

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

• 导出的 csv 文件保存在数据库服务端，且每次只能导出一张表的数据

• into outfile 表示输出文件的位置，受参数 secure_file_priv 的限制

  • secure_file_priv=empty，表示不限制文件生成的位置，不建议！！

  • secure_file_priv=具体路径，表示生成的文件只能放在指定目录或子目录

  • secure_file_priv=NULL，表示禁止在 Mysql 实例中进行导出 csv 操作

• 如果服务器存在同名文件会报错，不会自动覆盖

• 这种方式只会导出数据，没有表结构

• 将数据写入目标 db2.t 表中，以换行符为每一行数据的分隔符，load data infile 'dbbk.csv' into table db2.t;
  
  

物理拷贝

• innodb 表都对应有 .frm 和 .ibd 文件，但是直接拷贝这两文件不管用！！！除了物理文件外，还需要在数据字典中注册，否则系统不会识别

• Mysql5.6 引入了可传输表空间（transportable tablespace）的方法，通过导出后导入表空间的方式实现

  • create table t2 like t1，创建一个相同表结构的空表 t2

  • alter table t2 discard tablespace，t2.ibd 文件会被删除

  • flush table t1 for export，生成 t1.cfg 文件，此时表 t1 只读，直到 unlock tables

  • cp t1.cfg t2.cfg cp t1.ibd t2.ibd，得到 t2.cfg 和 t2.ibd，注意文件权限，Mysql需要可读写

  • unlock tables，t1.cfg 文件会被删除

  • alter table t2 import tablespace，将 t2.ibd 作为表 t2 的新的表空间

• 物理拷贝方式，比起前两种速度最快

• 只能拷贝全表数据，而且需要登陆到服务器进行操作

• 源表和目标表的引擎必须都是 innodb

三种方式各有利弊，选择合适的就是最好的