How We Saved a Production WordPress Site When the Database Was Dropped and Backups Failed

The Crisis: A Misclick and Silent Backup Failures

It was past 9:00 PM when the emergency notification hit our dashboard: “Error establishing a database connection.”

A client working late on their WordPress instance had made a split-second misclick in phpMyAdmin. Instead of clearing a temporary staging table, they dropped the entire production database. In a fraction of a second, years of blog posts, WooCommerce customer records, configurations, and custom plugin tables were gone.

"In a fraction of a second, years of WooCommerce customer records, blog posts, configurations, and custom plugin tables vanished into thin air."

The logical first step was to restore the previous night's backup. But when we checked the hosting panel, the automated routine had quietly failed weeks prior without throwing an alert. The only salvageable snapshot was a server filesystem backup from seven days earlier—and it didn't contain standard .sql export files.

All we had was a folder full of raw .frm and .ibd files.

The Challenge: Why You Can’t Just "Copy-Paste" .ibd Files

In MySQL (specifically with the default InnoDB engine), database storage is divided into two distinct components for each table:

• .frm files: Schema blueprints containing table definitions, column data types, indexes, and constraint structures.
• .ibd files: The InnoDB tablespace containing raw row data, secondary indexes, transaction change buffers, and undo logs.

To properly rebind an orphaned .ibd file back into MySQL, you must execute a strict four-step sequence for every single table:

1. Rebuild empty table structure from the original .frm blueprint.
2. Discard the blank tablespace using ALTER TABLE table_name DISCARD TABLESPACE;.
3. Copy the salvaged .ibd file into the target database data directory and apply correct system file ownership (e.g. mysql:mysql).
4. Force MySQL to bind data using ALTER TABLE table_name IMPORT TABLESPACE;.

For a single table, running these SQL statements manually takes under a minute. For an active WordPress instance running dozens of core and plugin tables (such as wp_posts, wp_postmeta, wp_woocommerce_order_items, etc.), executing this manually under high-pressure downtime is excruciatingly slow and error-prone.

The Solution: Automating the Reconnection via Python

To eliminate manual overhead and potential human error during a critical outage, we built a Python automation script that processed the entire schema in a single automated loop.

Step 1: Extract Schema Definitions

Using the official mysqlfrm diagnostic utility, we generated the original DDL creation statements directly from the raw .frm files:

After executing wp_schema.sql on a fresh, clean target database, all empty table containers were created and initialized.

Step 2: Automating Tablespace Binding

Next, our Python script automated the exact sequence of discarding blank tablespaces, placing the raw .ibd files into position, fixing file permissions, and issuing the tablespace import commands across all tables:

import os
import shutil
import subprocess
import mysql.connector

DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_secure_password',
    'database': 'wordpress_recovery'
}

BACKUP_DIR = '/path/to/raw_backup_files'
MYSQL_DATA_DIR = '/var/lib/mysql/wordpress_recovery'

def recover_database():
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()

    ibd_files = [f for f in os.listdir(BACKUP_DIR) if f.endswith('.ibd')]
    print(f"Discovered {len(ibd_files)} tables to restore...")

    for file_name in ibd_files:
        table_name = os.path.splitext(file_name)[0]
        src_path = os.path.join(BACKUP_DIR, file_name)
        dest_path = os.path.join(MYSQL_DATA_DIR, file_name)

        try:
            # 1. Discard the blank tablespace
            cursor.execute(f"ALTER TABLE `{table_name}` DISCARD TABLESPACE;")
            conn.commit()

            # 2. Copy the salvaged .ibd file into place
            shutil.copy2(src_path, dest_path)

            # 3. Apply correct MySQL file permissions
            subprocess.run(["chown", "mysql:mysql", dest_path], check=True)
            subprocess.run(["chmod", "660", dest_path], check=True)

            # 4. Bind the tablespace to the active engine
            cursor.execute(f"ALTER TABLE `{table_name}` IMPORT TABLESPACE;")
            conn.commit()

            print(f"[SUCCESS] Restored table: {table_name}")

        except mysql.connector.Error as err:
            print(f"[FAIL] MySQL error on {table_name}: {err}")
        except Exception as e:
            print(f"[FAIL] OS error on {table_name}: {e}")

    cursor.close()
    conn.close()

if __name__ == "__main__":
    recover_database()

The Result & Lessons Learned

Within seconds of running the script, MySQL re-indexed every .ibd tablespace. A quick database integrity check confirmed zero table corruption, and once wp-config.php was reconnected to the recovered schema, the production website resumed normal operations immediately.

Dealing with a crashed server or looking to optimize your database reliability? Talk to our engineering team for an audit or custom disaster recovery architecture.