mysql只有frm和ibd文件下如何恢复(表引擎为innodb)
环境:
OS:Centos 7
DB:mysql 5.6
说明:主库已经无法启动,表的引擎为innodb
下面操作是在新服务器安装与原库同版本的db下进行
###################已知表结构的情况######################
1.创建同结构的表
CREATE TABLE `ad` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`article_id` bigint(20) DEFAULT NULL COMMENT '文章id',
`ad_name` varchar(100) NOT NULL COMMENT '广告名称',
`picture` varchar(150) NOT NULL COMMENT '图片',
`link_type` int(11) NOT NULL DEFAULT '3' COMMENT '0:外链，1：文章，2：帖子，3：无跳转',
`content_link` varchar(500) DEFAULT NULL COMMENT '内容链接',
`type` int(11) NOT NULL DEFAULT '0' COMMENT '广告类型',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '是否启用：0-启用，1-禁用 默认启用',
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除：0未删除,1删除',
`start_time` datetime NOT NULL COMMENT '开始时间',
`end_time` datetime NOT NULL COMMENT '结束时间',
`sort` int(11) NOT NULL DEFAULT '1' COMMENT '排序权重（越小越靠后）',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4983 DEFAULT CHARSET=utf8mb4;
2.脱离表空间
alter table ad discard tablespace;
执行该命令后,对应的idb文件会自动删除掉
[root@host134 db_ym]# ls -al
total 16
drwx------ 2 mysql mysql 34 May 17 15:13 .
drwxrwxr-x 6 mysql mysql 238 May 17 14:52 ..
-rw-rw---- 1 mysql mysql 8556 May 17 15:12 ad.frm
-rw-rw---- 1 mysql mysql 67 May 17 14:38 db.opt
3.将crash服务器的表的idb文件拷贝到新服务器
cp /tmp/ad.ibd /opt/mysql56/data/db_ym/
chown -R mysql:mysql /opt/mysql56/data/db_ym/
4.加入表空间
alter table ad import tablespace;
5.验证
####################未知表结构的情况########################
1.创建表名一样只有一个字段的表
create table ad(id int);
2.停掉数据库
/opt/mysql56/bin/mysqladmin -h localhost -u root -S /opt/mysql56/data/mysql.sock -P13306 -p shutdown
3.将frm文件和ibd文件拷贝到数据目录
cp /tmp/ad.frm /opt/mysql56/data/db_ym/
cp /tmp/ad.ibd /opt/mysql56/data/db_ym/
4.启动
/opt/mysql56/bin/mysqld_safe --defaults-file=/opt/mysql56/conf/my.cnf --user=mysql &
查看启动日志:
2023-05-17 15:18:38 18214 [Note] Event Scheduler: scheduler thread started with id 1
2023-05-17 15:18:52 18214 [Warning] InnoDB: Table '"db_ym"."ad"' tablespace is set as discarded.
2023-05-17 15:18:52 18214 [Warning] InnoDB: table db_ym/ad contains 1 user defined columns in InnoDB, but 14 columns in MySQL
日志提示新的表只有1列,但是原表有14列
4.删除1列的表
mysql> drop table ad;
Query OK, 0 rows affected (0.05 sec)
相应的删除frm和idb文件(若存在的话)
5.重新创建同名表，保证字段数量为m，与备份表保持一致，然后重新拷贝备份的frm文件到对应目录
create table ad(
id01 int,
id02 int,
id03 int,
id04 int,
id05 int,
id06 int,
id07 int,
id08 int,
id09 int,
id10 int,
id11 int,
id12 int,
id13 int,
id14 int
);
6.拷贝frm文件到数据目录
[root@host134 db_ym]# cp /tmp/ad.frm /opt/mysql56/data/db_ym/
cp: overwrite ‘/opt/mysql56/data/db_ym/ad.frm’? y
chown -R mysql:mysql /opt/mysql56/data/db_ym/
7.重启数据库
加入参数:
innodb_force_recovery=6
/opt/mysql56/bin/mysqladmin -h localhost -u root -S /opt/mysql56/data/mysql.sock -P13306 -p shutdown
/opt/mysql56/bin/mysqld_safe --defaults-file=/opt/mysql56/conf/my.cnf --user=mysql &
登录查看
/opt/mysql56/bin/mysql -h localhost -u root -S /opt/mysql56/data/mysql.sock -P13306 -p
mysql> desc ad;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| article_id | bigint(20) | YES | | NULL | |
| ad_name | varchar(100) | NO | | NULL | |
| picture | varchar(150) | NO | | NULL | |
| link_type | int(11) | NO | | 3 | |
| content_link | varchar(500) | YES | | NULL | |
| type | int(11) | NO | | 0 | |
| status | int(11) | NO | | 0 | |
| is_del | bit(1) | NO | | b'0' | |
| start_time | datetime | NO | | NULL | |
| end_time | datetime | NO | | NULL | |
| sort | int(11) | NO | | 1 | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| modify_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+--------------+------+-----+-------------------+-----------------------------+
14 rows in set (0.00 sec)
这样就可以恢复表结构了.
CREATE TABLE `ad` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`article_id` bigint(20) DEFAULT NULL COMMENT '文章id',
`ad_name` varchar(100) NOT NULL COMMENT '广告名称',
`picture` varchar(150) NOT NULL COMMENT '图片',
`link_type` int(11) NOT NULL DEFAULT '3' COMMENT '0:外链，1：文章，2：帖子，3：无跳转',
`content_link` varchar(500) DEFAULT NULL COMMENT '内容链接',
`type` int(11) NOT NULL DEFAULT '0' COMMENT '广告类型',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '是否启用：0-启用，1-禁用 默认启用',
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除：0未删除,1删除',
`start_time` datetime NOT NULL COMMENT '开始时间',
`end_time` datetime NOT NULL COMMENT '结束时间',
`sort` int(11) NOT NULL DEFAULT '1' COMMENT '排序权重（越小越靠后）',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
8.恢复数据
8.1 停掉数据库,修改配置文件去掉如下参数,然后启动数据库
/opt/mysql56/bin/mysqladmin -h localhost -u root -S /opt/mysql56/data/mysql.sock -P13306 -p shutdown
vi /opt/mysql56/conf/my.cnf
##innodb_force_recovery=6
8.2 删除原来创建的表
drop table db_ym.ad;
8.3重获取到的表结构重新建表
删除掉ibd文件,若存在的话
[root@host134 db_ym]# pwd
/opt/mysql56/data/db_ym
[root@host134 db_ym]# rm ad.ibd
使用获取到的表语句建表
CREATE TABLE `ad` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`article_id` bigint(20) DEFAULT NULL COMMENT '文章id',
`ad_name` varchar(100) NOT NULL COMMENT '广告名称',
`picture` varchar(150) NOT NULL COMMENT '图片',
`link_type` int(11) NOT NULL DEFAULT '3' COMMENT '0:外链，1：文章，2：帖子，3：无跳转',
`content_link` varchar(500) DEFAULT NULL COMMENT '内容链接',
`type` int(11) NOT NULL DEFAULT '0' COMMENT '广告类型',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '是否启用：0-启用，1-禁用 默认启用',
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除：0未删除,1删除',
`start_time` datetime NOT NULL COMMENT '开始时间',
`end_time` datetime NOT NULL COMMENT '结束时间',
`sort` int(11) NOT NULL DEFAULT '1' COMMENT '排序权重（越小越靠后）',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
8.4.脱离表空间
alter table ad discard tablespace;
执行该命令后,对应的idb文件会自动删除掉
[root@host134 db_ym]# ls -al
total 16
drwx------ 2 mysql mysql 34 May 17 15:13 .
drwxrwxr-x 6 mysql mysql 238 May 17 14:52 ..
-rw-rw---- 1 mysql mysql 8556 May 17 15:12 ad.frm
-rw-rw---- 1 mysql mysql 67 May 17 14:38 db.opt
8.5.将crash服务器的表的idb文件拷贝到新服务器
cp /tmp/ad.ibd /opt/mysql56/data/db_ym/
chown -R mysql:mysql /opt/mysql56/data/db_ym/
8.6.加入表空间
mysql> alter table ad import tablespace;
Query OK, 0 rows affected, 1 warning (0.48 sec)
验证数据是否恢复
mysql> select count(1) from ad;
+----------+
| count(1) |
+----------+
| 526 |
+----------+
1 row in set (0.00 sec)
