Recovering crashed mysql(cpanel server) +innodb engine+ centos

livservers

Situations like crashing mysql and its recovery seem to be painful situation somehow, initially we can try all possible solutions, yet if we have no luck, please try below solution

step 1:Start mysql by setting mysql force recovery in /etc/my.cnf

innodb_force_recovery = 1

Try it for the values 2 and 3 but, try with 1 for better

step 2Start mysql server

service mysql start

step 3 .List all mysql DB in the server and write it to a file

mysql -e 'show databases' > /tmp/dbinfo.txt

step 4 : Exclude the databses information_schema and performance_schema and

for i in grep -wv Database /tmp/dbinfo.txt | grep -v '\.' | grep -v information_schema| grep -v performance_schema ; do mysqldump $i > /home/mysql/backup/$i.sql; done

or

simply,

for i in cat /tmp/dbinfo.txt; do mysqldump --skip-lock-tables $i >/home/mysql/backup/$i.sql; done

step 5. stop mysql server

service stop mysql

step 6. move all ibdata file to another location, otherwise while restarting it will use the corrupted innodb data.

cd /var/lib/mysql

mv ibdata* /home/mysql/recovery/

and Remove or comment out  the  innodb_force_recovery in /etc/my.cnf and start the server.

step 7 start mysql server

service mysql start

step 8. Once the backups are completed, delete all databases on the server

step 9. Restore the DBs

for i in cat /tmp/dbinfo.txt ; do mysql --lock-tables=false $i < /home/mysql/backup/$i.sql ; done

------------------------------------------------------/////////////////////////----------------------------------------------------------