Occasionally, due to errors or bugs in the MySQL database tables, one can come across various unknown issues. Whenever a database table gets corrupted, the most important thing is to determine the issue behind it. In this tutorial, we will provide step by step explanation on how to repair MySQL database. Let’s get started, there are 3 ways you can choose from.
1) Through cPanel:
Log in to cPanel and go to MySQL Databases.
In the section “Modify Databases”, select a database from the drop-down box next to “Repair DB”: and then click the “Repair DB” button.
If the database engine supports repair (for example, MyISAM supports it), a list of the repaired tables within the database will show up.
If the database engine does not support repair (e.g. InnoDB database engine), the following error message will appear:
note: The storage engine for the table doesn’t support repair
2) Through phpMyAdmin:
Open phpMyAdmin tool and select the database whose tables need to be repaired.
A list with all the database’s tables will appear. Tick the tables that need repairing, or simply click “Check All” to select all tables.
Click the box “With selected:” and choose “Repair table”. This will execute the REPAIR TABLE SQL query on the selected tables and they will be repaired.
3) Through console:
Although, Mysqlcheck and Myisamchk are similar in purpose, there are some essential differences. Mysqlcheck as well as Myisamchk can check, repair and analyze MyISAM tables. Mysqlcheck can also check InnoDB tables.
Log in as ‘root’ via SSH and:
Shows you if any need repair:
myisamchk --check /var/lib/mysql/*/*.MYI |
Then try ‘safe-recover’ first:
myisamchk --safe-recover /var/lib/mysql/*/*.MYI |
and, if neither that nor this works:
myisamchk --recover /var/lib/mysql/*/*.MYI |
Then use the ‘force’ flag:
myisamchk --recover --extend-check --force /var/lib/mysql/*/*.MYI |
If those are not MyISAM tables try Mysqlcheck:
mysqlcheck --all-databases -r #repair databases mysqlcheck --all-databases -a #analyze databases mysqlcheck --all-databases -o #optimize databases |
to repair one database:
mysqlcheck -ro database_name |
to repair one table in database:
mysqlcheck -ro database_name table_name |
That is it. Everything should be fixed now. In case of any questions please do contact us, our support engineers are 24/7 to answer all your questions.