MySQL ibdata损坏或丢失 通过frm&ibd文件恢复数据

MySQL ibdata损坏或丢失 通过frm&ibd文件恢复数据

环境:
db1:3306 /opt/vm-mysql/data1/test/t.ibd
db2:3316 /opt/vm-mysql/data2/recovery_test/t.ibd
innodb_file_per_table=1

1.在db1下3306，准备需要恢复的数据
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table t(id int not null auto_increment,content char(1) not null,primary key(id))engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t(content)values('1'),('2'),('3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----+---------+
| id | content |
+----+---------+
|  1 | 1       |
|  2 | 2       |
|  3 | 3       |
+----+---------+
3 rows in set (0.00 sec)

mysql> exit
Bye
停止db1 MySQLd，service mysql stop

查看需要恢复/opt/vm-mysql/data1/test/t.ibd表信息
vim -b /opt/vm-mysql/data1/test/t.ibd
0000000: 7789 4554 0000 0000 0000 0000 0000 0000  w.ET............
0000010: 0000 0000 0000 4a72 0008 0000 0000 0000  ......Jr........
0000020: 0000 0000 0002 0000 0002 0000 0000 0000  ................  #需要修改行
0000030: 0006 0000 0040 0000 0000 0000 0004 0000  .....@..........
0000040: 0000 ffff ffff 0000 ffff ffff 0000 0000  ................
0000050: 0001 0000 0000 009e 0000 0000 009e 0000  ................
0000060: 0000 ffff ffff 0000 ffff ffff 0000 0000  ................
0000070: 0000 0000 0003 0000 0000 ffff ffff 0000  ................
0000080: ffff ffff 0000 0000 0001 0000 0002 0026  ...............&
0000090: 0000 0002 0026 0000 0000 0000 0000 ffff  .....&..........
00000a0: ffff 0000 ffff ffff 0000 0000 0002 aaff  ................
00000b0: ffff ffff ffff ffff ffff ffff ffff 0000  ................
.......................
..............................
0000270: 0000 0000 0000 0000 0000 0000 0000 0000  ................
0000280: 0000 0000 0000 0000 0000 0000 0000 0000  ................
0000290: 0000 0000 0000 0000 0000 0000 0000 0000  ................
:%!xxd  #注意是以16进制查看此行:0000020,是我们需要数据:
0000020: 0000 0000 0002 0000 0002 0000 0000 0000  ................
#假设db1中ibdata损坏,恢复/opt/vm-mysql/data1/test/t.ibd中数据

2.在db2下3316数据恢复
mysql> create database recovery_test;
Query OK, 1 row affected (0.00 sec)
mysql> use recovery_test;
Database changed
#新建相同表结构空表
mysql> create table t(id int not null auto_increment,content char(1) not null,primary key(id))engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

同样方法查看刚建表/opt/vm-mysql/data2/recovery_test/t.ibd
vim -b /opt/vm-mysql/data2/recovery_test/t.ibd
0000000: 5e24 125e 0000 0000 0000 0000 0000 0000  ^$.^............
0000010: 0000 0000 0000 443d 0008 0000 0000 0000  ......D=........
0000020: 0000 0000 003b 0000 003b 0000 0000 0000  .....;...;......
0000030: 0006 0000 0040 0000 0000 0000 0004 0000  .....@..........
0000040: 0000 ffff ffff 0000 ffff ffff 0000 0000  ................
0000050: 0001 0000 0000 009e 0000 0000 009e 0000  ................
0000060: 0000 ffff ffff 0000 ffff ffff 0000 0000  ................
0000070: 0000 0000 0003 0000 0000 ffff ffff 0000  ................
0000080: ffff ffff 0000 0000 0001 0000 0002 0026  ...............&
0000090: 0000 0002 0026 0000 0000 0000 0000 ffff  .....&..........
00000a0: ffff 0000 ffff ffff 0000 0000 0002 aaff  ................
00000b0: ffff ffff ffff ffff ffff ffff ffff 0000  ................
.........................
.................................
0000280: 0000 0000 0000 0000 0000 0000 0000 0000  ................
0000290: 0000 0000 0000 0000 0000 0000 0000 0000  ................
:%!xxd 
查看0000020获得003b，故把需要恢复表/opt/vm-mysql/data1/test/t.ibd中0000020
行0002修改为/opt/vm-mysql/data2/recovery_test/t.ibd:003b
修改之前，把/opt/vm-mysql/data2/recovery_test/t.ibd coping到其他目录下,
假设, cp -rp /opt/vm-mysql/data2/recovery_test/t.ibd  /opt/vm-mysql/
vim -b /opt/vm-mysql/t.ibd
:%!xxd #转换成16进制
:i #修改对应行数据(0002=>003b)
:!xxd -r

:x #保存退出

--4步.

把修改好/opt/vm-mysql/t.ibd copying到/opt/vm-mysql/data2/recovery_test/t.ibd 覆盖

cp -rp /opt/vm-mysql/t.ibd /opt/vm-mysql/data2/recovery_test/
cp: overwrite `/opt/vm-mysql/data/recovery_test/t.ibd'? y #enter

vi db2 my.cnf 添加innodb_force_recovery=6(仅有读权限),避免重启MySQLd时,提示表损坏,无法打开.

重启 db2 mysqld

连上db2 mysqld，查验是否恢复:
mysql> use recovery_test;
mysql> select * from t;
+----+---------+
| id | content |
+----+---------+
|  1 | 1       |
|  2 | 2       |
|  3 | 3       |
+----+---------+
3 rows in set (0.00 sec)

在db2下查得数据已经恢复，表示恢复成功.
这时mysqldump该表数据，修改db2 my.cnf innodb_force_recovery=0 或注释该参数

到此，因ibdata损坏，通过表名.ibd恢复数据完成(还有一种通过,discard tablespace,import tablespace,加载数据字典方法,没有去验证).

追加一个:
2.找回表结构(*.ibd丢失，只有*.frm文件)
a.创建空数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
b.创建和需要恢复表一样表名，其字段无所谓
mysql> create table product(id int)engine=InnoDB charset=utf8;
Query OK, 0 rows affected (0.02 sec)
c.关闭MySQLd 
d.用需要恢复表frm替换刚新建表(a)frm文件
[root@vm-mysql test1]# cp -rp product.frm ../test
cp: overwrite `../test/product.frm'? y
e.在my.cnf增加,innodb_force_recovery=4(5,6已可以)，启动MySQLd
f.获取需要恢复表的结构
mysql> show create table product \G;
*************************** 1. row ***************************
       Table: product
Create Table: CREATE TABLE `product` (
  `PRODUCT_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `BRAND_ID` int(10) unsigned DEFAULT NULL,
  `PRODUCT_TYPE_ID` int(10) unsigned DEFAULT NULL,
  `GROUP_ID` int(10) unsigned DEFAULT NULL,
  `PRODUCT_NAME` varchar(500) NOT NULL,
  `DEFAULT_EMAIL_ID` varchar(48) DEFAULT NULL,
  `PRODUCT_STATUS` tinyint(1) NOT NULL,
  `CLIENT_ID` bigint(20) unsigned DEFAULT NULL,
  `LAST_MODIFIED_BY` varchar(45) NOT NULL,
  `LAST_MODIFIED_DATE` datetime NOT NULL,
  PRIMARY KEY (`PRODUCT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified
通过(a-f)找回product表结构

参考:http://www.linuxidc.com/Linux/2012-02/54122p3.htm