mysql_recovrey

dbsake is a collection of command-line tools for MySQL DBA tasks, including recovering table structures from .frm files and creating sandboxed MySQL instances for testing recovery.

Install dbsake

curl -s http://get.dbsake.net > /usr/bin/dbsake
chmod u+x /usr/bin/dbsake

Note: dbsake requires Python 3.9. It does not work with Python 3.10+.

Install Dependencies

apt-get install libaio1 libaio-dev libncurses5

Create a Sandbox Instance (MySQL 5.7)

dbsake sandbox \
    --sandbox-directory=/opt/mysql-5.7.31 \
    --datadir=/root/mysql

Create a Sandbox (MariaDB from tarball)

# Download the MariaDB binary tarball
wget https://archive.mariadb.org/mariadb-10.6.15/bintar-linux-systemd-x86_64/mariadb-10.6.15-linux-systemd-x86_64.tar.gz

# Create sandbox
dbsake sandbox \
    --sandbox-directory=/root/ezfood-sandbox \
    --mysql-distribution=/root/mariadb-10.6.15-linux-systemd-x86_64.tar.gz \
    --datadir=/root/ezfood-mysql/mysql

Start the Sandbox

/opt/mysql-5.7.31/start

Notes

• Full documentation: https://dbsake.readthedocs.io/en/latest/
• The sandbox is a self-contained MySQL instance useful for recovering data without touching production.

If you have .frm and .ibd files from a crashed MySQL server but no dump, you can recover the database structure and data using these steps.

Prerequisites

• A server running the same MySQL version as the crashed instance
• The .frm and .ibd files from the original data directory
• MySQL Utilities or dbsake installed

Step 1 - Recover Table Structure from .frm Files

# Run mysqlfrm in diagnostic mode (no running MySQL required)
mysqlfrm --diagnostic TABLE_NAME.frm > TABLE_NAME.txt

# Review the generated CREATE TABLE statement
cat TABLE_NAME.txt

Step 2 - Create the Table Structure

Create the database and tables using the recovered CREATE statements:

mysql -u root -p << EOF
CREATE DATABASE recovered_db;
USE recovered_db;
-- paste the CREATE TABLE statement here
EOF

Step 3 - Discard the Tablespace

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

Step 4 - Copy the .ibd File

cp /path/to/original/table_name.ibd /var/lib/mysql/recovered_db/
chown mysql:mysql /var/lib/mysql/recovered_db/table_name.ibd

Step 5 - Import the Tablespace

mysql -u root -p recovered_db -e "ALTER TABLE table_name IMPORT TABLESPACE;"

Verify

mysql -u root -p recovered_db -e "SELECT COUNT(*) FROM table_name;"

Notes

• This method works best when the MySQL version matches exactly. Version mismatches may cause import errors.
• For a simpler approach, consider using dbsake which automates the frm reading step.

Use dbsake to read table structure from .frm files when no backup is available, and to process mysqldump output for data recovery.

Install dbsake

curl -s http://get.dbsake.net > /usr/bin/dbsake
chmod u+x /usr/bin/dbsake

Recover Table Structure from .frm File

# Read structure without a running MySQL instance
dbsake frmdump /var/lib/mysql/mydb/mytable.frm

This outputs the CREATE TABLE statement directly to stdout.

Recover Structure Using a Running MySQL Instance (More Complete)

# With a running MySQL that matches the original version
mysqlfrm --server=root:password@localhost --diagnostic mytable.frm

Process mysqldump Output

dbsake can filter and transform mysqldump files — for example, extracting a single table:

dbsake splitdump full_backup.sql.gz

This splits the dump into per-table files for targeted restoration.

Notes

• dbsake requires Python 3.9 (not 3.10+).
• The frmdump command does not require MySQL to be running — ideal for recovery scenarios.
• The --server option in mysqlfrm provides more complete output by spawning a temporary MySQL instance.