乐趣区

关于mysql:MySQL学习笔记11表复制

jiezi

例子 


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，并插入 1000 行数据，同时创立一个雷同构造的表 db2.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

mysqldump 参数阐明：

参数 阐明
–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 示意生成的文件在客户端机器上
–skip-extended-insert 生成的文件中一条 INSERT 语句只插入一行数据

将这些 INSERT 语句放到 db2 库里去执行 

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"
客户端指令 source：关上文件，默认以分号为结尾读取一条条的 SQL 语句；将 SQL 语句发送到服务端执行。

服务端执行的并不是这个“source t.sql” 语句，而是 INSERT 语句。所以，不论是在慢查问日志（slow log），还是在 binlog，记录的都是这些要被真正执行的 INSERT 语句。

导出 CSV 文件 

1、导出 csv
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
2、load data
load data infile '/server_tmp/t.csv' into table db2.t;

导出 csv 语句阐明：

1、这条语句会将后果保留在服务端。如果你执行命令的客户端和 MySQL 服务端不在同一个机器上，客户端机器的长期目录下是不会生成 t.csv 文件的。
2、into outfile 指定了文件的生成地位（/server_tmp/），这个地位必须受参数 secure_file_priv 的限度。
3、参数 secure_file_priv 的可选值和作用别离是：
如果设置为 empty，示意不限度文件生成的地位，这是不平安的设置；
如果设置为一个示意门路的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
如果设置为 NULL，就示意禁止在这个 MySQL 实例上执行 select … into outfile 操作。3、这条命令不会帮你覆盖文件，因而你须要确保 /server_tmp/t.csv 这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。
4、这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。然而，如果字段中蕴含换行符，在生成的文本中也会有换行符。不过相似换行符、制表符这类符号，后面都会跟上“\”这个本义符，这样就能够跟字段之间、数据行之间的分隔符辨别开。

load data 语句阐明：

1、关上文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
2、启动事务。
3、判断每一行的字段数与表 db2.t 是否雷同：
若不雷同，则间接报错，事务回滚；
若雷同，则结构成一行，调用 InnoDB 引擎接口，写入到表中。
4、反复步骤 3，直到 /server_tmp/t.csv 整个文件读入实现，提交事务。

binlog 备库重放

如果 binlog_format=statement 这个 load 语句记录到 binlog 里当前，因为 /server_tmp/t.csv 文件只保留在主库所在的主机上，如果只是把这条语句原文写到 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 线程在执行这个事务日志时：
4-a. 先将 binlog 中 t.csv 文件的内容读出来，写入到本地长期目录 /tmp/SQL_LOAD_MB-1-0 中；
4-b. 再执行 load data 语句，往备库的 db2.t 表中插入跟主库雷同的数据。

load data 命令的两种用法

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

物理拷贝

间接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下，不可行。因为，一个 InnoDB 表，除了蕴含这两个物理文件外，还须要在数据字典中注册。间接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，零碎是不会辨认和承受它们的。

可传输表空间 (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；
5、这两个命令（这里须要留神的是，拷贝失去的两个文件，MySQL 过程要有读写权限）；
6、执行 unlock tables，这时候 t.cfg 文件会被删除；
7、执行 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、物理拷贝的形式速度最快，尤其对于大表拷贝来说是最快的办法。如果呈现误删表的状况，用备份复原出误删之前的长期库，而后再把长期库中的表拷贝到生产库上，是复原数据最快的办法。然而，这种办法的应用也有肯定的局限性：
必须是全表拷贝，不能只拷贝局部数据；
须要到服务器上拷贝数据，在用户无奈登录数据库主机的场景下无奈应用；
因为是通过拷贝物理文件实现的，源表和指标表都是应用 InnoDB 引擎时能力应用。
2、用 mysqldump 生成蕴含 INSERT 语句文件的办法，能够在 where 参数减少过滤条件，来实现只导出局部数据。这个形式的有余之一是，不能应用 join 这种比较复杂的 where 条件写法。
3、用 select … into outfile 的办法是最灵便的，反对所有的 SQL 写法。但，这个办法的毛病之一就是，每次只能导出一张表的数据，而且表构造也须要另外的语句独自备份。

退出移动版