欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql 复制表数据

程序员文章站 2022-05-29 09:36:25
...

在 mysql 数据库的使用过程当中,复制数据是一个非常常见并且有用的操作,在数据量较小,表扫描行数少的情况下,我们可以使用 insert into ... select ... 语句来完成表数据的复制。具体的流程如下:

假如有表 t,建表语句和初始化数据如下:

create table t (
    id int not null primary key,
    c  int null,
    d  int null
);

insert into t values (0, 0, 0), (5, 5, 5), (10, 10, 10), (15, 15, 15), (20, 20, 20);

然后使用语句 create table t_temp like t 创建一个和表 t 结构相同的表 t_temp,这时我们需要将表 t 中的数据复制到表 t_temp 中,可以使用命令 insert into t_temp select * from t where id > 10;,这里的 where 条件是可选的,后面可以加其他的 sql 语句。

这种拷贝表数据的方式简单,但是存在一个很大的局限性,那就是在数据量较大的情况下,事务执行的时间较长,对于一些业务关联性强的表,可能对业务造成影响。

因此,我们可以将表中的数据存储到外部文件中,然后再导入到另一个表中,常见的方法有以下介绍的三种。

一、逻辑拷贝

1.1 mysqldump

mysqldump 是 mysql 官方的逻辑备份工具,它使用起来非常简单,功能强大,下面是一个示例:

mysqldump -u root -p --databases sakila --tables t --single-transaction --where="id >= 20" --result-file=/usr/local/sakila_t.sql

这几个常见参数的含义分别是:

  • -u、-p 和登录数据库时的选项是一样的,分别表示用户名和密码
  • –databases 表示指定数据库
  • –tables 表示指定需要导出的表(可选)
  • –single-transaction 表示使用一致性视图,导出数据的时候不加表锁
  • –where 后面可以加上简单的过滤条件
  • –result-file 后面加上导出的文件的存储路径

如果需要查看 mysqldump 的更多用法,直接在终端输入 mysqldump --help 即可。

导出之后,需要在目标数据库命令行中使用 source 命令执行,语句如下:

source /usr/local/sakila_t.sql

1.2 导出数据文件

另一种方式是使用 sql 语句导出为外部文件,命令是

select * from t into outfile/var/lib/mysql-files/sakila_t.csv‘

需要注意的是,导出的文件的存储路径受到系统参数 secure_file_priv 的控制,这个参数的取值情况有以下三种:

  • 如果设置是空的,表示不限制文件存储的路径,一般这样设置是不太安全的
  • 如果设置为一个具体的路径,表示生成的文件必须存放在这个目录下
  • 如果设置为 NULL,表示禁止在这个 mysql 实例中执行 select ... into outfile 操作

导出为数据文件后,在 mysql 命令行执行以下命令将数据导入:

load data infile '/var/lib/mysql-files/sakila_t.csv' into table sakila.t_temp;

需要注意的是,如果数据文件中的数据列和数据表中字段不对应的话,那么会直接报错,导入失败。

使用这个命令导出,只能导出数据文件,而不能导出表结构,如果需要导出数据表的结构,可以使用如下的 mysqldump 命令:

mysqldump --databases sakila --tables t --tab=/var/lib/mysql-files -u root -p

执行这个导出命令,会在 /var/lib/mysql-files(secure_file_priv 指定的路径) 生成一个表结构 sql 文件,以及一个表数据 txt 文件。

二、物理拷贝

前面介绍的这两种方式都是逻辑复制数据的方法,在 mysql 5.6 及以后,可以通过复制表文件的方式来进行物理复制表的数据。

还是以上面的表 t 为例,假如需要将其复制一份,命名为 t_temp,具体的步骤如下:

  • 执行命令 create table t_temp like t,创建一个和表 t 结构相同的表 t_temp
  • 执行命令 alter table t_temp discard tablespace,这时候表 t_temp 的 ibd 文件会被删除
  • 执行命令 flush table t for export,这时候在数据库的表结构文件夹中,会生成一个 t.cfg 文件。表结构文件保存在 mysql 的默认数据目录,所对应的数据库目录下(一般是 /var/lib/mysql)。需要注意的是,执行完这个命令之后,表处于只读状态,直到下面执行 unlock tables 时才释放读锁
  • 复制表 t 的数据文件,执行命令 cp t.cfg t_temp.cfgcp t.ibd t_temp.ibd,如果复制的文件不是属于 mysql 用户的,需要使用 chown 命令将文件的所属用户和用户组改为 mysql
  • 执行命令 unlock tables ,这时候文件 t.cfg 将会被删除,并且释放表 t 的读锁
  • 执行命令 alter table t_temp import tablespace,拷贝完成

下面对几种常见的拷贝表数据的方式做个简单的总结:

相较于其他的几种方式,物理复制表数据的速度是最快的,但是它的缺点是只能复制整个表的数据,不能操作部分数据,并且这种方式只支持 InnoDB 表。

而使用 mysqldump 导出数据,优点是可以使用过滤条件导出部分数据,但是其缺点是不能使用一些较为复杂的 sql 语句例如 join。

使用 select ... from t into outfile 这种方式,最大的优点是支持各种 sql ,更加的灵活,但是它不能导出表结构,导出表结构的话需要单独操作。

相关标签: mysql 系列 mysql