Another MySQL howto!

I had a server running Mariadb or MySQL (not entirely sure which one, but I think it was Mariadb) within Docker on top of Btrfs. An awesome sandwich - a positive bundle of amazing tech. Or so I thought. It ran happily for a while, then got so monumentally corrupted that it was not possible to restore it. My research led me to various pages explaining that the only way to restore functionality was to destroy all the data.

I could not dump the data from the docker container because it was impossible to start docker itself, but I was able to make a copy of the mysql directory, so once I had restored the server itself (nixops <3) I had to figure out how to restore the database.

In the end, it turned out to be quite simple.

First note that in my case, there was only one database being used in the server! This is important, because this method involves replacing the mysql innodb file wholesale. Other restore methods exist if you have more than one database in the server, but this one will probably not work.

All of the below commands should be run as root, or prefixed with sudo, depending on your setup.

Stop the mysql server (Systemd here):

systemctl stop mysql

Backup the mysql directory:

tar cjpf mysql.tar.bz2 /var/lib/mysql

Copy the files from the database:

cp -r <PATH_TO_BACKUP>/mysql/database_name /var/lib/mysql/

Copy the mysql data files

cp -r <PATH_TO_BACKUP>/mysql/ib* /var/lib/mysql/

Fix ownership

chown -R mysql:mysql /var/lib/mysql

Restart the MySQL or Mariadb server:

systemctl start mysql

Finally, make sure the data is consistent:

mysql_upgrade

That worked for me, I hope it does for you.