Atlantic.Net Blog

How to Check and Repair MySQL Databases and Tables

You may need to check or repair a database or table in MySQL when your website is corrupt or not accessible due to a database error. In this case, you can use the mysqlcheck tool to check and fix the corrupted table or database. mysqlcheck is a maintenance tool that allows you to check, repair, analyze and optimize MySQL tables via a command-line interface. One of the best features of using mysqlcheck is that you can perform database maintenance on a live website without stopping the MySQL service.

In this post, we will explain how to check/repair MySQL databases and tables.

Basic Syntax of mysqlcheck

The basic syntax of the mysqlcheck command-line tool is shown below:

mysqlcheck [OPTION] DATABASENAME TABLENAME -u root -p

A brief explanation of each option that you can use with mysqlcheck is shown below:

  • -c – Check a table for errors
  • -C – Check the tables that are changed after last week.
  • -a – Analyze tables.
  • -A – Check all databases.
  • -g – Check tables for version-dependent changes.
  • -B, –databases – Specify multiple databases.
  • -F – Check tables that are not closed properly.
  • –fix-db-names – Fix the database name.
  • –fix-table-names – Fix the table name.
  • -e – Perform an extended check.
  • -r – Repair a corrupted table.

Also Read

How to Connect to MySQL Using PHP

Check a Table in MySQL

Sometimes, you may need to check a specific table in a specific database. In that case, you can use the following syntax:

mysqlcheck -c db-name table-name -u root -p

For example, to check the students table in the class database, run the following command:

mysqlcheck -c class students -u root -p

You will get the following output:

class.students                                      OK

Check All Tables in MySQL

If you want to check all the tables in a specific database, use the following syntax:

mysqlcheck -c db-name -u root -p

For example, to check all tables in the class database, run the following command:

mysqlcheck -c class -u root -p

You should get the following output:

Enter password: 
class.teacher                                   OK
class.students                                      OK
class.peon                                       OK

Check and Optimize All Tables and All MySQL Databases

You can use the following command to check all tables and all databases:

mysqlcheck -c  -u root -p --all-databases

Output:

Enter password: 
class.teacher                                   OK
class.students                                      OK
class.peon                                       OK
guest.MyGuests                                     OK
movies.hotstar                                     OK
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK

You can also optimize all tables and all databases with the following command:

mysqlcheck -o root -p --all-databases

Output:

Enter password: 
class.teacher
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
class.students
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
class.peon
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
guest.MyGuests
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
movies.hotstar
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
mysql.columns_priv

In the above output, you should see “Table does not support optimize” which means the InnoDB table that doesn’t support this option.

Also Read

How to Delete or Remove Databases in MySQL

Repair/Fix MySQL Databases

To repair teacher tables in the class database, run the following command:

mysqlcheck -r class teacher -u root -p

Output:

mysqlcheck -r class teacher -u root -p
Enter password: 
class.teacher                                   OK

To repair all tables in both class and movies database, run the following command:

mysqlcheck -r --databases class movies -u root -p

Output:

Enter password: 
class.teacher                                   OK
class.students                                      OK
class.peon                                       OK
movies.hotstar                                     OK

If you want to check and repair all tables in all databases, run the following command:

mysqlcheck --auto-repair --all-databases -u root -p

Output:

Enter password: 
class.teacher                                   OK
class.students                                      OK
class.peon                                       OK
guest.MyGuests                                     OK
movies.hotstar                                     OK
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK

Note: By default, the InnoDB storage engine does not support repair. In this case, you will need to change the MySQL storage engine from InnoDB to MyISAM.

Conclusion

In this post, we explained how to check and repair a table in MySQL using the mysqlcheck command-line tool. Hopefully, this will help you to fix your corrupted tables. Try it on dedicated server hosting from Atlantic.Net!

Get a $250 Credit and Access to Our Free Tier!

Free Tier includes:
G3.2GB Cloud VPS a Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year