Difference between revisions of "How to recover corrupted MySQL data"
From Kolmisoft Wiki
Jump to navigationJump to search
| Line 1: | Line 1: | ||
= Description = | |||
Sometimes MySQL data gets corrupted. | Sometimes MySQL data gets corrupted. | ||
| Line 5: | Line 7: | ||
After that when MySQL process is started, it keeps crashing. | After that when MySQL process is started, it keeps crashing. | ||
Example how it looks in the /var/log/mysql.log: | Example how it looks in the ''/var/log/mysql.log'': | ||
[[File:mysql_crashing_log.png|400px]] | [[File:mysql_crashing_log.png|400px]] | ||
<br><br> | |||
= Recovery mode = | |||
Solution to this is into file /etc/my.cnf enter such line: | Solution to this is into file ''/etc/my.cnf'' enter such line: | ||
innodb_force_recovery = 4 | innodb_force_recovery = 4 | ||
| Line 21: | Line 25: | ||
[[File:mysql_recovery_on_log.png]] | [[File:mysql_recovery_on_log.png]] | ||
Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored. E.g. DB is in read-only mode. | |||
<br><br> | |||
= Data retrieval = | |||
Try to retrieve data using mysqldump command: | |||
mysqldump -u mor -pmor mor > mor.sql | |||
It is big chance that at some point it will stop showing which table is corrupted. | |||
In our example it is [sessions] table: | |||
[[File:mysql_failed_dump.png]] | |||
Revision as of 18:40, 13 May 2011
Description
Sometimes MySQL data gets corrupted.
It often happens when HDD space runs out.
After that when MySQL process is started, it keeps crashing.
Example how it looks in the /var/log/mysql.log:
Recovery mode
Solution to this is into file /etc/my.cnf enter such line:
innodb_force_recovery = 4
and restart MySQL process:
/etc/init.d/mysql restart
After that log should show that recovery mode is turned on:
Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored. E.g. DB is in read-only mode.
Data retrieval
Try to retrieve data using mysqldump command:
mysqldump -u mor -pmor mor > mor.sql
It is big chance that at some point it will stop showing which table is corrupted.
In our example it is [sessions] table:
