Recovering MySQL Data from a Failed Linux System Using Disk Mount and LVM

1. Overview

If a Linux operating system crashes and cannot boot, can the MySQL data on that machine be recovered? This article conducts an experiment to answer that question.

2. Environment

Two virtual machines are prepared using VirtualBox. The VM with IP 192.168.56.81 runs MySQL and will be simulated to fail. The VM with IP 192.168.56.71 is a new machine to which the database will be migrated.

File system on VM 81:

MySQL database on VM 81:

3. Experiment Steps

Delete the /boot directory on VM 81 and force a reboot, which results in a boot failure with the error "file ‘/grup2/i386-pc/normal.mod’ not found".

Shut down the faulty VM 81 and force exit.

Attach the hard disk of VM 81 to the new VM 71 and start VM 71.

Using lsblk we can see that sdb is the newly attached disk.

Attempt to mount the new disk:

[root@oracletest ~]# mkdir /newmnt
[root@oracletest ~]# mount /dev/sdb2 /newmnt
mount: unknown filesystem type 'LVM2_member'

The error occurs because /dev/sdb2 is a physical volume belonging to an LVM group and cannot be mounted directly.

Activate the logical volume group: vgchange -ay /dev/vg After activation, the logical volume becomes available and can be mounted.

Mount the logical volume and verify that the files are recovered:

[root@oracletest ~]# mount /dev/vg/lvroot /newmnt
[root@oracletest ~]# cd /newmnt
[root@oracletest newmnt]# ls
bin  boot  dev  etc  home  lib  lib64  lost+found  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var
[root@oracletest newmnt]# cd /newmnt/root
[root@oracletest root]# ls
important.txt  install.sh  mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
[root@oracletest root]# cat important.txt
this is important text!

Next, restore the MySQL database. The principle is the same as a migration: copy the original MySQL files to the new location and start the service. Install the same MySQL version on VM 71 (installation steps omitted). Inspect the original MySQL configuration to locate log and data directories:

[root@oracletest root]# cat /newmnt/etc/my.cnf
[mysqld]
# basic settings #
...
basedir = /usr/local/mysql
datadir = /opt/mydata/data
tmpdir = /opt/mydata/tmp
log_bin = /opt/mydata/log/binlog/binlog
...

Check the original data files:

[root@oracletest root]# cd /newmnt/opt/mydata/
[root@oracletest mydata]# ll
total 12
drwxr-xr-x 6 995 1000 4096 Feb 11 14:03 data
drwxr-xr-x 4 995 1000 4096 Feb 11 14:03 log
drwxr-xr-x 2 995 1000 4096 Feb 11 14:03 tmp

Create the required directories on the new system and set ownership:

mkdir -p /opt/mydata/data
mkdir -p /opt/mydata/tmp
mkdir -p /opt/mydata/log/binlog
chown -R mysql:mysql /opt/mydata

Copy the MySQL files to the appropriate locations:

cp -r /newmnt/etc/my.cnf /etc
cp -r /newmnt/opt/mydata/data /opt/mydata/
cp -r /newmnt/opt/mydata/log/binlog /opt/mydata/log/
chown -R mysql:mysql /opt/mydata

Start MySQL:

[root@oracletest ~]# service mysql start
Starting MySQL.2021-04-08T11:39:26.437943Z mysqld_safe error: log-error set to '/opt/mydata/log/error.log', however file don't exists. Create writable for user 'mysql'.
The server quit without updating PID file (/opt/mydata/data[FAILED]est.pid).

The service fails because the error log file is missing. Create it and restart:

[root@oracletest ~]# touch /opt/mydata/log/error.log
[root@oracletest ~]# chown -R mysql:mysql /opt/mydata/log/error.log
[root@oracletest ~]# service mysql start
Starting MySQL..      [  OK  ]

After the service starts, the database tables are restored.

4. Summary

As long as the database data files are not deleted, even if the system cannot boot, we can detach the disk, attach it to another machine, and copy the data out.

Develop a habit of regular backups for production, simulation, development, and testing environments.

About the Author

Yang Bao, a dedicated DBA at Guotai Junan, passionate about Oracle and MySQL databases, familiar with shell and Python programming, and experienced with Zabbix monitoring.