How to Recover MySQL/MariaDB Tables: Step-by-Step Guide

Updated on : May 24, 2025 Category : MySQL

MySQL Table Recovery Process

Lost access to your MySQL or MariaDB tables? Don’t worry-recovery is possible.
This guide walks you through MySQL/MariaDB table recovery using your .ibd and .frm files, referencing official documentation and BaseZap’s own tutorials for deeper insight at each step.

Prerequisites

  • Have the .ibd and .frm files for the affected tables ready.
  • Set up a temporary environment running the same MySQL/MariaDB version as your original database.
  • For official details on InnoDB file migration and mysql mariadb table recovery, see the MySQL documentation.
  • Before you begin, it’s a good idea to make a fresh backup. If you need help, check our guide on how to force a backup in cPanel/WHM.

MySQL Table Recovery Infographic

Quick Reference: The infographic above summarizes the table recovery process at a glance.

Step 1: Verify Database Version

To confirm your MySQL/MariaDB version, run:

cat /path/to/mysql-dir/mysql_upgrade_info

Make sure your recovery environment matches this version to avoid compatibility issues.

If you need to export or import large databases then, our guide on exporting and importing MySQL/MariaDB databases via command line can help you avoid timeouts or performance issues.

Step 2: Recover Table Structure from .frm Files

Use dbsake to extract the table structure from .frm files. This tool lets you retrieve the CREATE TABLE statement directly from a .frm file.

Download and set up dbsake:

curl -s get.dbsake.net > dbsake
chmod u+x dbsake

Extract the CREATE TABLE statement:

./dbsake frmdump table_name.frm > table_name.sql

Repeat for all .frm files you need to recover.

Note: If you can’t extract the structure with dbsake, see Step 6 for an alternative approach.

Also, If you’re working with MyISAM tables and need to recover or recreate MYI files, see our tutorial on how to recover or recreate MYI file for Table in MySQL Database.

Step 3: Create a New Database

With your extracted CREATE TABLE statements, recreate the tables in a new database:

CREATE DATABASE recovered_db;
USE recovered_db;

Then, import each SQL file:

mysql -u root -p recovered_db < table_name.sql

Repeat for all tables you wish to restore.

Step 4: Replace .ibd Files

Log in to MySQL and discard the current tablespace:

mysql -u root -p
USE recovered_db;
ALTER TABLE table_name DISCARD TABLESPACE;

Repeat for each table you are restoring.

Next, copy the .ibd files from your backup into the MySQL data directory:

cp /path/to/backup-dir/*.ibd /path/to/mysql-dir/

Ensure the correct ownership and permissions:

chown mysql:mysql table_name.ibd
chmod 660 table_name.ibd
ls -l

Step 5: Import the Tablespace

After placing the .ibd files, import the tablespace back into MySQL:

ALTER TABLE table_name IMPORT TABLESPACE;

Repeat for all tables you are recovering.

Note: Replace the following placeholders with your actual paths and names:

  • /path/to/mysql-dir/ → The MySQL data directory (e.g., /var/lib/mysql/)
  • /path/to/backup-dir/ → The directory where your backup .ibd files are stored
  • table_name → Use the name of the table being restored
  • recovered_db → Insert the name of the database being recovered

For more on importing tablespaces, see the official MySQL glossary: Import Tablespace.

Step 6: Manually Recover Table Structure (Optional)

If you’re unable to extract the table structure with dbsake, you can use a third-party tool such as Stellar Repair for MySQL.

  1. Download and install the free version of Stellar Repair for MySQL from the official website.
  2. Launch the application and select the database directory path, then click OK.
  3. Select the table you want to restore, then click Properties to view its structure.
  4. Using the available data, manually recreate the table structure and import it into the database as done in Step 3.
For more on MariaDB’s own REPAIR TABLE statement, see the MariaDB knowledge base.
Column Name Data Type Size Allow Nulls Key Name
ID bigint 20 No Primary Key
user_login varchar 240 No Key
user_pass varchar 1020 No
user_nicename varchar 200 No Key
user_email varchar 400 No Key
user_url varchar 400 No
user_registered datetime 19 No
user_activation_key varchar 1020 No
user_status int 11 No
display_name varchar 1000 No

Conclusion

MySQL or MariaDB table recovery using .ibd and .frm files is a practical solution when disaster strikes. By following the steps above and consulting the official documentation when needed, you can restore your data efficiently and with confidence. Always work on a backup, match your MySQL/MariaDB versions, and consider using specialized tools for complex recoveries.

Further Reading