说明：
本文介绍如何通过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 grepmysql 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=mysqlmysql 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.sockmysql> 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: TABLESPACETABLESPACE_NAME: innodb_file_per_table_42 ENGINE: InnoDB1 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: t1Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `time` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 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 -lrthtotal 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.ibdmysql> 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 disk2024-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 pages2024-06-02T18:41:28.135548+08:00 4 [Note] InnoDB: Sync to disk2024-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 disk2024-06-02T18:41:28.194581+08:00 4 [Note] InnoDB: Phase IV - Flush complete2024-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》
