资讯

展开

一种快速复制单表的方法

作者:快盘下载 人气:

//

一种快速复制mysql单表的方法

//

01

复制MySQL单表的方法

作为MySQL DBA,在日常运维过程中,经常需要对某张表进行备份恢复。单个表常用的数据备份方法有下面几种:

1、mysqldump或者mysqlpump原生工具,通常情况下,可以通过--database和--tables选项来过滤想要的表。然后通过mysql命令行或者source 指令来恢复表结构。

2、通过select into outfile xxx 的方法来导出表的数据,然后使用load data的方式将表恢复到另外一个表里面。

3、insert into tbl_B select * from tbl_A的方法

今天,我们来看另外一种物理复制的方法。

02

利用物理复制的方法复制一张表的数据

下面的例子,演示从表src到表dst的数据复制过程(基于MySQL 8.0.24)

1、首先我们创建一个表src,并插入几条数据:

mysql> create table src (id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into src (name) values ('zhangsan'),('lisi'),('wangwu');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from src;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

2、然后我们创建目标表dst:

mysql> create table dst like src;
Query OK, 0 rows affected (0.10 sec)

查看数据目录里面的数据(dst.ibd、src.ibd):

root@4a1214a30f43:/var/lib/mysql/test# ls -l
total 816
-rw-r----- 1 mysql mysql 114688 Jun  7 15:17 dst.ibd
-rw-r----- 1 mysql mysql 114688 Jun  7 15:16 src.ibd

3、我们通过alter table discard的方法丢弃表dst的idb文件(这一步的目的是为了后面复制src的数据过来):

mysql> alter table dst discard tablespace;   
Query OK, 0 rows affected (0.02 sec)

查看ibd文件情况,发现dst的ibd文件已经被删除
root@4a1214a30f43:/var/lib/mysql/test# ls -l
total 736
-rw-r----- 1 mysql mysql 114688 Jun  7 15:16 src.ibd

4、执行下面的命令,生成一个src的cfg文件,如下:

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

生成了一个src.cfg的cfg文件
root@4a1214a30f43:/var/lib/mysql/test# ls -l
total 740
-rw-r----- 1 mysql mysql    655 Jun  7 15:18 src.cfg
-rw-r----- 1 mysql mysql 114688 Jun  7 15:16 src.ibd

5、然后我们拷贝源表src的cfg文件和ibd文件到目标表dst,命令如下:

cp src.cfg dst.cfg
cp src.ibd dst.ibd

6、复制完成之后,其实这个目标表dst还是不能查询的,会报错,报错的结果如下:

mysql> select * from dst;
ERROR 1100 (HY000): Table 'dst' was not locked with LOCK TABLES

7、然后我们执行unlock tables,释放源表的src.cfg文件,

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

并用alter table的方法为目标表dst导入这个ibd文件:

mysql> alter table dst import tablespace; 
ERROR 1812 (HY000): Tablespace is missing for table `test`.`dst`.
mysql> 

报错不难看出来,找不到文件,权限问题,修复方法如下:
root@4a1214a30f43:/var/lib/mysql/test# ls -l
total 820
-rw-r----- 1 root  root     655 Jun  7 15:19 dst.cfg
-rw-r----- 1 root  root  114688 Jun  7 15:19 dst.ibd
-rw-r----- 1 mysql mysql 114688 Jun  7 15:16 src.ibd

root@4a1214a30f43:/var/lib/mysql/test# chown mysql.mysql dst.*
root@4a1214a30f43:/var/lib/mysql/test# ls -l
total 852
-rw-r----- 1 mysql mysql    655 Jun  7 15:19 dst.cfg
-rw-r----- 1 mysql mysql 114688 Jun  7 15:21 dst.ibd
-rw-r----- 1 mysql mysql 114688 Jun  7 15:16 src.ibd

修复后重新导入ibd文件,成功:
mysql> alter table dst import tablespace;
Query OK, 0 rows affected (0.06 sec)

8、查询最终的结果,发现数据已经导入:

mysql> select * from dst;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

03

物理复制方法介绍

上面的操作,过程看着有点复杂,这里简单解释一下,你可能会觉得复制一张表干嘛这么复杂?直接insert into select 语句,执行就完事儿了,但是这种语句本身是逻辑的SQL语句,如果要复制的表,本身比较大,这条SQL可能执行的时间很长。

上述物理复制的方法,核心在于中间的cp命令,它的本质是物理拷贝,如果某个表非常大,那么这个物理拷贝,就比逻辑上的SQL写入快很多。

关于上述物理复制过程,我简单总结一下:

1、create table like语法创建一个相同表结构的空的目标表

2、目标表执行alter table discard语法,丢弃ibd文件

3、源表执行alter table for export语法,生成.cfg文件

4、使用cp命令复制源表cfg文件和ibd文件为目标表

5、unlock tables 释放源表的cfg文件

6、alter table import命令导入目标表的ibd数据文件即可。

这里,对alter table for export这个语法做个介绍:

1、这个命令是为了将内存中关于这个表的数据刷新到磁盘上,确保数据都能被binlog所记录;

2、这个操作需要flush table或者reload权限;

3、这个操作会持有当前表的共享MDL锁,阻止其他会话修改表结构,在FOR EXPORT操作完成时不会释放先前获取的MDL锁,需要手工释放

4、InnoDB会在与该表相同的数据库目录中生成一个名为table_name.cfg的文件,.cfg文件包含了当前表的元信息,如果你使用cat命令,可以看到:

root@4a1214a30f43:/var/lib/mysql/test# cat src.cfg
4a1214a30f43    test/src@!@!id
                             Pname
DB_ROW_ID
DB_TRX_ID
        DB_ROLL_PTR
                   CLUST_IND_SDI+y-id
DB_TRX_ID
         DB_ROLL_PTRcompressed_lenuncompressed_lendataPRIMARYid
DB_TRX_ID
         DB_ROLL_PTRname

5、处理完表复制后,需要使用UNLOCK tables释放源表的MDL锁。

注意:

因为存在锁表的情况,所以这种方法更适合在从库上停掉复制关系,然后执行这个表复制的操作。如果有业务操作当前的源表,请慎用该方法。

加载全部内容

相关教程
猜你喜欢
用户评论
快盘暂不提供评论功能!