How to Recreate MySQL's InnoDB System Database on Linux When It Corrupts

Your system tables are completely corrupt and mysqld won’t even start. Rebooting the server and even trying to start up with innodb_force_recovery doesn’t work.

When you check /var/lib/mysql/mysql.err, it tells you the situation is so bad you should consider restoring from backup.

You do have backups, right?

If you’re taking system snapshots, the easiest way out of this situation is to pick the latest one and roll back. Although, the system table corruption issue may have already started by that point.

Hopefully, you’re also taking regular backups via mysqldump or a similar utility.

You can recreate your system tables by "reinstalling" MySQL to get the service back up and running.

Investigate the Cause of the Corruption

Before you start restoring, it’s a good idea to try to investigate the cause of the corruption. If you know what caused it, you can prevent it from happening again.

The MySQL documentation itself is a good source to start with:

https://dev.mysql.com/doc/refman/8.4/en/crashing.html

Some common causes:

• Hardware failure. For example, I/O issues with your disk. You’ll typically see I/O issues plastered all over your system journal if this is the case.
• OOM Killer. Check if your machine is resource-constrained. It’s possible the OOM Killer killed MySQL at a critical point, leading to database corruption. You may need to upgrade your system resources to prevent this from happening again.
• An external program manipulated data without locking the table properly.

Recreating MySQL System Tables

When you install MySQL on a Linux server, you usually do it from a system package from your distribution’s repositories; whether that’s Ubuntu, Debian, or Arch. This package will contain some scripts to install and setup MySQL for you; creating directories, configuration files, users, groups, initialising the database, etc.

What we’re doing here is not reinstalling the MySQL package; we’re stepping through some of the process for setting up MySQL.

We already have the mysql user and group created, and quite a few directories that we’re going to leave alone. Most of it is already done, so we only need to redo the last few things.

We’re going to rename the /var/lib/mysql directory to something else so MySQL loses track of it, and do the same for the logs directory. Alternatively, you can purge this data altogether, but it’s likely worth backing up this data—just in case.

mysqld won’t start without proper configuration, so we’ll leave the /etc/mysql directory and everything else alone.

And while we’re doing this, we’re going to stop and mask the MySQL service so that nothing can start it up in the meantime.

I’m running Ubuntu. The correct permissions/directories for you may differ depending on your distribution.

To start with, setup the new directories:

systemctl stop mysql && systemctl mask mysql
mv /var/lib/mysql /var/lib/corrupted_mysql
mv /var/log/mysql /var/log/corrupted_mysql
mkdir /var/lib/mysql && chown mysql:mysql /var/lib/mysql
mkdir /var/log/mysql && chown mysql:adm /var/log/mysql

It’s a good idea to reference the official documentation while working through this process: https://dev.mysql.com/doc/refman/8.4/en/data-directory-initialization.html

The next step is to initialise the data directory, and, assuming the command executed successfully, unmask and start the MySQL service:

mysql --initialize --user mysql
systemctl unmask mysql && systemctl start mysql

This command would have created a temporary password for ‘root’@‘localhost’ which will be dropped in /var/log/mysql/mysql.err.

Check if you can access the MySQL shell using that password with:

mysql -u root -p

Then run this to finalise the setup:

mysql_secure_installation

Restoring From SQL Dumps

Now, you need to recreate your database(s) and users.

It might look like this:

CREATE DATABASE db_name;
CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON db_name.* TO 'your_username'@'localhost';
FLUSH PRIVILEGES;

After you’ve done that, start restoring your database from your SQL dump:

mysql -u root -p db_name < db_name.sql

Once this is done, check your database for corruption with mysqlcheck:

mysqlcheck -u root -p db_name --check

If it all comes back OK, this is a good sign. If you’re lucky, the corruption may have been limited only to the system tables.

If not, grab an earlier SQL dump and repeat this process.