Today I came across a crashed InnoDB MariaDB database. When trying to restart the process, it immediately failed with the following messages in the systemd journal:

[Note] Starting MariaDB 10.11.6-MariaDB-0+deb12u1 source revision  as process 661197
[Note] InnoDB: Compressed tables use zlib 1.2.13
[Note] InnoDB: Number of transaction pools: 1
[Note] InnoDB: Using crc32 + pclmulqdq instructions
[Note] InnoDB: Using liburing
[Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
[Note] InnoDB: Starting crash recovery from checkpoint LSN=10862992357
[Note] InnoDB: End of log at LSN=10868013717
[Note] InnoDB: Retry with innodb_force_recovery=5
[ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
[Note] InnoDB: Starting shutdown...
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

Before continuing with any recovery process, I created a backup of /var/lib/mysql:

cp -rp /var/lib/mysql /var/lib/mysql.bak

Some posts recommended to start MariaDB with innodb_force_recovery = 4. However, as you can see in the messages, MariaDB automatically tried to recover with innodb_force_recovery = 5 and failed. I manually set the recovery mode to 6 in the /etc/mysql/mariadb.cnf:

[mysqld]
innodb_force_recovery = 6

With that, MariaDB finally started. Sadly, this did not yet fully resolve the issue. In mode 6, MariaDB starts with all tables in read only mode. That means I was able to access the data, but the database was not really usable or fixed. The obvious next step was to do a mysqldump, clean the database and then insert all data again. Running mysqldump failed with the next error:

mysqldump: Error 1034: Index for table 'mytable' is corrupt; try to repair it when dumping table mytable at row: 3

Interestingly, dumping a single table did work. If anyone knows why, please let me know. I dumped a list of all table names in the database into a file called /root/tables, then with bash iterated over all tables and dumped each one into a dedicated file:

while read TABLE; do
   mysqldump -f mydatabase $TABLE > /root/mydatabase/$TABLE-$(date -I).sql;
done < /root/tables

All files can then just be concatenated into one large dump file:

cat /root/mydatabase/* > /root/mydatabase.sql

After the database content was safely stored in a text sql dump file, I bootstrapped a new /var/lib/mysql directory:

rm -rf /var/lib/mysql
apt install --reinstall mariadb-server
sudo -u mysql mariadb-install-db

Then I created the database again and inserted the dump:

MariaDB [(none)]> CREATE DATABASE mydatabase;
MariaDB [(none)]> use mydatabase;
MariaDB [mydatabase]> source /root/mydatabase.sql

After this, the database was healthy again.