Mysql5.7 Innodb restores data with only FRM and IBD files

I received an urgent task from the leader. There was an error report on the online table. The table file existed but the table could not be typed, but there was no backup, so the routine operation of restoring the backup table could not be done directly. Therefore, you need to recover data with only FRM and IBD files

1. Download MySQL Utilities

Download MySQL Utilities, link: downloads.mysql.com/archives/ut… .

Download mysql connector – python (mysql Utilities rely on mysql connector – python), link: dev.mysql.com/downloads/c… .

RPM: mysql-connector-python-2.1.8-1.el7.x86_64. RPM: mysql-utilities-1.6.5-1.el7.noarch

2. Install MySQL Utilities and mysql-connector-Python

RPM RPM -ivh mysql-connector-python-2.1.8-1.el7.x86_64. RPM RPM -ivh mysql-utilities-1.6.5-1.el7.noarch. RPM

3. View the table construction clauses according to the FRM file

Mysqlfrm — diagnostic. / file directory /sys_dict. FRM

The following is an example:

mysqlfrm --diagnostic /home/liu/sys_dict.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, 
# it may not identify all of the components of the table correctly. This is especially true for damaged files. 
# It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /home/liu/sys_dict.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `liu`.`sys_dict` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `dic_code` varchar(150) NOT NULL comment 'Dictionary code', 
  `dic_key` varchar(300) NOT NULL comment 'a dictionary KEY', 
  `dic_value` varchar(300) NOT NULL comment 'Dictionary value', 
  `dic_type` varchar(300) NOT NULL comment 'Dictionary type,[0]Properties file,[1] Static dictionary,[2]SQL', 
  `dic_type_value` text DEFAULT NULL comment 'Properties file path if dictionary type is 0, null if dictionary type is 1, SQL statement if dictionary type is 2', 
  `ds_key` varchar(150) DEFAULT NULL comment 'Dictionary type 2, non-null, represents ds_key data source where SQL is located', 
  `descr` varchar(300) DEFAULT NULL comment 'Description', 
  `seq` int(11) DEFAULT NULL comment 'Sort number', 
PRIMARY KEY `PRIMARY` (`id`) USING BTREE,
UNIQUE KEY `uk_code_key` (`dic_code`,`dic_key`) USING BTREE
) ENGINE=InnoDB ROW_FORMAT = 2;
Copy the code

4. Create a table from mysqlFRm

CREATE TABLE `sys_dict` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `dic_code` varchar(150) NOT NULL comment 'Dictionary code', 
  `dic_key` varchar(300) NOT NULL comment 'a dictionary KEY', 
  `dic_value` varchar(300) NOT NULL comment 'Dictionary value', 
  `dic_type` varchar(300) NOT NULL comment 'Dictionary type,[0]Properties file,[1] Static dictionary,[2]SQL', 
  `dic_type_value` text DEFAULT NULL comment 'Properties file path if dictionary type is 0, null if dictionary type is 1, SQL statement if dictionary type is 2', 
  `ds_key` varchar(150) DEFAULT NULL comment 'Dictionary type 2, non-null, represents ds_key data source where SQL is located', 
  `descr` varchar(300) DEFAULT NULL comment 'Description', 
  `seq` int(11) DEFAULT NULL comment 'Sort number', 
PRIMARY KEY `PRIMARY` (`id`) USING BTREE,
UNIQUE KEY `uk_code_key` (`dic_code`,`dic_key`) USING BTREE
) ENGINE=InnoDB;
Copy the code

5. Uninstall the created table tablespace and delete ibD files

mysql> alter table sys_dict discard tablespace;

Then replace the IDB file to be restored

6. Load the space of the created table

mysql> alter table sys_dict import tablespace;

At this point, the entire table structure and data recovery is complete