Wednesday, December 17, 2008

How to Fix MySQL Database (MyISAM / InnoDB). Part 2

12/17/2008 09:13:00 AM |

Recently I was faced with the daunting task of reparing an InnoDB database gone bad. The database would not start due to corruption.

First step was turning-on InnoDB force-recovery mode, where InnoDB starts but ignores all UPDATEs and INSERTs.

Add this line to /etc/my.cnf:
innodb_force_recovery = 2

Now we can restart the database:
/usr/local/bin/mysqld_safe &

(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = 8)

Save all data into a temporary alldb.sql (this next command can take a while to finish):
mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql

Shutdown the database again:
mysqladmin -uUSERNAME -pPASSWORD shutdown

Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)
rm -fdr /usr/local/var

Recreate the database directory and install MySQL basic tables
mkdir /usr/local/var
chown -R mysql:mysql /usr/local/var
-R mysql:mysql /usr/local/var

Remove innodb_force_recovery from /etc/my.cnf and restart database:
/usr/local/bin/mysqld_safe &

Import all the data back (this next command can take a while to finish):
mysql -uroot --compress < /usr/alldb.sql

And finally - flush MySQL privileges (because we're also updating the MySQL table)
/usr/local/bin/mysqladmin -uroot flush-privileges

Note: For best results, add port=8819 (or any other random number) to /etc/my.cnf before restarting MySQL and then add --port=8819 to the mysqldump command. This way you avoid the MySQL database getting hit with queries while the repair is in progress.

AddThis Feed Button
Bookmark and Share

You Might Also Like :