Thursday, May 15, 2014

Top 3 methods to Repair MySQL Tables

Corruption in MySQL database table can happen due to any uncertain reasons, and some of the top reasons are bugs in MySQL itself, virus attack, power failure, hardware problem, OS problem etc. If this happens with you then to repair MySQL table is the only solution you can try. Note that MySQL table repair may cause of valuable data loss because of limited functionalities of in-build MySQL repair utility or table has been badly corrupted. So make a proper backup of your MySQL database before trying any repair method.

When you have verified that your MySQL table gets corrupt, use one of the following methods to repair it.
  1. Repair MySQL Table using MySQLCheck
  2. Repair MySQL Table using phpMyAdmin
  3. Repair MySQL Table using 3rd party Software
Repair MySQL Table using MySQLCheck: It is a table maintenance program that checks, analyzes, repairs & optimizes MySQL tables. MySQLCheck is similar to myisamchk, but myisamchk must be run when MySQL server is running while MySQLCheck must be run when MySQL server is not running.

Check MySQL Table
# mysqlcheck -c database-name table-name -u root -p
Enter password:
database-name.table-name OK

Repair MySQL Table
# mysqlcheck -r database-name table-name -u root -p
Enter password:
database-name.table-name OK

Repair MySQL Table using phpMyAdmin: It is the easiest method to fix corrupt MySQL table. Follow below steps to repair corrupt MySQL table:

  • Open your phpMyAdmin client, enter the username & password then click on 'Go' button as shown in the image.

  • Select your MySQL database from left drop-down menu and click on the desired database as shown in the image.

  • Now you will see all of the tables of your selected MySQL database. In below image, there are 11 tables for the selected database.

  • Tick-mark on the check-boxes of tables that you want to repair. For example, I have selected 'wp_options' table as shown in the image.

  • Now click on the drop-down arrow of 'with selected' option and click on 'repair table'. Take a reference from below image.

  • After that you will get a conformation message saying that “your sql-query has been executed successfully”.
Repair MySQL Table using 3rd Party Software: There is platy of 3rd party MySQL repair utility available online that claims to repair & recover data from corrupt MySQL table. But I generally recommend Stellar Repair for MySQL because it is trusted software to repair corrupt MySQL tables. It uses advanced algorithm to scan & repair corrupt tables. Software does not change anything of your system because it is read-only in nature. Software repairs myisam & innodb storage engine's table and supports MySQL 6.x, 5.x, 4.x, and 3.x versions. If you know & believe any other software that can do your job they go with that. Choose the right software that will make you happy.

Your Turn: I love to hear from you guys! Share your opinion through comment.

1 comment:

  1. Thanks a lot Mark willium. I normally use phpMyAdmin to repair MySQL tables but the database is too big and it times out. I tried 1st method from your post and successfully repaired the database. You have clearly explained all the required steps.