MySQL 复制表的方法

作者:萌新J 时间:2024-01-21 02:22:50 

目录
  • 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、默认保存数据方式是多个记录对,如下面格式

    MySQL 复制表的方法

    如果想要保存为一条语句只保存一条记录,那么可以加上参数–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 表中插入跟主库相同的数据。

    MySQL 复制表的方法

    关于 "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 相同的数据。

    MySQL 复制表的方法

    注意:

    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 文件数据到本地临时文件),最后一个执行效率是最高的,但是不能跨引擎,且只能进行全量拷贝。

    来源:https://www.cnblogs.com/mengxinJ/p/14387096.html

    标签:MySQL,表
    0
    投稿

    猜你喜欢

  • Python编写百度贴吧的简单爬虫

    2023-12-06 02:17:56
  • Python中matplotlib库安装失败的经验总结(附pycharm配置anaconda)

    2021-01-04 21:42:14
  • mysql prompt的用法详解

    2024-01-28 07:30:32
  • pip更新问题的解决:'python -m pip install --upgrade pip' 报错问题(最新推荐)

    2023-04-23 04:32:13
  • asp如何做一个专门显示文本文件的页面?

    2010-07-12 19:04:00
  • Python数据存储之 h5py详解

    2023-09-18 00:04:48
  • 使用Python+Splinter自动刷新抢12306火车票

    2023-09-17 18:38:52
  • 卸载VS2011 Developer Preview后Sql Server2008 R2建立数据库关系图报“找不到指定的模块”错误的解决方法

    2011-11-03 16:49:09
  • Pytorch中transforms.Resize()的简单使用

    2023-06-17 02:23:28
  • Mysql语句快速复习教程(全)

    2024-01-26 20:13:37
  • 将备份的SQLServer数据库转换为SQLite数据库操作方法

    2024-01-23 18:52:52
  • 如何在Unix系统环境下安装MySQL数据库

    2009-01-04 13:09:00
  • 基于PHP实现用户注册登录功能

    2024-04-30 08:48:35
  • 详解JavaScript匿名函数和闭包

    2024-04-19 10:07:12
  • mysql drop database删除数据库命令实例讲解

    2024-01-18 19:40:01
  • Oracle 管道 解决Exp/Imp大量数据处理问题

    2024-01-21 22:15:16
  • 详解python中各种文件打开模式

    2023-08-30 09:10:30
  • 详解如何通过Python制作一个密码生成器

    2023-11-24 10:36:43
  • Mysql常见bug及解决方案超详细讲解

    2024-01-15 17:20:56
  • SQL Server数据类型char、nchar、varchar、nvarchar的区别浅析

    2024-01-14 06:57:15
  • asp之家 网络编程 m.aspxhome.com