MySQL之如何复制一张表的数据

写在前面

在工作中经常会遇到需要拷贝一个表的数据的场景，本文就一起来看下常用的方法都有哪些。如下准备测试数据：

drop database db1;
drop database db2;
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1：mysqldump+source

1.1：mysqldump导出db1.t的数据

[root@localhost tmp]# mysqldump -h127.0.0.1 -P3306 -uroot -p --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/tmp/t.sql
[root@localhost tmp]# cat /tmp/t.sql 
...
INSERT INTO `t` VALUES (901,901,901),(902,902,902),(903,903,903),(904,904,904),(905,905,905),(906,906,906),(907,907,907),(908,908,908),(909,909,909),(910,910,910),(911,911,911),(912,912,912),(913,913,913),(914,914,914),(915,915,915),(916,916,916),(917,917,917),(918,918,918),(919,919,919),(920,920,920),(921,921,921),(922,922,922),(923,923,923),(924,924,924),(925,925,925),(926,926,926),(927,927,927),(928,928,928),(929,929,929),(930,930,930),(931,931,931),(932,932,932),(933,933,933),(934,934,934),(935,935,935),(936,936,936),(937,937,937),(938,938,938),(939,939,939),(940,940,940),(941,941,941),(942,942,942),(943,943,943),(944,944,944),(945,945,945),(946,946,946),(947,947,947),(948,948,948),(949,949,949),(950,950,950),(951,951,951),(952,952,952),(953,953,953),(954,954,954),(955,955,955),(956,956,956),(957,957,957),(958,958,958),(959,959,959),(960,960,960),(961,961,961),(962,962,962),(963,963,963),(964,964,964),(965,965,965),(966,966,966),(967,967,967),(968,968,968),(969,969,969),(970,970,970),(971,971,971),(972,972,972),(973,973,973),(974,974,974),(975,975,975),(976,976,976),(977,977,977),(978,978,978),(979,979,979),(980,980,980),(981,981,981),(982,982,982),(983,983,983),(984,984,984),(985,985,985),(986,986,986),(987,987,987),(988,988,988),(989,989,989),(990,990,990),(991,991,991),(992,992,992),(993,993,993),(994,994,994),(995,995,995),(996,996,996),(997,997,997),(998,998,998),(999,999,999),(1000,1000,1000);
...
1
2
3
4
5

如果不想要values中包含多行，可以增加参数–skip-extended-insert，但是一般不需要，因为values多行的插入速度是要优于单value的。

主要参数说明如下：

--add-locks=0：输出文件中不要增加Lock table t write
--no-create-info：不要建表语句
--single-transaction：不对表t加表锁，而是使用一致性视图
--set-gtid-purged=OFF：不输出gtid相关的信息
--where="a>900"：过滤条件
--result-file：要写入的目标文件
1
2
3
4
5
6

1.1.1：输出到控制台

ogon:~ xb$ mysqldump -h127.0.0.1 -P3306 -uroot -p --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF test --tables a --where="1=1"
Enter password: 
......
-- Dumping data for table `a`
--
-- WHERE:  1=1

/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES ('a'),('a'),('b'),('b'),('c'),('c'),('z'),('z'),('f'),('f'),('e'),('e');
1
2
3
4
5
6
7
8
9

1.2：source导入数据

[root@localhost tmp]# mysql -h127.0.0.1 -P3306 -uroot -p db2 -e "select count(*) from t"
Enter password: 
+----------+
| count(*) |
+----------+
|        0 |
+----------+
[root@localhost tmp]# mysql -h127.0.0.1 -P3306 -uroot -p db2 -e "source /tmp/t.sql"
Enter password: 
[root@localhost tmp]# mysql -h127.0.0.1 -P3306 -uroot -p db2 -e "select count(*) from t"
Enter password: 
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

source是客户端命令，执行流程如下：

1：解析文件，以分号为结尾，读取一条条的sql语句
2：将sql语句发送到服务器端执行
1
2

即效果等同于我们自己将sql语句复制出来手动执行。

2：csv

这种方式需要在服务器端生成文件，而MySQL对于这种在客户端操作并在服务器端生成文件的方式，提供了参数secure_file_priv来控制具体行为，其可能值如下：

1：empty，表示不限制，这种方式很危险，线上不可这样设置
2：一个表示路径的字符串，表示只可以在指定的路径和子路径下生成文件
3：NULL，表示禁止在当前MySQL实例执行select ... into outfile操作，默认就是该配置，是最安全的
1
2
3

为了执行以下的测试，我们需要查看当前secure_file_priv的值，并将其修改为合适的值，以满足测试的要求：

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

可以看到是NULL，为了测试，我们将其修改为指定路径，方式为修改my.cnf在mysqld下增加secure_file_priv=/tmp，然后重启，重启后如下：

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

下面我们就能开始我们的测试了。

2.1：生成csv文件

[root@localhost etc]# mysql -uroot -p db1 -e "select * from db1.t where a>900 into outfile '/tmp/t.csv'"
Enter password: 
[root@localhost etc]# ll /tmp/ | grep 'csv'
-rw-rw-rw- 1 mysql mysql  1203 Sep 12 17:56 t.csv
[root@localhost etc]# cat /tmp/t.csv 
901     901     901
902     902     902
903     903     903
904     904     904
905     905     905
906     906     906
...
1
2
3
4
5
6
7
8
9
10
11
12

2.2：导入数据

mysql> load data infile '/tmp/t.csv' into table db2.t;
Query OK, 100 rows affected (0.00 sec)
Records: 100  Deleted: 0  Skipped: 0  Warnings: 0
1
2
3

具体的执行流程如下：

1：读入文件，以\t制表符作为字段的分隔符，以\n换行符作为一行的分隔符，读取数据
2：启动事务
3：判断每行的数据个数和字段数是否相同，不相同则直接报错，回滚事务，否则构造一行数据，并调用innodb的接口写入数据
4：重复3，直到csv中的所有行都写入到数据库，最后提交事务
1
2
3
4

3：物理拷贝

如果只是简单的拷贝.frm和.ibd文件，是不行的，因为这样并没有在系统字典中注册，系统是不会识别的，而在mysql5.6版本中引入了可传输表空间（transportable tablespace）的概念，来解决普通方法不在系统字典中注册等问题，从而实现物理拷贝。接下来看下如何操作：

3.1：创建一张要拷贝数据的表r4，并备份t

mysql> create table r4 like t;
Query OK, 0 rows affected (0.00 sec)
1
2

这样就会生成对应的r4.frm,r4,ibd文件了，如下：

[root@localhost db1]# ls -lt
total 632
-rw-r----- 1 mysql mysql 114688 Sep 13 15:59 r4.ibd
-rw-r----- 1 mysql mysql   8604 Sep 13 15:59 r4.frm
...
1
2
3
4
5

3.2：r4放弃自己的表空间

mysql> alter table r4 discard tablespace;
Query OK, 0 rows affected (0.00 sec)
1
2

这样，会将r4对应的数据文件r4.ibd文件删除。

3.3：生成表t的配置文件

mysql> flush table t for export;
Query OK, 0 rows affected (0.00 sec)
1
2

注意：执行完该命令后，表t会整体处于只读状态，效果等同于执行lock tables t read 如下：

mysql> update t set a=a where id=1;
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
1
2

3.4：使用表t的cfg和ibd生成r4的

[root@localhost db1]# cp t.cfg r4.cfg && cp t.ibd r4.ibd
1

MySQL用户和创建文件用户可能权限不同，所以需要修改权限：

[root@localhost db1]# chmod 777 r4.cfg && chmod 777 r4.ibd 
1

3.5：释放表t的表读锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
1
2

如果是不执行这步的话，表t将一直处于只读状态。

3.6：引入r4的表空间

即使用表t的cfg和ibd生成表r4的相关配置和数据，让MySQL系统识别r4。

mysql> alter table r4 import tablespace;
Query OK, 0 rows affected (0.01 sec)
1
2

此时r4就有了和t一样的数据了：

mysql> select count(*) from r4;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

注意：在生产环境执行该操作，最好写成脚本，并反复演练，保证没有问题，因为操作数据还是要十分小心的，一旦执行错命令，可能会造成不可逆的严重后果。

写在后面

参考文章列表：

load data locainfile 加载csv文件 。

05mysql的锁分析 。