说明：
本文介绍如何通过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,表结构任意
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; 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)
丢弃表对应的表空间
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 [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》
