41 | MySQL快速复制数据到一张表中（本篇躺平一下，了解即可）

最新推荐文章于 2024-08-21 15:18:12 发布

笔仙笔仙

最新推荐文章于 2024-08-21 15:18:12 发布

阅读量962

点赞数

分类专栏： MySQL笔记文章标签： java mysql

原文链接：https://time.geekbang.org/column/article/81925

版权

MySQL笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

一、前言

现有表 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

二、复制表数据的三种方式

2.1.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；

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"

1）打开文件，默认以分号为结尾读取一条条的 SQL 语句；2）将 SQL 语句发送到服务端执行。

2.2.导出CSV文件方式的命令是什么（不用记）？如果binlog_format=statement格式，备库如何执行？load data命令的有哪两种用法？

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

1）into outfile 指定了文件的生成位置（/server_tmp/），这个位置必须受参数 secure_file_priv 的限制。2）这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。

客户端：load data infile '/server_tmp/t.csv' into table db2.t;

这条语句的执行流程如下所示：

1）打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；2）启动事务；3）判断每一行的字段数与表 db2.t 是否相同：若不相同，则直接报错，事务回滚；相同则提交；4）重复3步骤，最后提交事务。

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 表中插入跟主库相同的数据。
1）不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；2）加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程。

2.3.可以直接把表的.frm 文件和.ibd 文件拷贝到一个库中？为什么不可以？如何进行物理拷贝？

不可以
一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有，所以无法识别。
可传输表空间(transportable tablespace) 的方法

2.4.三种方式的优缺点对比

物理：优点 = 速度最快，尤其对于大表拷贝来说是最快的方法。缺点 = 1）必须是全表拷贝，不能只拷贝部分数据。2）需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用。3）由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
dump：优点 = 1）可以在 where 参数增加过滤条件，来实现只导出部分数据；2）支持跨引擎。缺点 = 不能使用 join 这种比较复杂的 where 条件写法。
csv：优点 = 1）最灵活；2）支持跨引擎。缺点 = 每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份

补充：

MySQL的数据库其相关文件都会存放在安装目录下data文件夹下的同命文件夹中，不同的存储引擎创建的表其文件也不一样，下面来认识下这些数据库文件。

db.opt

用来记录该库的默认字符集编码和字符集排序规则用的。也就是说如果你创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。

.frm

与表相关的元数据信息都存放在.frm文件中,主要是表结构的定义信息,不论什么存储引擎,每一个表都会有一个以表名命名的.frm文件。

.MYD和.MYI

.MYD：MY Data，是MyISAM存储引擎专用的用于存放MyISAM表的数据；

.MYI：MY Index，也是专属于MyISAM存储引擎的主要存放MyISAM表的索引相关信息。

.ibd和.ibdata

两者都是专属于InnoDB存储引擎的数据库文件。

当采用共享表空间时所有InnoDB表的数据均存放在.ibdata中，所以当表越来越多时，这个文件会变得很大；

相对应的.ibd就是采用独享表空间时InnoDB表的数据文件。

修改为独享表空间的方法是在my.ini配置文件中添加/修改此条：

Innodb_file_per_table=1

转载自：https://blog.csdn.net/weixin_35728532/article/details/113428046