On Feb 9th 2019, the datacenter where the Virtual Private Server that I use for this site(and many others) experienced a bit of a hardware failure which required some emergency maintenance.
This happened overnight and when I woke up, I found my inbox with 100+ emails alerts from the various sites that I manage indicating an error with connecting to the database.
Normally this isn’t a huge issue but this time things were different. The MySQL server not only wouldn’t start, it also didn’t provide any logs in either the MySQL error log or the system error log. I didn’t have any recent backups and neither did the datacenter, so this meant that had to do some serious guess work.
If that sounds similar to what you have to deal with, don’t worry. It’s fixable.
The Fix
Before you start screwing around with a live production database, unless you have daily or weekly backups that you would be ok with, the first thing you’re going to want to do is extract whatever data you can.
To do that, we’re going to boot the MySQL in forced recovery mode. There’s a line in your mysql my.cnf that says “innodb_forced_recovery = 0”
Change that 0 value to 1 or just add that line if you don’t have it.
This may or may not work depending on the issue. If it doesn’t work, try a value of 2 or 3. If recovery levels 1-3 won’t work for you, there’s still hope as there’s also levels 4-6, however, do understand that recovery levels 4-6 may cause data loss with level 6 resulting in guaranteed data loss. So work your way up from level 1 and pray to god that your database boots up in the earlier levels.
In my case, I had to use recovery level 4 which puts me in a risky position but at least it booted.
Once you find a level that boots, it’s time to backup and restore your databases/tables
Backup and restore MySQL
For this you have two options. Either export the databases using phpmyadmin or use mysqldump.
Both tools will fail once it reaches a table that’s corrupted so your mileage may vary.
To dump ALL databases run
mysqldump –all-databases –single-transaction –quick –lock-tables=false > someName-full-backup-$(date +%F).sql -u root -p
To dump one specific database run
mysqldump -u username -p databaseName –single-transaction –quick –lock-tables=false > databaseName-backup-$(date +%F).sql
To dump one specific table run
mysqldump -u username -p –single-transaction –quick –lock-tables=false databaseName tableName > databaseName-tableName-$(date +%F).sql
Once you have everything backed up, this is where it may be easy or not as it depends on what the issue is and what innodb recovery level you had to use.
Finding the faulty table
Finding the faulty table
Completely remove MySQL server and do a fresh re-install
This step
Remove MySQL completely (instructions for a Debian based system)
sudo apt-get remove –purge mysql\*
sudo apt-get clean
sudo updatedb
Reinstall MySQL (in my case it was 5.6 or 5.7)
Download the mysql repository configuration tool
wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
(you can get whatever the latest version is at
http://dev.mysql.com/downloads/repo/apt/ )
Install the tool using
dpkg -i mysql-apt-config_0.8.9-1_all.deb
You get the option of selecting versions 5.6 or 5.7. I went with 5.6 because that’s what was running before.
Update the apt repo list and install the damn server
apt-get update
apt-get install mysql-community-server
Re-install the mysqli connector (in my case I needed it for php5.6 and php7)
apt-get install php5-mysqlnd
apt-get install php-mysql
Note: you can get away with php5-mysql but for MySQL 5.6 or higher you’re going to want mysqlnd. (the ND stand for Native Driver).
While it php5-mysql and MySQL 5.6+ will most likely work, depending on the level of error reporting, you may get a LOT of notices about incorrect minor versions. It’s a problem I ran into before and it’s more annoying than anything.
To restore a backup:
For full backups
mysql -u root -p < full-backup.sql
For a specific database
mysql -u [username] -p databaseName < databaseName-backup.sql
For a specific table
mysql -u [username] -p databaseName < databaseName-tableName.sql
Understanding the InnoDB recovery levels