I recently had a power failure at my house and unfortunately the batteries in my UPS were shot (it had been complaining to me for a while about this, but I neglected to do anything about it, other things going on and whatnot), and as a result I ended up with a corrupt MySQL instance on one of my web servers. I don’t have a whole lot of experience troubleshooting and/or fixing MySQL, I use it for a few things, but pretty much end up searching for answers/instructions each time and copying/pasting commands with the hope that I’m doing the thing that I think I am. This post is for all of you folks out there like me who’ve never had to recover a corrupted MySQL instance and are now faced with having to do that.

Now, I’m hoping I can help you all cut out the of hours troubleshooting this that I ended up doing. The error message that I was getting in the MySQL error logs (/var/log/mysql/error.log) was

Decryption of redo log block at offset failed because there's no keyring configured.

I hadn’t remembered configuring encryption on my databases, but as I mentioned above most of the time I end up just finding the answer that looks best and implementing that in the hopes that it works, so I thought it’s possible that encryption got configured because of some command that I’d run at one point. Turns out that was not the case, this was a red herring and apparently it is one of the behaviors that can be exhibited when MySQL is unable to read a corrupted redo log. It assumes that it can’t read it because it’s encrypted, but actually it’s just corrupted, in my case because of an abrupt shutdown because of a power failure.

Alright, hopefully I just saved you a few hours of searching around trying to figure out how to find/load a keyring file that doesn’t exist and never did.

Now, the real meat of the post: recovery.

In my searching around for answers I came across several different posts around forcing innodb recovery. I’d never dealt with that before, but it seemed like something that could work for me based on some of the posts I read. For more information here’s a reference page that walks through what this setting does and how it works https://dev.mysql.com/doc/refman/8.4/en/forcing-innodb-recovery.html One thing that I didn’t do is read it fully. In the past when I’ve had table corruption I’ve used MySQL repair commands and it pretty much always repaired successfully without me having to do much more. One thing that I failed to read on this page is this is not a repair, it is a recovery step. What this means is that if you set this setting (innodb_force_recovery=x) in your option file (mine was located at /etc/mysql/mysql.conf.d/mysqld.cnf) what you will likely see is that your databases will become available again. In my case my websites popped back up and everything appeared to be hunky dory, so I figured that the repair was finished, right? So I went back in and set innodb_force_recovery=0 and restarted the MySQL service and bang, my sites were down again. What the heck?

Well if you do the thing that I didn’t do and actually read that reference page, it tells you right there that, once again, this is a recovery step. This is basically for emergencies when you have high levels of corruption such that repair is not possible and all you’re trying to do is get your data back out of the databases. It’s stated right on the page ‘As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0. An innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.’

Ok, so now what? We have to set the force_recovery setting to bring the databases up and then pull all of the data out of them. This actually was easier than I expected it to be, there’s actually a MySQL command for it called mysqldump. There’s a good set of directions on Stack Exchange that walks through this whole process (basically negating the need for this post that I’m writing) here https://dba.stackexchange.com/questions/65728/forcing-innodb-recovery-of-a-corrupted-database. One thing that I had trouble with though is that it kept failing while I was running it, but I didn’t know why. It would just fail and exit with ‘Lost connection to MySQL server during query’. Keep in mind this can be a lot of data and if you run out of space in the location that you’re writing the dump file to, it will fail and exit. Alright, here’s the commands that I ran to get my data pulled out of my corrupted instance of MySQL.

sudo systemctl stop mysql.service
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add innodb_force_recovery = 6 under the [mysqld] section, you may want to play around with a lower recovery setting. I ended up having to use 6, the MySQL service would fail to start with anything less.

sudo systemctl start mysql.service
sudo nano /var/log/mysql/error.log

Reading the error log to verify that after setting the force recovery option it was successfully able to read the database files and no additional errors were presented.

mysqldump -u root -p --quick --skip-lock-tables -A > alldb.sql

At this point, all of your data is in that alldb.sql file, at least all of it that could be successfully read. From here we need to re-initialize the data directory to basically create a non-corrupted starting point to dump all of our data back into. More information here https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html but here is what I ran to make it happen.

mysql -NBe "SELECT @@datadir" (Check the MySQL data directory)
mysqladmin shut
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Set innodb_force_recovery = 0 under the [mysqld] section.

mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql
chown mysql: /var/lib/mysql
cd /usr/local/mysql
bin/mysqld --initialize-insecure
/etc/init.d/mysql start
mysql
ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>'; (Set root password)

I initially tried just the regular –initialize, which worked but I never knew what the password was that it set. It was not any password that I’d set in the past and it didn’t tell or ask me what to use, so I just went the –initialize-insecure route and then went in and set my own root password after that. From there we need to load the database data into our freshly initialized instance.

mysql < alldb.sql

This worked for me, though there is one additional thing that I had to do in order to get everything operational again; it successfully imported all of the different accounts that I’d created for each database, but didn’t set the permissions for those accounts, so I had to go through and set those again for each account.

GRANT ALL ON <database>.* TO '<account>'@'localhost';

But that was the extent of it. After that I had a fully functioning MySQL instance again. I have another post about recreating tables that were lost due to corruption during a recovery like this here https://www.lukky.us/zabbix-table-history_uint-missing/