MySQL数据库如何通过frm、ibd文件恢复表数据？

chenoracle

已于 2024-11-17 12:24:01 修改

阅读量1.1k

点赞数 5

文章标签：数据库 mysql

于 2024-11-17 12:16:16 首次发布

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/143830233

版权

说明：

本文介绍如何通过frm、ibd文件恢复表数据。

环境：

MySQL 5.7.44

方法：

恢复分为两部分，先恢复frm，再恢复ibd。
恢复frm通常有两种方法：
(1)通过mysqlfrm工具，恢复出创建出表结构的语句；
(2)在新库创建一个任意表结构的同名表，通过error.log报错能获得原表列数，在结合原frm文件，可以获得原表创建表结构语句，本次使用采用这种方法。

恢复ibd的方法：

通过表空间的DISCARD和IMPORT，恢复表数据。

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;

测试过程如下：
启动两个测试库，端口分别是3307和3308，模拟3307库故障，如何使用3307库的t1.frm,t2.ibd文件，在3308库恢复t1表的数据。
启动测试MySQL:

[mysql@cjc-db-01 conf]$ /mysqldata/app/5.7.44/bin/mysqld --defaults-file=/mysqldata/3307/conf/my.cnf --user=mysql &
[mysql@cjc-db-01 conf]$ /mysqldata/app/5.7.44_a/bin/mysqld --defaults-file=/mysqldata/3308/conf/my.cnf --user=mysql &

查看进程

[mysql@cjc-db-01 ~]$ ps -ef|grep mysqld|grep -v grep
mysql    10707  2833  0 17:38 pts/0    00:00:01 /mysqldata/app/5.7.44/bin/mysqld --defaults-file=/mysqldata/3307/conf/my.cnf --user=mysql
mysql    11169  2833  3 17:43 pts/0    00:00:00 /mysqldata/app/5.7.44_a/bin/mysqld --defaults-file=/mysqldata/3308/conf/my.cnf --user=mysql

3307库，创建测试数据

[mysql@cjc-db-01 ~]$ /mysqldata/app/5.7.44/bin/mysql -uroot -p --socket=/mysqldata/3307/socket/mysql.sock
mysql> create database cjc;
mysql> use cjc;
mysql> create table t1(id int,name varchar(10),time datetime);
mysql> insert into t1 values(1,'aaa',now());
mysql> insert into t1 values(2,'bbb',now());
mysql> insert into t1 values(3,'ccc',now());
mysql> select * from t1;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    1 | aaa  | 2024-06-02 17:49:01 |
|    2 | bbb  | 2024-06-02 17:50:12 |
|    3 | ccc  | 2024-06-02 17:50:17 |
+------+------+---------------------+
3 rows in set (0.00 sec)

查看t1信息

mysql> select FILE_ID,FILE_NAME,FILE_TYPE,TABLESPACE_NAME,ENGINE from INFORMATION_SCHEMA.files where file_name like '%t1%'\G;
*************************** 1. row ***************************
        FILE_ID: 42
      FILE_NAME: ./cjc/t1.ibd
      FILE_TYPE: TABLESPACE
TABLESPACE_NAME: innodb_file_per_table_42
         ENGINE: InnoDB
1 row in set (0.00 sec)
ERROR: 
No query specified

停库，模拟故障：

[mysql@cjc-db-01 ~]$ /mysqldata/app/5.7.44/bin/mysqladmin -uroot -p shutdown

3308库，登录另一套数据库

[mysql@cjc-db-01 ~]$ /mysqldata/app/5.7.44_a/bin/mysql -uroot -p --socket=/mysqldata/3308/socket/mysql.sock

创建新库yyy,并将3307库的t1表恢复到yyy库里：

mysql> create database yyy;

恢复t1.frm:
新增表t1,表结构任意

mysql> create table t1(ccc int);

将3307库t1.frm文件拷贝到yyy库下

[mysql@cjc-db-01 yyy]$ mv t1.frm t1.frm.bak
[mysql@cjc-db-01 yyy]$ cp /mysqldata/3307/data/cjc/t1.frm .
mysql> show tables;
+---------------+
| Tables_in_yyy |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
mysql> desc t1;
ERROR 1146 (42S02): Table 'yyy.t1' doesn't exist

查看 error.log，通过but 3 columns in MySQL可知，原表有3列：

2024-06-02T18:33:08.660865+08:00 4 [Warning] InnoDB: Table yyy/t1 contains 1 user defined columns in InnoDB, but 3 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2024-06-02T18:33:08.661119+08:00 4 [Warning] InnoDB: Cannot open table yyy/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

重建表t1，3列，列名任意

mysql> drop table t1;
mysql> create table t1(col1 int,col2 int,col3 int);
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  |     | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
| col3  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

将3307库t1.frm文件拷贝到yyy库下

[mysql@cjc-db-01 yyy]$ mv t1.frm t1.frm.bak.1
[mysql@cjc-db-01 yyy]$ cp /mysqldata/3307/data/cjc/t1.frm .
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  |     | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
| col3  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

刷新表

mysql> flush tables;
Query OK, 0 rows affected (0.31 sec)

可以获取到创建原表结构的语句

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| time  | datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

重建表

mysql> drop table t1;
mysql> CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意，需要安装上面的表结构语句重新建表，否则最终恢复数据会出现乱码，例如：

mysql> select * from t1;
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| id   | name                                                                                                                                                      | time                |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|    1 | aa?                                    A. OLUMNS_DATETIME_PRECISION_12 COLUMNS_CHARACTER_SET_NAME_13 CO?l  H1l  ?l                                | 4953-08-29 12:52:00 |
|    2 | bb?                                    .. OLUMNS_DATETIME_PRECISION_12 COLUMNS_CHARACTER_SET_NAME_13 CO?l  H1l  ?l                                 | 0954-00-02 19:12:00 |
|    3 | cc?                                    .. OLUMNS_DATETIME_PRECISION_12 COLUMNS_CHARACTER_SET_NAME_13 CO?l  H1l  ?l                                 | 1347-11-02 19:12:00 |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
3 rows in set (0.00 sec)

恢复t1.ibd:

丢弃表对应的表空间

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.34 sec)

[mysql@cjc-db-01 yyy]$ ls -lrth 
total 40K
-rw-r----- 1 mysql mysql   67 Jun  2 18:23 db.opt
-rw-r----- 1 mysql mysql 8.4K Jun  2 18:31 t1.frm.bak
-rw-r----- 1 mysql mysql 8.5K Jun  2 18:34 t1.frm.bak.1
-rw-r----- 1 mysql mysql 8.5K Jun  2 18:40 t1.frm

拷贝3307库t1.ibd到yyy库

[mysql@cjc-db-01 xxx]$ cp /mysqldata/3307/data/cjc/t1.ibd /mysqldata/3308/data/yyy/
[mysql@cjc-db-01 yyy]$ ls -lrth
total 136K
-rw-r----- 1 mysql mysql   67 Jun  2 18:23 db.opt
-rw-r----- 1 mysql mysql 8.4K Jun  2 18:31 t1.frm.bak
-rw-r----- 1 mysql mysql 8.5K Jun  2 18:34 t1.frm.bak.1
-rw-r----- 1 mysql mysql 8.5K Jun  2 18:40 t1.frm
-rw-r----- 1 mysql mysql  96K Jun  2 18:40 t1.ibd
mysql> select * from t1;
ERROR 1814 (HY000): Tablespace has been discarded for table 't1'

导入表空间

mysql> ALTER TABLE t1 IMPORT TABLESPACE; 
Query OK, 0 rows affected, 1 warning (0.40 sec)

查看warnings;

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './yyy/t1.cfg', will attempt to import without schema verification |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看error.log:

2024-06-02T18:41:27.814983+08:00 4 [Note] InnoDB: Sync to disk
2024-06-02T18:41:28.134973+08:00 4 [Note] InnoDB: Sync to disk - done!
2024-06-02T18:41:28.135125+08:00 4 [Note] InnoDB: Phase I - Update all pages
2024-06-02T18:41:28.135548+08:00 4 [Note] InnoDB: Sync to disk
2024-06-02T18:41:28.152099+08:00 4 [Note] InnoDB: Sync to disk - done!
2024-06-02T18:41:28.163949+08:00 4 [Note] InnoDB: Phase III - Flush changes to disk
2024-06-02T18:41:28.194581+08:00 4 [Note] InnoDB: Phase IV - Flush complete
2024-06-02T18:41:28.194820+08:00 4 [Note] InnoDB: `yyy`.`t1` autoinc value set to 0

查看表数据，恢复成功：

mysql> select * from t1;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    1 | aaa  | 2024-06-02 17:49:01 |
|    2 | bbb  | 2024-06-02 17:50:12 |
|    3 | ccc  | 2024-06-02 17:50:17 |
+------+------+---------------------+
3 rows in set (0.01 sec)

mysql> select * from information_schema.INNODB_SYS_TABLES where name='xxx/t1';
+----------+--------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME   | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------+------+--------+-------+-------------+------------+---------------+------------+
|       46 | xxx/t1 |   33 |      6 |    45 | Barracuda   | Dynamic    |             0 | Single     |
+----------+--------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.INNODB_SYS_TABLESTATS where name='xxx/t1';
+----------+--------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME   | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+--------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       46 | xxx/t1 | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
+----------+--------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
1 row in set (0.00 sec)

参考：

https://www.jb51.net/database/3200900yd.htm

###chenjuchao 20240602###
欢迎关注我的公众号《IT小Chen》