Recover MariaDB Redo Log Corruption with force_recovery and Reinit

One day, my dev machine, where I use MariaDB through XAMPP regularly, had an unclean shutdown (a blue screen followed by an automatic reboot). When it came back up, Apache started normally, but MariaDB would not start at all. Every time I tried starting it, it stopped immediately. The machine held around twenty dev databases, several for projects I had not yet pushed to the server, so I needed to recover them. I worked through it until I recovered everything, then wrote down the sequence I actually used in case someone else runs into the same problem.

The database names and backup paths in this article are examples, but the recovery steps and commands are the ones I actually used to fix this case.

Symptoms: Apache runs normally, but MariaDB keeps stopping

The XAMPP panel only said MySQL shutdown unexpectedly, which told me nothing useful. What I needed to check was MariaDB's actual error log. On XAMPP, it is not named *.err; it is at C:\xampp\mysql\data\mysql_error.log. I opened it and found these three lines, which explained everything:

[ERROR] InnoDB: Missing MLOG_CHECKPOINT ... at the end of the redo log
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Incorrect definition of table mysql.global_priv: ... Incorrect file format
[ERROR] Aborting

In plain English, the InnoDB redo log was missing the checkpoint that should have been written properly at shutdown (because the machine stopped unexpectedly without a clean shutdown). With InnoDB unable to start, system tables such as mysql.global_priv, which itself uses InnoDB, became unreadable too. The server finally reported Aborting and shut itself down.

Before touching anything, copy the data folder elsewhere

This is the most important step, and impatient people often skip it. Before starting recovery, stop mysqld completely and copy the entire mysql\data folder to another drive, because the recovery steps that follow (especially higher force_recovery levels) can cause further damage to the data files. With a copy of the original, I can at least always go back and start again.

# สำรอง data ทั้งก้อนไปไดรฟ์อื่นก่อน ยังไม่แตะของจริง
robocopy "C:\xampp\mysql\data" "D:\db-backup\data_before_recovery" /E /COPYALL

What innodb_force_recovery does and how the levels differ

innodb_force_recovery is a switch that tells InnoDB to skip its normal startup operations (redo log recovery, rollback of unfinished transactions, purge, etc.) one layer at a time, forcing the engine to start even if the data is not 100% complete. Its only purpose is to get the engine running enough to dump the data, not to keep using it. It accepts values from 1 to 6:

Start with force_recovery to make the data readable

In my case, I was lucky that level 1 was enough. The cleanest way on XAMPP is to edit my.ini, add one line under [mysqld], then start normally without complicating things with the command line:

# C:\xampp\mysql\bin\my.ini  (ใต้ [mysqld])
[mysqld]
innodb_force_recovery = 1

To start it manually without touching my.ini, running it directly from the command line works too. I also included --skip-grant-tables because the grant tables were in the damaged system DB, letting it skip permission checks for now:

# สตาร์ท mysqld ด้วย force_recovery ระดับ 1 + ข้ามการเช็ค grant table
C:\xampp\mysql\bin\mysqld.exe --defaults-file=C:\xampp\mysql\bin\my.ini --innodb_force_recovery=1 --skip-grant-tables

If it starts, a client can connect, and SHOW DATABASES; lists everything, that clears the first hurdle. It is not finished yet. The data is only readable at this point, so do not get excited and start using it.

Dump each db because --all-databases fails at the system table

A common mistake is rushing to run mysqldump --all-databases to get everything in one go. It fails when it tries to read the damaged mysql.global_priv, bringing down the entire dump and leaving nothing usable. The way through is to dump each db separately, taking only the databases that hold my own work and skipping mysql, information_schema, and performance_schema entirely (those will be recreated later).

First, get the full list of databases, then loop through and dump each one:

-- ดูรายชื่อ db ทั้งหมด เอาไปไล่ dump ทีละก้อน
SHOW DATABASES;

# dump ทีละ db ใส่ --databases ไว้ ไฟล์จะมี CREATE DATABASE + USE ให้เอง ตอน reload สะดวก
C:\xampp\mysql\bin\mysqldump.exe -u root --single-transaction --skip-lock-tables --databases shop_local > D:\db-backup\perdb\shop_local.sql
C:\xampp\mysql\bin\mysqldump.exe -u root --single-transaction --skip-lock-tables --databases crm_local  > D:\db-backup\perdb\crm_local.sql
C:\xampp\mysql\bin\mysqldump.exe -u root --single-transaction --skip-lock-tables --databases blog_local > D:\db-backup\perdb\blog_local.sql
# ... ไล่ให้ครบทุก db ที่เป็นของเราเอง

I recommend checking the size of each .sql file after the dump finishes. Any file that is 0 byte or unusually small suggests that its db may be more heavily affected than the others. Go back and try a higher force_recovery level specifically for that one. Do not assume everything succeeded just because the command reported no error.

Reinitialize the entire system DB

Only after getting all the dumps is it time to dismantle things. This is the key part. I do not repair the original system DB; I discard it entirely and create a clean one, because mysql.global_priv and the other system tables are already damaged, and repairing them is much harder than recreating them. The steps are:

1. Stop mysqld completely, then rename the original data folder to data_corrupt and keep it for now. Do not delete it yet.
2. Run mysql_install_db.exe with datadir pointing to the original path to create a clean system DB.
3. If you edited my.ini, remove the innodb_force_recovery line, then start mysqld normally.
4. Load the per-db dumps back one file at a time, then recreate the phpMyAdmin user.

# 1) ย้าย data เดิมออก (อย่าลบ เผื่อต้องกลับมาขุดอีก)
move C:\xampp\mysql\data C:\xampp\mysql\data_corrupt

# 2) สร้าง system DB ใหม่ที่สะอาด ลง datadir เดิม
C:\xampp\mysql\bin\mysql_install_db.exe --datadir="C:/xampp/mysql/data"

Once mysqld starts normally (this time without force_recovery), load the dumps back in. Files dumped with --databases create the DB and run USE automatically, so they can be loaded directly:

# 3) โหลด dump ราย db กลับทีละไฟล์
C:\xampp\mysql\bin\mysql.exe -u root < D:\db-backup\perdb\shop_local.sql
C:\xampp\mysql\bin\mysql.exe -u root < D:\db-backup\perdb\crm_local.sql
C:\xampp\mysql\bin\mysql.exe -u root < D:\db-backup\perdb\blog_local.sql

The new system DB will not have the previous phpMyAdmin control user (pma). It needs to be recreated manually, or phpMyAdmin will complain about the control user when opened:

-- สร้าง control user ของ phpMyAdmin ขึ้นใหม่ (XAMPP default = ไม่มีรหัส)
CREATE USER 'pma'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'pma'@'localhost';
FLUSH PRIVILEGES;

After that, open each db and count its tables, then check the row counts in two or three main tables against what you remember. In my case, every database had all its data. The only thing gone was the original system DB, which I had deliberately discarded anyway.

Lesson: MariaDB did not simply break on its own

The point I want to stress is that the redo log was not corrupted because MariaDB is bad software. It was corrupted because the machine shut down while InnoDB was still writing, with no chance to finish the checkpoint properly. Setting MariaDB to run as a Windows service would not prevent this case either, because a blue screen means there is no clean shutdown regardless of how it runs.

Personally, I treat force_recovery as a salvage tool, not something to rely on. What I can actually rely on is a backup that runs every night. Anyone using this sequence can adjust the paths and db names to suit their own machine. If I have left anything out of the explanation or made a typo, I will come back and fix it later.