由于未关闭mysql服务就将服务器重启,导致服务器无法重启,使用fsck修复磁盘后启动服务器后,发现mysql数据文件损坏和丢失,但还存在idb文件。
数据库版本:5.7.20
什么是idb文件
如何通过idb文件恢复数据
mysql 5.7.20 目前使用的是独立表空间,每一个表都存在独立的ibd文件和frm文件,如下:
Shell
[root@db01 /data/mysql/oldboy]# ll
total 69648
-rw-r----- 1 mysql mysql 61 Mar 8 11:57 db.opt
-rw-r----- 1 mysql mysql 8780 Mar 8 17:50 t1.frm
-rw-r----- 1 mysql mysql 71303168 Mar 8 17:50 t1.ibd
1
2
3
4
5
[root@db01/data/mysql/oldboy]# ll
total69648
-rw-r-----1mysqlmysql61Mar811:57db.opt
-rw-r-----1mysqlmysql8780Mar817:50t1.frm
-rw-r-----1mysqlmysql71303168Mar817:50t1.ibd
准备另外一台服务器用于还原数据,这里采用不同实例来恢复
STEP 1 获取并创建表结构,如果表结构都不知道的话,可以去寻找开发寻求帮助提供表结构语句
MySQL
xad-[oldboy]>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`stuid` int(11) NOT NULL,
`stuname` varchar(20) NOT NULL,
`stusex` char(1) NOT NULL,
`cardid` varchar(20) NOT NULL,
`birthday` datetime DEFAULT NULL,
`entertime` datetime DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`stuid`),
KEY `idx_name` (`stuname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
xad-[oldboy]>showcreatetablet1\G
***************************1.row***************************
Table:t1
CreateTable:CREATETABLE`t1`(
`stuid`int(11)NOT NULL,
`stuname`varchar(20)NOT NULL,
`stusex`char(1)NOT NULL,
`cardid`varchar(20)NOT NULL,
`birthday`datetimeDEFAULTNULL,
`entertime`datetimeDEFAULTNULL,
`address`varchar(100)DEFAULTNULL,
PRIMARY KEY(`stuid`),
KEY`idx_name`(`stuname`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)
STEP 2 在另一台实例上创建库后并运行上面的的建表语句
MySQL
xad-[(none)]>create database ibdtest;
Query OK, 1 row affected (0.00 sec)
xad-[(none)]>use ibdtest;
Database changed
xad-[ibdtest]>CREATE TABLE `t1` (
-> `stuid` int(11) NOT NULL,
-> `stuname` varchar(20) NOT NULL,
-> `stusex` char(1) NOT NULL,
-> `cardid` varchar(20) NOT NULL,
-> `birthday` datetime DEFAULT NULL,
-> `entertime` datetime DEFAULT NULL,
-> `address` varchar(100) DEFAULT NULL,
-> PRIMARY KEY (`stuid`),
-> KEY `idx_name` (`stuname`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
xad-[(none)]>createdatabaseibdtest;
QueryOK,1rowaffected(0.00sec)
xad-[(none)]>useibdtest;
Databasechanged
xad-[ibdtest]>CREATETABLE`t1`(
->`stuid`int(11)NOT NULL,
->`stuname`varchar(20)NOT NULL,
->`stusex`char(1)NOT NULL,
->`cardid`varchar(20)NOT NULL,
->`birthday`datetimeDEFAULTNULL,
->`entertime`datetimeDEFAULTNULL,
->`address`varchar(100)DEFAULTNULL,
->PRIMARY KEY(`stuid`),
->KEY`idx_name`(`stuname`)
->)ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.02sec)
查看此时数据库目录,会生成一个ibdtest库名的目录,目录文件如下
Shell
[root@db01 /data/3307/data]# ll ibdtest/
total 128
-rw-r----- 1 mysql mysql 65 Mar 9 14:15 db.opt
-rw-r----- 1 mysql mysql 8780 Mar 9 14:15 t1.frm
-rw-r----- 1 mysql mysql 114688 Mar 9 14:15 t1.ibd
1
2
3
4
5
[root@db01/data/3307/data]# ll ibdtest/
total128
-rw-r-----1mysqlmysql65Mar914:15db.opt
-rw-r-----1mysqlmysql8780Mar914:15t1.frm
-rw-r-----1mysqlmysql114688Mar914:15t1.ibd
STEP 3 测试将上面目录中的t1.ibd文件直接换为源库中的ibd文件,替换后注意文件权限和属主属组的修改,测试是否能直接恢复数据
Shell
[root@db01 /data/3307/data]# cp /data/mysql/oldboy/t1.ibd ./ibdtest/
cp: overwrite ‘./ibdtest/t1.ibd’? y
[root@db01 /data/3307/data]# ll ibdtest/
total 131024
-rw-r----- 1 mysql mysql 65 Mar 9 14:15 db.opt
-rw-r----- 1 mysql mysql 8780 Mar 9 14:15 t1.frm
-rw-r----- 1 mysql mysql 71303168 Mar 9 14:22 t1.ibd
1
2
3
4
5
6
7
[root@db01/data/3307/data]# cp /data/mysql/oldboy/t1.ibd ./ibdtest/
cp:overwrite‘./ibdtest/t1.ibd’?y
[root@db01/data/3307/data]# ll ibdtest/
total131024
-rw-r-----1mysqlmysql65Mar914:15db.opt
-rw-r-----1mysqlmysql8780Mar914:15t1.frm
-rw-r-----1mysqlmysql71303168Mar914:22t1.ibd
此时是依然看不见原来的数据的
MySQL
xad-[ibdtest]>select count(*) from t1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
xad-[ibdtest]>select count(*)fromt1;
+----------+
|count(*)|
+----------+
|0|
+----------+
1rowinset(0.00sec)
STEP 4 得先清空表的tablespace后再导入源表的ibd文件
MySQL
xad-[ibdtest]>alter table t1 discard tablespace;
Query OK, 0 rows affected (0.02 sec)
[root@db01 /data/3307/data]# cp /data/mysql/oldboy/t1.ibd ./ibdtest/
[root@db01 /data/3307/data]# chown -R mysql.mysql ./*
[root@db01 /data/3307/data]# ll ibdtest/
total 69648
-rw-r----- 1 mysql mysql 65 Mar 9 14:15 db.opt
-rw-r----- 1 mysql mysql 8780 Mar 9 14:15 t1.frm
-rw-r----- 1 mysql mysql 71303168 Mar 9 14:26 t1.ibd
xad-[ibdtest]>alter table t1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.29 sec)
xad-[ibdtest]>select count(*) from t1;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.33 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
xad-[ibdtest]>altertablet1discardtablespace;
QueryOK,0rowsaffected(0.02sec)
[root@db01/data/3307/data]# cp /data/mysql/oldboy/t1.ibd ./ibdtest/
[root@db01/data/3307/data]# chown -R mysql.mysql ./*
[root@db01/data/3307/data]# ll ibdtest/
total69648
-rw-r----- 1 mysql mysql 65 Mar 9 14:15 db.opt
-rw-r----- 1 mysql mysql 8780 Mar 9 14:15 t1.frm
-rw-r----- 1 mysql mysql 71303168 Mar 9 14:26 t1.ibd
xad-[ibdtest]>altertablet1importtablespace;
QueryOK,0rowsaffected,1warning(0.29sec)
xad-[ibdtest]>select count(*)fromt1;
+----------+
|count(*)|
+----------+
|500000|
+----------+
1rowinset(0.33sec)
STEP 5 此时再将表数据导出,之后再导回原库中来恢复数据
xadocker, 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:Mysql 通过ibd文件恢复数据