It is ok to use InnoDB if your server only have a few database. But what about if you have many database that use InnoDB.
According to Cpanel forum, InnoDB can be repair according to their guide. However my method is easier and it is alternate method to repair MySQL.
This guide however intended for admin who is familiar with MySQL administration and have deep knowledge with Linux. Use at your own risk.
This guide using script to backup InnoDB database and restore database that can be obtain here. This script can also be use in a cron job.
Recently one of my server MySQL down and cannot be started at all. Only force recovery 6 can start up the MySQL. Using force recovery higher than 4 can permanently corrupt the data.
So what I do after set force recovery 6, I use the script to backup all InnoDB databases in one folder. Create to copy of the database in a separate folder because we will modified the sql query of the database.
root@svr4 [/]# mkdir innodbbak innodbbak1 root@svr4 [/]# cd innodbbak root@svr4 [/innodbbak]# ./innodbbak.sh *You need to edit 'innodbbak.sh' script for the InnoDB dump location
This script then will backup all the InnoDB database.
What I will do is changing the InnoDB database engine to MyISAM. But before I do that, I need to rename some of the database.
mv mysql.sql mysql.sqlx ; mv roundcube.sql roundcube.sqlx ;
I will change the InnoDB database to MyISAM engine using the following command;
root@svr4 [/innodbbak]# sed -i 's/InnoDB/MyISAM/g' *.sql
Now all the sql query have been change from InnoDB to MyISAM.
What to do now is to restore the database back. Use restore script that I give at the earlier post.
root@svr4 [/innodbbak]# ./restoredb.sh
If during restore you find database restore error similar like;
Specified key was too long
Just copy the all the database name because we need to convert back the database to InnoDB. Actually it also can be repair by changing charset and collate but it is more complicated and will not be discussed here.
In my case I found at least 6 database have this error.
Once the database restoration completed what we need to do now is to focus on the remaining failed databases.
Edit back my.cnf and remove force recovery. Stop the MySQL server. Rename;
mv ibdata1 ibdata1a mv ib_logfile0 ib_logfile0a mv ib_logfile1 ib_logfile1a
Start back MySQL. ibdata1,ib_logfile0 and ib_logfile1 should be automatically created.
Now we should dump database mysql.sqlx and roundcube.sqlx that we NEVER change the database engine to MyISAM. But first we must remove the *.ibd data inside that folder using batch rename.
root@svr4 [/var/lib/mysql]# pwd /var/lib/mysql root@svr4 [/var/lib/mysql]# cd mysql root@svr4 [/var/lib/mysql/mysql]# for x in *.ibd; do mv "$x" "${x%.ibd}.ibdx"; done
Then restore the database using command.
mysql mysql < mysql.sqlx
Do the same for Roundcube database.
For the remaining database that failed to restore just change back to InnoDB engine using command;
root@svr4 [/innodbbak]# sed -i 's/MyISAM/InnoDB/g' faileddatabase.sql root@svr4 [/innodbbak]# cd /var/lib/mysql root@svr4 [/var/lib/mysql]# cd faileddatabase root@svr4 [/var/lib/mysql/faileddatabase]# for x in *.ibd; do mv "$x" "${x%.ibd}.ibdx"; done root@svr4 [/var/lib/mysql/faileddatabase]# cd /innodbbak root@svr4 [/innodbbak]# mysql faileddatabase < faileddatabase.sql