Home » articles » 18 March 2019 » 1,874 supporters » 3 Comments »

MySQL down because of InnoDB

18 March 2019 1,874 supporters 3 Comments

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.

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



3 Comments »

  • Charli said:

    Having read this I believed it was really informative.
    I appreciate you spending some time and energy to put this article together.
    I once again find myself personally spending way too much time both
    reading and leaving comments. But so what, it was still worthwhile!

  • brawlstars.yolasite.com said:

    I am really impressed with your writing skills as well as
    with the layout on your blog. Is this a paid theme or did you customize it yourself?

    Anyway keep up the nice quality writing, it is rare to see a
    great blog like this one today.

  • Jamesaddek said:

    Hi, inertz.org

    I’ve been visiting your website a few times and decided to give you some positive feedback because I find it very useful. Well done.

    I was wondering if you as someone with experience of creating a useful website could help me out with my new site by giving some feedback about what I could improve?

    You can find my site by searching for “casino gorilla” in Google (it’s the gorilla themed online casino comparison).

    I would appreciate if you could check it out quickly and tell me what you think.

    casinogorilla.com

    Thank you for help and I wish you a great week!

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.