DEV Community

Cover image for How to Recover MySQL Data from a .ibd File
Eazybright😊😊
Eazybright😊😊

Posted on

How to Recover MySQL Data from a .ibd File

#mysql #backup #webdev #bash

On a fateful day, you logged into your computer preparing to start the day's work but alas upon opening your MySql work tools you couldn't login due to error:

Error 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (61)
Enter fullscreen mode Exit fullscreen mode

This error means that MySQL is installed but not running or listening on the port 3306. If you use HomeBrew, you would have ran the command brew services list to see what is going on.

I ran the command below to see if MySQL is listening on TCP but nothing showed up:

lsof -nP -iTCP:3306 | grep LISTEN
Enter fullscreen mode Exit fullscreen mode

After several debugging with no fix, I concluded that MySQL was corrupted and a fresh installation was necessary. But how do I recover my data?

First and foremost, backup your .ibd file as you still have the grace to restore them as we proceed in this article.
.ibd files are per-table tablespace files which contains the actual data and indexes for a single InnoDB table. They are binary files which can not be read easily.

Step 1: Backup your data

For Homebrew-installed MySQL, the default data directory is:

/usr/local/var/mysql.     # For Intel chips

/opt/homebrew/var/mysql    # For Apple Silicon chips
Enter fullscreen mode Exit fullscreen mode

There are different files in the data directory: ibdata1, ib_logfile0, mysql.ibd, your_database_name/.
Each database is a folder, e.g.:

/opt/homebrew/var/mysql/your_database_name/
/opt/homebrew/var/mysql/your_database_name/your_table_name.ibd
Enter fullscreen mode Exit fullscreen mode

Run (add sudo if necessary):

cp -r /opt/homebrew/var/mysql/your_database_name /opt/homebrew/var/backup_mysql/your_database_name
Enter fullscreen mode Exit fullscreen mode

You should see a new directory /opt/homebrew/var/backup_mysql with the copied data intact.

Step 2: Uninstall Mysql

Stop the service:

brew services stop mysql // or mysql@8.* if you are using a specific version
Enter fullscreen mode Exit fullscreen mode

Uninstall:

brew uninstall mysql
Enter fullscreen mode Exit fullscreen mode

Remove MySQL configuration data (ensure you've backed up before doing this):

rm -rf /opt/homebrew/var/mysql
rm -rf /opt/homebrew/etc/my.cnf
rm -rf /opt/homebrew/etc/my.cnf.d
Enter fullscreen mode Exit fullscreen mode

Step 3: Install New Mysql 

brew update
brew install mysql
brew link mysql --force # link the MySQL binary
Enter fullscreen mode Exit fullscreen mode

Start MySQL as a background service:

brew services start mysql
Enter fullscreen mode Exit fullscreen mode

Check the status to ensure it has started successfully:

brew services list
Enter fullscreen mode Exit fullscreen mode 
Name    Status    User    File
mysql   started   root    ~Library/LaunchAgents/homebrew.mxcl.mysql.plist
Enter fullscreen mode Exit fullscreen mode

Step 4: Restore Data to MySQL

I found this awesome tool that parses mysql .ibd file to sql
ibd2sql.

Clone the repo into your device:

git clone https://github.com/ddcw/ibd2sql.git

cd ibd2sql

// open the code with your favorite editor
Enter fullscreen mode Exit fullscreen mode

This tool requires python3 and supports mysql versions 5, 8 & 9.

If your database has large tables, I wrote a bash script to automate things without having to run the .ibd recovery command one after the other as it is per table basis.

At the root directory, create a new bash file restore_data.sh and paste the below code:

#!/bin/bash

set -e

DB_NAME="your_database_name"
MYSQL_USER="root"
MYSQL_PASSWORD= # if you've protected the new MySql installation with a password, you have to provide one
IBD_DIR=~/opt/homebrew/var/backup_mysql/your_database_name             # Folder where all .ibd backup files are located (refer to step 1 above)

mysql -u root -e "CREATE DATABASE IF NOT EXISTS $DB_NAME; SET FOREIGN_KEY_CHECKS=0;"

# Find all .ibd files in the provided directory
for IBD_FILE in "$IBD_DIR"/*.ibd; do
    TABLE_NAME=$(basename "$IBD_FILE" .ibd)

    echo "📦 Restoring table: $TABLE_NAME"
    SCHEMA_SQL_PATH=$TABLE_NAME."sql"

    # read .ibd file and create a sql file requivalent
    python3 main.py $IBD_DIR/$TABLE_NAME.ibd --sql --ddl > $SCHEMA_SQL_PATH

    echo "📄 Recreating table structures and data from: $SCHEMA_SQL_PATH"
    mysql -u $MYSQL_USER $DB_NAME < "$SCHEMA_SQL_PATH"
done
Enter fullscreen mode Exit fullscreen mode

save the file and make it executable:

chmod +x restore_data.sh
Enter fullscreen mode Exit fullscreen mode

Run:

./restore_data.sh
Enter fullscreen mode Exit fullscreen mode

When the execution is complete, your database should be populated with tables and it's data. A copy of each table schema has been saved in the root directory as well e.g. your_table.sql

Conclusion

We've been able to recover MySQL data from a .ibd file with the right steps and tools. While recovering strategy is possible, it is a time-consuming process so do well to backup your database regularly.

Top comments (0)