Recovering Accidentally Deleted MySQL Metadata on CDH Without Downtime

MySQL serves as the metastore for CDH, storing Hive table definitions, partition information, data locations, and permission settings. When the MySQL 5.5.6 replica failed, the primary data directory was mistakenly deleted, causing the entire big‑data platform to become unavailable.

The recovery had to be performed without stopping business services, so the team acted quickly to restore the lost handles.

1. Lock the Database

-- Set the database to read‑only
SET GLOBAL read_only = 1;
-- Flush tables and acquire a global read lock
FLUSH TABLES WITH READ LOCK;

2. Locate Deleted File Handles

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+

# Find deleted but still‑open file descriptors
lsof | grep deleted | grep "/usr/local/mysql/data" \
| awk '{print $2, $5, $10}' > /tmp/deleted_files.txt

3. Recover Data Files

3.1 Restore Shared Tablespace Files

# Recover ibdata1 (replace $pid with the actual process ID)
cat /proc/$pid/fd/64 > /var/lib/data/ibdata1
chown mysql:mysql /var/lib/data/ibdata1

# Recover the redo log (e.g., ib_logfile0)
cat /proc/$pid/fd/65 > /var/lib/data/ib_logfile0
chown mysql:mysql /var/lib/data/ib_logfile0

3.2 Restore Independent Tablespace Files

# Example: recover mydb.tab1.ibd
cat /proc/$pid/fd/66 > /var/lib/data/mydb/tab1.ibd
chown mysql:mysql /var/lib/data/mydb/tab1.ibd

3.3 Restore Table Structure Files (.frm)

# Copy the .frm file from a backup that matches the original schema
cp /path/to/backup/mydb/tab1.frm /var/lib/data/mydb/
chown mysql:mysql /var/lib/data/mydb/tab1.frm

# If the .frm file is missing, recreate the table definition first

4. Verify Consistency Between .frm and .ibd

# Step 1: Discard the existing tablespace (table must exist)
ALTER TABLE table_name DISCARD TABLESPACE;

# Step 2: Import the recovered tablespace; MySQL will automatically validate the .frm match
ALTER TABLE table_name IMPORT TABLESPACE;

5. Technical Principles

5.1 Linux File Deletion Mechanism

When rm removes a file, only the directory entry is deleted; the actual data remains on disk as long as a process holds an open file descriptor, which can be accessed via /proc/<pid>/fd.

5.2 MySQL’s Dependence on Open Handles

InnoDB keeps .ibd file descriptors open while the server runs. Deleting the file marks it as “deleted” in the filesystem, but the data stays intact until the handle is closed.

5.3 Version Differences

MySQL 5.6+ enables innodb_file_per_table by default, using separate .ibd files per table. MySQL 8.0+ removes .frm files entirely, storing table definitions in the system tablespace (mysql.ibd).

6. Summary and Lessons Learned

The incident exposed the fragility of metadata management and gaps in operational procedures; CDH relies heavily on MySQL, and loss of metadata brings the whole cluster down. Protecting core production components like the metastore with strict access controls, multi‑level backups (local, cloud, binary logs), and automated health checks is essential.

Prevention outweighs recovery: enforce sudo auditing, implement tiered backup strategies, and regularly verify tablespace integrity. Upgrading legacy versions (e.g., MySQL 5.5) reduces technical debt and mitigates the risk of similar disasters.