mysql異常恢復工具_[MySQL異常恢復]mysql ibd文件恢復

在mysql中由于某種原因保存有ibd文件，但是表已經被刪除或者frm文件損壞亦或者ibdata文件損壞/丟失等。本文模擬在這種情況下，通過mysql自身技術即可完成ibd文件恢復.

測試環境mysql版本

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.25 |

+-----------+

1 row in set (0.00 sec)

mysql主要參數

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'innodb_force_recovery';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_force_recovery | 0 |

+-----------------------+-------+

1 row in set (0.00 sec)

innodb_file_per_table這個參數為on才能夠實現每個表存儲單獨的ibd文件.innodb_force_recovery參數默認范圍0

測試表情況

mysql> use xifenfei;

Database changed

mysql> show tables;

+-----------------------------+

| Tables_in_xifenfei |

+-----------------------------+

| user_login |

+-----------------------------+

1 rows in set (0.00 sec)

mysql> select count(*) from user_login;

+----------+

| count(*) |

+----------+

| 48 |

+----------+

1 row in set (0.02 sec)

mysql> desc user_login;

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| ID | varchar(255) | NO | PRI | NULL | |

| LifeCycle | int(11) | YES | | NULL | |

| UserStatus | int(11) | YES | | NULL | |

+------------+--------------+------+-----+---------+-------+

10 rows in set (0.05 sec)

mysql> select * from user_login limit 1;

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| Role | UTime | UserID

| UserName | UserStatus |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | 胡元會 | 698d51a19

d8a121ce581499d7b701668 | |6| | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e

d5cfee8 | NULL | 1 |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

1 row in set (0.00 sec)

mysql> show create table user_login \G;

*************************** 1. row *************

Table: user_login

Create Table: CREATE TABLE `user_login` (

`ID` varchar(255) NOT NULL,

`ACCOUNT` varchar(255) DEFAULT NULL,

`LifeCycle` int(11) DEFAULT NULL,

`Name` varchar(255) DEFAULT NULL,

`Password` varchar(255) DEFAULT NULL,

`Role` varchar(255) DEFAULT NULL,

`UTime` varchar(255) DEFAULT NULL,

`UserID` varchar(255) DEFAULT NULL,

`UserName` varchar(255) DEFAULT NULL,

`UserStatus` int(11) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> show variables like 'datadir';

+---------------+-----------------------------------------------+

| Variable_name | Value |

+---------------+-----------------------------------------------+

| datadir | D:\xifenfei\mysql-5.6.25-winx64\data\ |

+---------------+-----------------------------------------------+

1 row in set (0.00 sec)

備份ibd文件

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅動器 D 中的卷沒有標簽。

卷的序列號是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-02 20:07 98,304 user_login.ibd

1 個文件 98,304 字節

0 個目錄 78,789,591,040 可用字節

C:\Users\XIFENFEI>cp D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd d:/

C:\Users\XIFENFEI>dir d:\user_login.ibd

驅動器 D 中的卷沒有標簽。

卷的序列號是 4215-1F18

d:\ 的目錄

2016-12-25 23:15 98,304 user_login.ibd

1 個文件 98,304 字節

0 個目錄 78,789,591,040 可用字節

模擬刪除表(ibd文件也被刪除)

mysql> drop table xifenfei.user_login;

Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅動器 D 中的卷沒有標簽。

卷的序列號是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

找不到文件

創建新表

mysql> CREATE TABLE `user_login` (

-> `ID` varchar(255) NOT NULL,

-> `ACCOUNT` varchar(255) DEFAULT NULL,

-> `LifeCycle` int(11) DEFAULT NULL,

-> `Name` varchar(255) DEFAULT NULL,

-> `Password` varchar(255) DEFAULT NULL,

-> `Role` varchar(255) DEFAULT NULL,

-> `UTime` varchar(255) DEFAULT NULL,

-> `UserID` varchar(255) DEFAULT NULL,

-> `UserName` varchar(255) DEFAULT NULL,

-> `UserStatus` int(11) DEFAULT NULL,

-> PRIMARY KEY (`ID`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅動器 D 中的卷沒有標簽。

卷的序列號是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-25 23:19 98,304 user_login.ibd

1 個文件 98,304 字節

0 個目錄 78,789,591,040 可用字節

mysql> select count(*) from xifenfei.user_login;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

停掉mysql,替換user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅動器 D 中的卷沒有標簽。

卷的序列號是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-25 23:22 98,304 user_login.ibd

1 個文件 98,304 字節

0 個目錄 78,787,141,632 可用字節

C:\Users\XIFENFEI>cp d:\user_login.ibd D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd

驅動器 D 中的卷沒有標簽。

卷的序列號是 4215-1F18

D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目錄

2016-12-02 20:07 98,304 user_login.ibd

1 個文件 98,304 字節

0 個目錄 78,787,141,632 可用字節

啟動mysql 服務,查詢數據庫

mysql> select count(*) from xifenfei.user_login;

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> exit

Bye

C:\Users\XIFENFEI>mysql -uroot

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

mysql 日志報錯

2016-12-25 23:31:07 11632 [Note] MySQL: ready for connections.

Version: '5.6.25' socket: '' port: 3306 MySQL Community Server (GPL)

InnoDB: Error: tablespace id is 56 in the data dictionary

InnoDB: but in file .\xifenfei\user_login.ibd it is 47!

2016-12-25 23:31:31 2eb8 InnoDB: Assertion failure in thread 11960 in file fil0fil.cc line 796

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

很明顯由于替換的ibd文件和現在數據庫記錄的ibd文件的page的字典信息不匹配，因為數據庫無法正常查詢該數據，而且mysql為了安全直接把實例給crash了.

恢復操作

mysql> show variables like 'innodb_force_recovery';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_force_recovery | 1 |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> alter table xifenfei.user_login discard tablespace;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> alter table xifenfei.user_login import tablespace;

Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select count(*) from xifenfei.user_login;

+----------+

| count(*) |

+----------+

| 48 |

+----------+

1 row in set (0.00 sec)

mysql> select * from xifenfei.user_login limit 1;

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| Role | UTime | UserID

| UserName | UserStatus |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

| 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | 胡元會 | 698d51a19

d8a121ce581499d7b701668 | |6| | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e

d5cfee8 | NULL | 1 |

+----------------------------------+---------+-----------+-----------+----------

------------------------+------+---------------------+--------------------------

--------+----------+------------+

1 row in set (0.00 sec)

通過mysql自帶的discard tablespace和import tablespace操作后,表數據已經可以完成查詢了.

mysql日志

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Failed to find tablespace for table '"xifenfei"."user_login"' in the cache. Attempting to load the tablespace with space id 56.

2016-12-25 23:34:08 10464 [ERROR] InnoDB: In file '.\xifenfei\user_login.ibd', tablespace id and flags are 47 and 0, but in the InnoDB data dictionary they are 56 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei/user_login'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-12-25 23:34:08 30e8 InnoDB: cannot calculate statistics for table "xifenfei"."user_login" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.

2016-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!

2016-12-25 23:34:41 10464 [Note] InnoDB: Phase I - Update all pages

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk

2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!

2016-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 56

2016-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 56

2016-12-25 23:34:41 10464 [Note] InnoDB: Phase III - Flush changes to disk

2016-12-25 23:34:41 10464 [Note] InnoDB: Phase IV - Flush complete

mysql日志依舊報了page字典信息不匹配.但是數據已經可以訪問,通過mysqldump導出重新創建表即可.如果由于ibd損壞使用該方法無法恢復,請參考:MySQL drop database恢復(恢復方法同樣適用MySQL drop table,delete,truncate table)