您的位置: 首页  >  文章  >  Mysql通过数据文件恢复表数据

Mysql通过数据文件恢复表数据

分类: 文章 • 2024-09-21 21:14:22

目标

通过数据文件把一个表的数据从一个库（源库）恢复（迁移）到另一个库（目标库），源库和目标库可以在相同的server，也可以在不同的server。

数据库版本

Mysql通过数据文件恢复表数据

数据库引擎

Mysql通过数据文件恢复表数据

前提条件

目标端数据库的表结构需要跟源端一样：
源库：

mysql> desc tmp1;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | MUL | NULL | |
±------±------------±-----±----±--------±------+

目标库：

mysql> desc tmp1;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±------+

步骤

  • 在源端数据插入数据，并创建索引（注意这个索引是建在字段name上）- 此步可以跳过，主要为了演示这种方式只迁移(恢复)表数据，不迁移(恢复)表结构：

mysql> create index idx_tmp1name on tmp1(name);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tmp1(id,name) values(222,‘数据’);
Query OK, 1 row affected (0.05 sec)
mysql> select * from tmp1;
±-----±-------------+
| id | name |
±-----±-------------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 1 | test_illegal |
| 0 | test_illegal |
| 0 | test_illega2 |
| 111 | 情况 |
| 222 | 数据 |
±-----±-------------+
8 rows in set (0.00 sec)
mysql> show index from tmp1;
±------±-----------±-------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±------±-----------±-------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| tmp1 | 1 | idx_tmp1name | 1 | name | A | 7 | NULL | NULL | YES | BTREE | | |
±------±-----------±-------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
1 row in set (0.00 sec)

  • 在目标端discard该表的表空间：

mysql> alter table tmp1 discard tablespace;
Query OK, 0 rows affected (0.07 sec)
mysql> desc tmp1;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(32) | YES | | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)
mysql> select * from tmp1;
ERROR 1814 (HY000): Tablespace has been discarded for table ‘tmp1’

  • 查看表tmp1数据文件，发现tmp1.ibd已经不存在了，只有表结构文件：
    Mysql通过数据文件恢复表数据
  • 从源端拷贝该表的数据文件(tmp1.idb)到目标端相同的目录，修改文件属主和权限：

[[email protected] oriontest]# ll
total 548
-rw-r-----. 1 mysql mysql 61 Apr 16 08:09 db.opt
-rw-r-----. 1 mysql mysql 8586 May 15 02:02 tmp1.frm
-rwx------. 1 root root 114688 May 15 06:53 tmp1.ibd
-rw-r-----. 1 mysql mysql 8586 May 13 02:13 tmp2.frm
-rw-r-----. 1 mysql mysql 98304 May 13 15:03 tmp2.ibd
-rw-r-----. 1 mysql mysql 8586 May 13 10:16 tmp3.frm
-rw-r-----. 1 mysql mysql 98304 May 13 10:16 tmp3.ibd
-rw-r-----. 1 mysql mysql 8620 Apr 20 07:01 tmp_par.frm
-rw-r-----. 1 mysql mysql 98304 Apr 20 06:53 tmp_par#P#p1001.ibd
-rw-r-----. 1 mysql mysql 98304 Apr 20 07:01 tmp_par#P#p1002.ibd
[[email protected] oriontest]# chown mysql:mysql tmp1.ibd
[[email protected] oriontest]# chmod 640 tmp1.ibd
[[email protected] oriontest]# ll
total 548
-rw-r-----. 1 mysql mysql 61 Apr 16 08:09 db.opt
-rw-r-----. 1 mysql mysql 8586 May 15 02:02 tmp1.frm
-rw-r-----. 1 mysql mysql 114688 May 15 06:53 tmp1.ibd
-rw-r-----. 1 mysql mysql 8586 May 13 02:13 tmp2.frm
-rw-r-----. 1 mysql mysql 98304 May 13 15:03 tmp2.ibd
-rw-r-----. 1 mysql mysql 8586 May 13 10:16 tmp3.frm
-rw-r-----. 1 mysql mysql 98304 May 13 10:16 tmp3.ibd
-rw-r-----. 1 mysql mysql 8620 Apr 20 07:01 tmp_par.frm
-rw-r-----. 1 mysql mysql 98304 Apr 20 06:53 tmp_par#P#p1001.ibd
-rw-r-----. 1 mysql mysql 98304 Apr 20 07:01 tmp_par#P#p1002.ibd

  • 重新导入该表的表空间：

mysql> alter table tmp1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.23 sec)

  • 这样数据就恢复了：

mysql> select * from tmp1;
±-----±-------------+
| id | name |
±-----±-------------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 1 | test_illegal |
| 0 | test_illegal |
| 0 | test_illega2 |
| 111 | 情况 |
| 222 | 数据 |
±-----±-------------+
8 rows in set (0.00 sec)

备注：这种方式只恢复表的数据，不恢复表的结构，包括索引：
源库的索引建在name上，目标库的索引建在id上

mysql> show index from tmp1;
±------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| tmp1 | 1 | idn_tmp1id | 1 | id | A | 6 | NULL | NULL | YES | BTREE | | |
±------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
1 row in set (0.00 sec)

相关推荐