Salvaging a broken database

by Gisle Hannemyr

This chapter deals with the situation that the database for some reason is broken, and suggests some steps towards recovery.

Table of contents

Introduction

Date: Sun, 19 Apr 2020 07:37:57 +0200 (CEST)
From: mdadm monitoring 
To: root@bar.nemo-project.org
Subject: DegradedArray event on /dev/md/0:pvn.no

This is an automatically generated mail message from mdadm
running on pvn.no

A DegradedArray event had been detected on md device /dev/md/0.

Faithfully yours, etc.

P.S. The /proc/mdstat file currently contains the following:

Personalities : [raid1] [linear] [multipath] [raid0] [raid6] [raid5] [raid4] [raid10] 
md0 : active raid1 sda1[0]
      488253248 blocks super 1.2 [2/1] [U_]
      
unused devices:

Databases break. This may, for instance, happen if there is no space left on the device. The error message will typically look like this:

PDOException: SQLSTATE[HY000] [2002] No such file or directory in \
  lock_may_be_available() (line 167 of …/includes/lock.inc).

Try this first

You may be able to fix this by stopping and restarting the server

$ sudo systemctl stop mysql
$ sudo systemctl start mysql

Recovering the InnoDB files

If the mysql server fails to start, you may need to reinstall it, and the recover the binary InnoDB files that live under /var/lib/mysql.

tipInnoDB is the current a storage engine for MySQL. It replaced MyISAM in distribution 5.5.5 in 2010.

First, make sure that mysql on your production site is unable to start. You can check the status if the mysql service with:

$ sudo service mysql status

Before you do anything, make a backup of the /var/lib/mysql/ directory on your production server. It is usually huge, so make sure there is space on the destination partition.

$ sudo mkdir /home/username/mysql/
$ cd /var/lib/mysql/
$ sudo cp -R * /home/username/mysql/

You should not use the mysql server on your production site to recover these files on a staging site. Note that there is no need to run a CMS on the staging site, as everything is done using the CLI. Instead create a mirror of the backup directory on a staging server. Log on to the staging server and use this command:

$ rsync -r user@production.com:/home/username/mysql /home/username

Before you start the recovery process, you should clean up the broken mysql on the production server. This entails purging all mysql packages. If the mysql server is running, stop it first. Purging with the server still running may break things.

$ sudo systemctl stop mysql
$ sudo apt purge mysql-server mysql-client mysql-common
$ sudo apt purge mysql-server-core-* mysql-client-core-*

After a clean purge, remove all the mysql-related folders (make sure you have a backup copy of /var/lib/mysql).

$ sudo rm -rf /etc/mysql
$ sudo rm -rf /var/lib/mysql

Then reinstall the MySQL server fron scratch.

$ sudo apt install mysql-server

A full reinstall requires about 154 Mbyte of space. If it announces that less is required, some scraps remain. The only cure I've spotted for this is to keep purging until all packages are gone. If purging fails, running this may sometimes be helpful.

$ sudo apt-get --fix-broken install

Sources: AskUbuntu, StackOverflow.

Now, make sure that you also have a clean install of mysql on the staging server. Before starting to recover, you must stop the MySQL server gto configure recover mode:

$ sudo systemctl stop mysql

Change directory to the backup pf the InnoDB files that you synced to the staging server and copy just the system database folders (mysql and sys) to /var/lib/mysql. Then also copy the innodb dictionary and other innodb-files. Set correct ownership for these files. Make sure that /var/log/mysql/ exists and is writeable by the database user (mysql). Do not delete the files that already exist in /var/lib/mysql.

$ cd /home/username/mysql
$ sudo cp -R mysql /var/lib/mysql
$ sudo cp -R sys /var/lib/mysql
$ sudo cp ib* /var/lib/mysql
$ sudo chown -R mysql.mysql /var/lib/mysql

Edit the MySQL configuration file (often /etc/mysql/mysql.cnf) and add the following in the [mysqld] section at the end of file:

[mysqld]
innodb_force_recovery=3

You may need to use a higher number to be able to recover anything. (I haven't have time to investigate extensively, but 5 seems to work OK.) You might also need to add the section title ([mysqld]).

noteMaximum supported value of innodb_force_recovery is 6. Values of 4 or greater can permanently corrupt data files! Use at your own risk and only if you do not have a backup.

Now is the time to attempt to start the MySQL server again.

$ sudo systemctl start mysql

If the server starts up and stays up you can use mysqldump to dump your databases. If it does not start up you have to increase the above value of 1 stepwise.

If you look at /var/log/mysql/error.log you might still see errors. However try if you are able to log in to mysql:

$ mysql -u root -p
Enter password:
Welcome …
mysql> show databases
…
mysql> quit
Bye
$

If this works, you can start recovering production databases.

$ mysqldump -u root -p database > database.sql

You can import these dumps into a production database (i.e. one that is not configured to force recovery).

$ mysql -u root -p database < database.sql

Final word

[TBA]

Last update: 2020-02-28 [gh].