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
/usr/local/bin/mysql_install_db
chown -R mysql:mysql /usr/local/var
chown -R mysql:mysql /usr/local/var
/usr/local/bin/mysql_install_db
chown -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.
You Might Also Like :
0 comments:
Post a Comment