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!