Recovering MySQL Data from Corrupted Files Using mysqlfrm and Tablespace Import

When a MySQL server fails to start, the data is often still recoverable; for MyISAM tables you can copy the .MYD/.MYI files, while InnoDB tables require tablespace transport.

Install mysql-utilities

On RedHat:

yum -y install mysql-server mysql-utilities

On Debian/Ubuntu:

apt install mysql-utilities

Generate CREATE statements from .frm files using mysqlfrm

Analyze a single .frm file:

mysqlfrm --diagnostic /var/lib/mysql/test/t1.frm

Analyze all .frm files in a directory and save to a script:

mysqlfrm --diagnostic /var/lib/mysql/my_db/bk/ >createtb.sql

Count generated CREATE statements:

grep "^CREATE TABLE" createtb.sql | wc -l

Alternatively, generate CREATE statements with mysqldump (no data, compact):

mysqldump --no-data --compact my_db >createtb.sql

Create a new database and import the generated script

mysql> create database my_db;

mysql> use my_db;

Database changed

mysql> source createtb.sql

Discard existing .ibd files for the tables

List .ibd files and discard each tablespace:

mysql -e "show tables from my_db" | grep -v Tables_in_my_db \

| while read a; do mysql -e "ALTER TABLE my_db.$a DISCARD TABLESPACE"; done

Verify that all .ibd files have been discarded (no .ibd files remain).

Copy the old .ibd files back and import them

Copy the recovered .ibd files into /var/lib/mysql/my_db and adjust ownership:

chown mysql:mysql *.ibd

Import each tablespace:

mysql -e "show tables from my_db" | grep -v Tables_in_my_db \

| while read a; do mysql -e "ALTER TABLE my_db.$a IMPORT TABLESPACE"; done

Verify the import

Run mysqlcheck -c my_db to check all tables; successful output indicates the tables were imported correctly.