inertz.org

This method that I use proven as alternate solution to repair MySQL. As we all know if anything problem with InnoDB database corruption the whole MySQL database will down. My method will change the InnoDB database engine to MyISAM or at least reduce the amount of database which is using InnoDB engine.
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.

[email protected] [/]# mkdir innodbbak innodbbak1
[email protected] [/]# cd innodbbak
[email protected]4 [/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;

[email protected] [/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.

[email protected] [/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.

[email protected] [/var/lib/mysql]# pwd
/var/lib/mysql
[email protected] [/var/lib/mysql]# cd mysql
[email protected] [/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;

[email protected] [/innodbbak]# sed -i 's/MyISAM/InnoDB/g' faileddatabase.sql
[email protected] [/innodbbak]# cd /var/lib/mysql
[email protected] [/var/lib/mysql]# cd faileddatabase
[email protected] [/var/lib/mysql/faileddatabase]# for x in *.ibd; do mv "$x" "${x%.ibd}.ibdx"; done
[email protected] [/var/lib/mysql/faileddatabase]# cd /innodbbak
[email protected] [/innodbbak]# mysql faileddatabase < faileddatabase.sql