系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

MySQL使用物理方式快速恢复单表

时间:2022-12-15来源:www.pcxitongcheng.com作者:电脑系统城

  • GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
  • GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。

使用方法

1、首先创建一个测试表test1,并插入几条数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create table test1 (id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into test1 (name) values ('张三'),('李四'),('王二');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from test1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

2、创建目标表test2:

1
2
3
4
5
mysql> create table test2 like test1;
Query OK, 0 rows affected (0.10 sec)
查看数据目录里面的ibd文件(test2.ibd、test1.ibd):
-rw-r-----. 1 * * 114688 Nov  2 16:20 test1.ibd
-rw-r-----. 1 * * 114688 Nov  2 16:23 test2.ibd

3、通过alter table discard的方法丢弃表test2的idb文件(为下一步复制test1的数据过来做准备):

1
2
3
4
5
6
mysql> alter table test2 discard tablespace;  
Query OK, 0 rows affected (0.02 sec)
 
查看ibd文件情况,发现test2的ibd文件已经被删除
 
-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

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

1
2
3
4
5
6
7
mysql> flush table test1 for export;
Query OK, 0 rows affected (0.00 sec)
 
生成了一个test1.cfg的cfg文件
 
-rw-r----- 1 * *    655 Nov  2 16:25 test1.cfg
-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

5、拷贝源表test1的cfg文件和ibd文件到目标表test2,并修改文件权限:

1
2
3
cp test1.cfg test2.cfg
cp test1.ibd test2.ibd
chown -R mysql.mysql test2.*

6、复制完成之后,执行select命令发现出现以下报错:

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

7、执行unlock tables,释放源表的test1.cfg文件,然后导入ibd文件:

1
2
3
4
5
6
7
8
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
并用alter table的方法为目标表test2导入这个ibd文件:
 
mysql> alter table test2 import tablespace;
Query OK, 0 rows affected (0.03 sec)
1 row in set (0.00 sec)

8、再次执行select,发现数据已经导入:

1
2
3
4
5
6
7
8
9
mysql> select * from test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

物理复制方法介绍

上述单表物理复制的方法,核心在于cp命令,因为是通过物理拷贝,所以如果复制的表非常大,那么通过物理拷贝,就会比逻辑上的SQL写入快很多,比如insert into select语句。

简单总结一下上述物理复制过程:

  • 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的文件
  • 5、处理完表复制后,需要使用UNLOCK tables释放源表的MDL锁或者断开连接。

注意:

因为alter table for export锁表,所以这种方法更适合在从库上停掉复制关系,然后执行这个表复制的操作。如果有业务操作当前的源表,需要谨慎考虑。

Enjoy GreatSQL

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载