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.