Recovering a Crashed MySQL Server Using innodb_force_recovery and Data Export/Import

In many development environments we rely on storage services such as MySQL, MongoDB, and Redis. Without a dedicated DBA, regular backups are often overlooked, creating security risks. The author describes a recent incident where a server crash left MySQL unable to start because no backup existed.

After investigating the issue on machine A, the author identified typical MySQL crash symptoms—corrupted logs and data files—and proceeded to resolve them step by step.

The key to restarting MySQL was setting the innodb_force_recovery parameter in /etc/my.cnf . The parameter accepts values from 1 to 6, each adding more aggressive recovery behavior:

1 (SRV_FORCE_IGNORE_CORRUPT): ignore corrupted pages.

2 (SRV_FORCE_NO_BACKGROUND): stop background threads.

3 (SRV_FORCE_NO_TRX_UNDO): skip transaction roll‑back.

4 (SRV_FORCE_NO_IBUF_MERGE): disable insert‑buffer merge.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): treat uncommitted transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO): skip redo log.

When the value is greater than 0, read‑only operations such as SELECT , CREATE , and DROP are allowed, but INSERT , UPDATE , and DELETE are prohibited.

Setting innodb_force_recovery=4 allowed MySQL to start successfully. The next step was to export the data:

/usr/local/mysql/bin/mysqldump -uroot -h localhost -P 3306 -p --default-character-set=latin1 --add-drop-database --lock-tables --single-transaction -q --triggers -A --result-file=/home/fuhaitao/3306.sql

The dump file was then transferred to machine B and imported with:

mysql -uroot -p -S /tmp/mysql3333.sock --default-character-set=latin1 < 3306.sql

During import the data appeared garbled because the source database used UTF‑8 while the dump was created with the Latin1 charset. The author corrected this by exporting the database with the appropriate charset (UTF‑8) and re‑importing, ensuring consistent encoding across source, dump file, and target server.

After a successful import, the author verified the data, stopped MySQL on machine A, reset innodb_force_recovery to 0 in /etc/my.cnf , and restarted the service, fully restoring five years of data.

Finally, the article offers three practical recommendations: use a DBA team when possible, schedule regular database backups, and always keep character set settings consistent when exporting and importing data.