mysql数据表怎么复制(MySQL 复制表的方法)
mysql数据表怎么复制
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 文件数据到本地临时文件),最后一个执行效率是最高的,但是不能跨引擎,且只能进行全量拷贝。
以上就是mysql 复制表的方法的详细内容,更多关于mysql 复制表的资料请关注开心学习网其它相关文章!
原文链接:https://www.cnblogs.com/mengxinJ/p/14387096.html
- mysql索引面试总结(Mysql数据库索引面试题程序员基础技能)
- mysql 死锁产生的原因和必要条件(Mysql查看死锁与解除死锁的深入讲解)
- mysql join规则(浅谈为什么MySQL不推荐使用子查询和join)
- navicatformysql怎么激活(一文读懂navicat for mysql基础知识)
- deepin数据库编程(deepin20.1系统安装MySQL8.0.23超详细的MySQL8安装教程)
- mysql语句性能分析(聊聊MySQL的COUNT*的性能)
- 数据库mysql基本知识(深入理解r2dbc在mysql中的使用)
- mysql变量技巧(mysql用户变量与set语句示例详解)
- linuxmysql客户端搭建(一台linux主机启动多个MySQL数据库的方法)
- mysql的7种索引(浅入浅出 MySQL 索引)
- mysql将字符串转换成整数(MYSQL字符串强转的方法示例)
- dockermysql实例(docker 连接宿主Mysql操作)
- mysql添加数据很慢(mysql如何优化插入记录速度)
- dockermysql配置详解(Docker 部署Mysql 服务和Redis 服务的方法)
- mysql 慢查询日志
- ubuntu20.2安装mysql(Ubuntu 14.04下mysql安装配置教程)
- 夕云天际飞,亢龙化太极(夕云天际飞亢龙化太极)
- 爱情可以当饭吃吗(怎么回复)
- 高考数学题(高考数学题基础题占多少分)
- 没钱只能吃土(没钱要吃土了幽默短信发朋友圈)
- 今年考高会很难吗(今年高考会考试吗)
- 盘古开天地 他创造了世界,谁创造了盘古 盘古是伏羲吗(盘古开天地他创造了世界)
热门推荐
- wampserver配置教程图文(WampServer安装配置及使用图文教程)
- apache跳转配置(Apache 的 order deny allow 设置说明)
- python3有哪些内置模块(Python3.5内置模块之os模块、sys模块、shutil模块用法实例分析)
- zabbix sender能否发送告警数据(python3实现zabbix告警推送钉钉的示例)
- display flex 布局(解决display:flex属性 justify-content: space-between换行后的排版问题)
- 如何在cmd里开启mysql(Windows下通过cmd进入DOS窗口访问MySQL数据库)
- sql表更新关联语句(SQL update 多表关联更新的实现代码)
- php新建文件夹代码(php新建文件的方法实例)
- react新手指引页面编写(React+TypeScript进行项目构建案例讲解)
- jquery实现微信中长按识别二维码
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9