How to repair a MySQL database

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.

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

How to import a MySQL Database using phpMyAdmin

How can I import a database? Log into your QuantmWeb Control Panel. Navigate to the...

MySQL Import and Export (.sql file) via SSH

Export A MySQL Database This example shows you how to export a database. It is a good idea...

MySQL Import and Export (.sql file) via PhpMyAdmin

Note: PhpMyAdmin can only import and export databases up to 50 MB in size. If your database is...

MySQL table marked as crashed and should be repaired

  If you are getting the following error message: Table ‘jos151_session’ is marked as crashed...

How to find the largest tables in MySQL

The largest tables are often the most promising candidates for optimization.Here is a query to...