Previously we talked about MyISAM and Innodb, that has importance in this context. mysqlcheck is a command line utility to check, repair, optimize MySQL Tables. mysqlcheck offers an efficient way to perform maintenance of the database tables. mysqlcheck checks, repairs, optimizes MySQL tables within the database. mysqlcheck intended to be used when the mysqld server is running. mysqlcheck executes the CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE statements in the most convenient way for the user.
Precaution To Use mysqlcheck To Check, Repair, Optimize MySQL Tables
If you are really new user, you must take the latest database backup on a test cloud server instance running exactly same version of MySQL with same my.cnf file settings. mysqlcheck demands some about handling database which we will discuss in this article. Furthermore, it is important whether you are using original MySQL or MySQL forks like MariaDB, Percona MySQL (we usually write guides to use Percona MySQL) etc. mysqlcheck was designed for MyISAM not Innodb. Other engine or storage mechanisms may not support all operations, in these cases, error messages will appear to inform you. We are no way talking about Percona MySQL unless specified. For Percona MySQL, you should search Percona community forum or ask questions there. We are talking about original MySQL with MyISAM engine :
1 | https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html |
mysqlcheck : Check, Repair, Optimize MySQL Tables
To check a specific table in a database, run :
---
1 | mysqlcheck -c database_name table_name -u root -p |
To check all tables in a database, run :
1 | mysqlcheck -c database_name -u root -p |
To check all tables in all databases, run :
1 | mysqlcheck -c -u root -p --all-databases |
To list specific databases, run :
1 | mysqlcheck -c -u root -p --databases database_name_1 database_name_2 |
To analyze a specific table in a database, run :
1 | mysqlcheck -a database_name table_name -u root -p |
To optimize table in a database, run :
1 | mysqlcheck -o database_name table_name -u root -p |
To repair table in a database, run :
1 | mysqlcheck -r database_name table_name -u root -p |
To run other commands, please get used with the basics and read official documentations. It is practical to create an unprivileged MySQL user to run scripts :
1 2 3 4 5 6 | mysql -u root -p # run on # mysql> create user 'mysqlcheckmaintenance'@'localhost'; grant select, insert, reload on *.* to 'mysqlcheckmaintenance'@'localhost'; flush privileges; |
