E-mail：chf.dba@gmail.com
Title: MySQL ibd Recovery
Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]
The ibd file is saved in mysql for some reason, but the table has been deleted or the frm file is damaged or the ibdata file is damaged / lost. This article simulates that in this case, ibd file recovery can be completed through mysql’s own technology.
Test environment mysql version
mysql> select version (); + ----------- + | version () | + ----------- + | 5.6.25 | + ----------- + 1 row in set (0.00 sec)
mysql main parameters
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)
The innodb_file_per_table parameter is on to enable each table to store a separate ibd file.The default range of the innodb_force_recovery parameter is 0
Test table situation
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; + ------------ + -------------- + ------ + ----- + -------- -+ ------- + Field | Type | Null | Key | Default | Extra | + ------------ + -------------- + ------ + ----- + -------- -+ ------- + | ID | varchar (255) | NO | PRI | NULL | | | ACCOUNT | varchar (255) | YES | | NULL | | | LifeCycle | int (11) | YES | | NULL | | | Name | varchar (255) | YES | | NULL | | | Password | varchar (255) | YES | | NULL | | | Role | varchar (255) | YES | | NULL | | | UTime | varchar (255) | YES | | NULL | | UserID | varchar (255) | YES | | NULL | | | UserName | varchar (255) | YES | | NULL | | UserStatus | int (11) | YES | | NULL | | + ------------ + -------------- + ------ + ----- + -------- -+ ------- + 10 rows in set (0.05 sec) mysql> select * from user_login limit 1; + ---------------------------------- + --------- + ---- ------- + ----------- + ---------- ------------------------ + ------ + ------------------ --- + -------------------------- -------- + ---------- + ------------ + | ID | ACCOUNT | LifeCycle | Name | Password | Role | UTime | UserID | UserName | UserStatus | + ---------------------------------- + --------- + ---- ------- + ----------- + ---------- ------------------------ + ------ + ------------------ --- + -------------------------- -------- + ---------- + ------------ + | 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | Hu Yuanhui | 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)
Back up ibd files
C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd The volume in drive D has no labels. The serial number of the volume is 4215-1F18 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei 2016-12-02 20:07 98,304 user_login.ibd 1 file 98,304 bytes 0 directories 78,789,591,040 available bytes 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 The volume in drive D has no labels. The serial number of the volume is 4215-1F18 d: \ directory 2016-12-25 23:15 98,304 user_login.ibd 1 file 98,304 bytes 0 directories 78,789,591,040 available bytes
Simulated delete table (ibd file is also deleted)
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 The volume in drive D has no labels. The serial number of the volume is 4215-1F18 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei File not found
Create a new table
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 The volume in drive D has no labels. The serial number of the volume is 4215-1F18 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei 2016-12-25 23:19 98,304 user_login.ibd 1 file 98,304 bytes 0 directories 78,789,591,040 available bytes mysql> select count (*) from xifenfei.user_login; + ---------- + | count (*) | + ---------- + | 0 | + ---------- + 1 row in set (0.00 sec)
Stop mysql and replace user_login.ibd
C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd The volume in drive D has no labels. The serial number of the volume is 4215-1F18 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei 2016-12-25 23:22 98,304 user_login.ibd 1 file 98,304 bytes 0 directories 78,787,141,632 bytes available 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 The volume in drive D has no labels. The serial number of the volume is 4215-1F18 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei 2016-12-02 20:07 98,304 user_login.ibd 1 file 98,304 bytes 0 directories 78,787,141,632 bytes available
Start the mysql service and query the database
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 log error
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
Obviously, because the replaced ibd file and the dictionary information of the page of the ibd file recorded in the database do not match, because the database cannot query the data normally, and mysql directly crashed the instance for security.
Resume operation
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; + ---------------------------------- + --------- + ---- ------- + ----------- + ---------- ------------------------ + ------ + ------------------ --- + -------------------------- -------- + ---------- + ------------ + | ID | ACCOUNT | LifeCycle | Name | Password | Role | UTime | UserID | UserName | UserStatus | + ---------------------------------- + --------- + ---- ------- + ----------- + ---------- ------------------------ + ------ + ------------------ --- + -------------------------- -------- + ---------- + ------------ + | 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | Hu Yuanhui | 698d51a19 d8a121ce581499d7b701668 | | 6 | | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e d5cfee8 | NULL | 1 | + ---------------------------------- + --------- + ---- ------- + ----------- + ---------- ------------------------ + ------ + ------------------ --- + -------------------------- -------- + ---------- + ------------ + 1 row in set (0.00 sec)
After the discard tablespace and import tablespace operations provided by mysql, the table data can be completed.
mysql log
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
The mysql log still reports that the page dictionary information does not match. But the data is already accessible, you can re-create the table through mysqldump export. If this method cannot be restored due to ibd damage, please refer to: MySQL drop database recovery (the recovery method is also applicable to MySQL drop table, delete, truncate table)