Using lsof to Restore MySQL Deleted Data Files

MySQL 8.0.36 - RHEL 8.10

If you accidentally delete files, DO NOT RESTART OR SHUTDOWN the server.

The MySQL database info:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
5 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.65 sec)

~]# ls -1 /var/lib/mysql/zabbix/ | wc -l
207

Simulation files was mistakenly deleted.

rm -rf /var/lib/mysql/zabbix

Check MySQL service status, and set database read only.

~]# systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2025-09-03 19:18:15 CST; 2 days ago
 Main PID: 16048 (mysqld)
   Status: "Server is operational"
    Tasks: 61 (limit: 49003)
   Memory: 314.0M
   CGroup: /system.slice/mysqld.service
           └─16048 /usr/libexec/mysqld --basedir=/usr

Sep 03 19:17:46 oracle19c systemd[1]: Starting MySQL 8.0 database server...
Sep 03 19:17:47 oracle19c mysql-prepare-db-dir[15969]: Initializing MySQL database
Sep 03 19:18:15 oracle19c systemd[1]: Started MySQL 8.0 database server.

~]# mysql -uroot -p
mysql> SET GLOBAL read_only = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (23.44 sec)

Using lsof to restore deleted files.

~]# lsof | grep deleted | grep "/var/lib/mysql/zabbix" | awk '{print $2,$5}' | sort | uniq
16048 mysql
16048 REG

~]# mkdir /var/lib/mysql/zabbix

~]# ls -l /proc/16048/fd | grep deleted | grep "/var/lib/mysql/zabbix" |awk '{print $9,$11}' > /tmp/deleted.txt
~]# while read num file; do echo "cat /proc/16048/fd/$num > $file"; done < /tmp/deleted.txt | bash

~]# chown -R mysql:mysql /var/lib/mysql/zabbix

~]# mysql -uroot -p

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL read_only = 0;
Query OK, 0 rows affected (0.05 sec)

~]# systemctl restart mysqld

Script:

cat >get_restore_files.sh <<"EOF"
#!/bin/env bash

#-----------------
#
# get_restore_files.sh
#
# Usage: ./get_restore_files.sh <pid> <path>
#
# Func: generating restore file script.
#
#-----------------

set -e

usage() {
  cat <<"END"
usage: ./get_restore_files.sh <pid> <path>

pid : The deleted file open process id.
path: The deleted file path.

Use `lsof | grep deleted` to fetch.
END
}

if [[ -z $1 || -z $2 ]]; then
  usage
  exit 1
fi

ls -l /proc/$1/fd | grep deleted \
| grep "$2" \
| awk '{print $9,$11}' \
| while read num file; do echo "cat /proc/$1/fd/$num > $file"; done \
> /tmp/get_$1_restore_files.txt
EOF