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:<\/p>\n\n\n\n
[Note] Starting MariaDB 10.11.6-MariaDB-0+deb12u1 source revision as process 661197\n[Note] InnoDB: Compressed tables use zlib 1.2.13\n[Note] InnoDB: Number of transaction pools: 1\n[Note] InnoDB: Using crc32 + pclmulqdq instructions\n[Note] InnoDB: Using liburing\n[Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB\n[Note] InnoDB: Completed initialization of buffer pool\n[Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)\n[Note] InnoDB: Starting crash recovery from checkpoint LSN=10862992357\n[Note] InnoDB: End of log at LSN=10868013717\n[Note] InnoDB: Retry with innodb_force_recovery=5\n[ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption\n[Note] InnoDB: Starting shutdown...\n[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.\n[Note] Plugin 'FEEDBACK' is disabled.\n[ERROR] Unknown\/unsupported storage engine: InnoDB\n[ERROR] Aborting\n<\/code><\/pre>\n\n\n\nBefore continuing with any recovery process, I created a backup of \/var\/lib\/mysql:<\/p>\n\n\n\n
cp -rp \/var\/lib\/mysql \/var\/lib\/mysql.bak<\/code><\/pre>\n\n\n\nSome posts recommended to start MariaDB with innodb_force_recovery<\/a> = 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:<\/p>\n\n\n\n[mysqld]
innodb_force_recovery = 6<\/code><\/pre>\n\n\n\nWith 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:<\/p>\n\n\n\n
mysqldump: Error 1034: Index for table 'mytable' is corrupt; try to repair it when dumping table mytable at row: 3<\/code><\/pre>\n\n\n\nInterestingly, 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:<\/p>\n\n\n\n
while read TABLE; do
mysqldump -f mydatabase $TABLE > \/root\/mydatabase\/$TABLE-$(date -I).sql;
done < \/root\/tables<\/code><\/pre>\n\n\n\nAll files can then just be concatenated into one large dump file:<\/p>\n\n\n\n
cat \/root\/mydatabase\/* > \/root\/mydatabase.sql<\/code><\/pre>\n\n\n\nAfter the database content was safely stored in a text sql dump file, I bootstrapped a new \/var\/lib\/mysql directory:<\/p>\n\n\n\n
rm -rf \/var\/lib\/mysql
apt install --reinstall mariadb-server
sudo -u mysql mariadb-install-db<\/code><\/pre>\n\n\n\nThen I created the database again and inserted the dump:<\/p>\n\n\n\n
MariaDB [(none)]> CREATE DATABASE mydatabase;\nMariaDB [(none)]> use mydatabase;\nMariaDB [mydatabase]> source \/root\/mydatabase.sql<\/code><\/pre>\n\n\n\nAfter this, the database was healthy again.<\/p>\n","protected":false},"excerpt":{"rendered":"
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: Before continuing with any recovery process, I created a backup of \/var\/lib\/mysql: Some posts … Continue reading