Sometimes in the MySQL database tables got corrupted. We use the mysqlcheck command to check and repair the database tables. Mysqlcheck command is one of the useful commands to check, repair, optimize, and analyzes the database tables. Mysqlcheck command can be executed when the MySQL daemon is running. We do not need to stop the MySQL service to run the mysqlcheck command. Learn How to Check and Repair MySQL Tables Using Mysqlcheck
Useful Mysqlcheck Options
Some key options that we can use with mysqlcheck.
- -A (–all-databases) – It will include all the databases.
- -a (–analyze) – It will analyze the tables.
- –auto-repair – It will automatically check and repair the table in the database, if it is corrupted.
- -B (–databases) – If we want to check one or more than one database.
- -c (–check) – To check the errors in table.
- -C (–check-only-changed) – To check tables that are changed since last check.
- -e (–extended) – To perform extended check on a table. It can take a long time to execute.
- -F (–fast) – To check the tables that are not closed properly.
- –fix-db-names – To fix the DB names.
- –fix-table-names – To fix the table names.
- -f (–force) – To continue even when there is an error.
- -g (–check-upgrade) – To check for the version dependent changes in the tables.
- -m (–medium-check) – It will check faster than extended check option, but does most checks.
- -o (–optimize) – To optimize the tables.
- -q (–quick) – It will check faster than medium check option.
- -r (–repair) – To fix the corrupt table.
- -1 (–all-in-1) – It will use one query per database, with tables listed in a comma separated way.
Check a Specific Table in a Database
If you are facing an error for a particular table in the database, run the following command to check a particular table is corrupted or not in the MySQL database.
mysqlcheck -c database_name table_name -u root -p'Password'
Enter the password of the Root user in the place of Password in the above command.
Check All Tables in a Database
To check all the tables of a particular MySQL database, run the following command with the Database name.
Replace the database_name with your database name, and replace Password with MySQL root password in the below command.
mysqlcheck -c database_name -u root -p'Password'
Check All Tables and All Databases
To check all the databases with their tables, use the -–all-databases attribute with -c option as shown in the below command.
mysqlcheck -c -u root -p'Password' --all-databases
If you want to check only a few databases with their tables, use the –databases attribute followed by the database names. Separate the Database name with space, as shown in the below command.
mysqlcheck -c -u root -p'Password' --databases database1 database2
Analyze Tables using Mysqlcheck
To analyze, the particular table of a particular database, run the following command. Mention the database and the table name in the above command.
mysqlcheck -a database_name table_name -u root -p'Password'
To analyze all the tables of a particular database in the MySQL server.
To analyze all the tables of all the databases in the MySQL server.
Optimize Tables using Mysqlcheck
To optimize a particular table of a database, run the following command.
mysqlcheck -o database_name table_name -u root -p'Password'
When we execute the above command, mysqlcheck command uses the OPTIMIZE TABLE and defragment the data file to get the unused space. It will improve the performance of the tables.
Repair Tables using Mysqlcheck
To repair particular tables of a database, then run the following command.
mysqlcheck -r database_name table_name -u root -p'Password'
When we execute the above command, mysqlcheck command use the REPAIR TABLE to repair and fix the corrupted tables.
Combine Check, Optimize, and Repair Tables
Instead of checking and repairing one by one, we can combine the check, repair, and optimize functions with the help of –auto-repair as mentioned in the below command.
The following command automatically checks, optimize and repair all the tables of a database.
mysqlcheck -u root -p'Password' --auto-repair -o database_name
To automatically check, optimize and repair all the tables of all databases, run the following command.
mysqlcheck -u root -p'Password' --auto-repair -o --all-databases
To check the logs, that is what the command is doing. Simple add –debug-info as shown in the below command.
mysqlcheck --debug-info -u root -p'Password' --auto-repair -o database_name table_name
Conclusion
In this tutorial, we have learned how to Check and Repair MySQL Tables and how we can check the corrupted tables in the database and repair them, how we can analyze and optimize the tables databases. You can also visit our popular MySQL blogs such as MySQL installation, Manage MySQL user and database, MySQL backup and restore.
If you guys have any queries related to this tutorial, then let me know in the comments section.
Need Any Solution Related to Server Hosting Please Visit Serverpoet