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.