How to repair all MySQL Databases on a cPanel/WHM Server
There can be different reasons why one wants to repair a MySQL database. Database corruption maybe due to a lack of disk space or an unexpected server shutdown that caused the database to go bad. Queries that worked before suddenly stopped working and overall the database is not behaving anymore as expected. This guide shall not be a full database troubleshooting guide, but rather give you a hand on doing a MySQL repair on all databases on a cPanel/WHM server (or any other server running MySQL).
The situation: Your server crashed hard and when it is back up the MySQL side of the house is not working anymore. You do your normal troubleshooting. If all other troubleshooting and verifications are done, you come to the conclusion that you will need to run a repair as the next step. A full database restore from a (hopefully existing) good database backup would be your last choice.
Repairing one MySQL database is fairly easy, but how do you do this for an entire server holding20, 30, 50 or over 100 databases? You will need SSH access as the root user to the server running MySQL. So, log in as the root user and then do the following to repair your MySQL databases:
Option #1
Check if you any need DB repair:
myisamchk –check /var/lib/mysql/*/*.MYI
Then try a ’safe-recover’ as the first step:
myisamchk –safe-recover /var/lib/mysql/*/*.MYI
If the ‘safe-recover’ does not work, run a full recover:
myisamchk –recover /var/lib/mysql/*/*.MYI
Then use the ‘force’ flag to get things back to normal:
myisamchk –safe-recover –extend-check –force /var/lib/mysql/*/*.MYI
or:
myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI
Option #2
Use the mysqlcheck tool to repair your databases.
mysqlcheck –all-databases -r #repair
mysqlcheck –all-databases -a #analyze
mysqlcheck –all-databases -o #optimize
There you have it. By using one of these two options you should be able to repair and recover your databases. Please be advised that you should backup critical databases before running any of the commands above. If something goes wrong you can revert back and try again.
Related posts:
- Howto: Upgrade Cpanel To Latest Mysql
- Quick Cpanel/WHM/Server Troubleshooting
- How to move a large forum from Server A to Server B?
- Which Firewall Ports are needed to be open on a cPanel/WHM Server
- How to Secure your Dedicated Server (RedHat Linux + Cpanel)
- Restoring Domain Accounts and Server Config Files with WHM / cPanel
- Your Dedicated Server just Died. What do you do?
{ 2 comments… read them below or add one }
Hey, good post, but for the second option it should be the following:
mysqlcheck –all-databases -r #repair
mysqlcheck –all-databases -a #analyze
mysqlcheck –all-databases -o #optimize
(Notice two dashes –)
I just checked and it is how Wordpress displays the text. When I go and edit the posting it shows the 2 dashes. I have to check if there is a better way to display the commands to that it becomes more visible. Anyway – thanks for pointing this out.
Christoph