Check, repair and optimize MySQL tables with mysqlcheck

MySQL’s mysqlcheck is a command line table maintenance program: it checks, repairs, optimizes, or analyzes tables. Very handy for automated performance optimizations of MySQL databases and tables.

The mysqlcheck maintenance program can run under an unprivileged user who only needs SELECT and INSERT privileges.

To use mysqlcheck in an automated and unattended task, we use a cronjob to optimize all MySQL databases on a daily or weekly basis. The mysqlcheck utility optimizes tables and databases, which means that fragmented tables are defragmented.

Create an unprivileged MySQL user

The first step is to create an unprivileged MySQL user, so log on to your mysql console:

$ mysql -u root -p
mysql> create user 'maintenance'@'localhost';
mysql> grant select,insert,reload on *.* to 'maintenance'@'localhost';

And flush all privileges to make it effective:

mysql> flush privileges;
Query OK, 0 rows affected (0.15 sec)

Cronjob for MySQL maintenance

The cron software utility is a time-based job scheduler in Unix-like computer operating systems. We use cron to schedule jobs (commands or shell scripts) to run periodically at fixed times, dates, or intervals.

In order to run the maintenance unattended and automated, we create a cronjob for it:

echo "0 4 * * Sun root mysqlcheck -u maintenance --optimize --all-databases" > /etc/cron.d/mysqlcheck

This cron runs every Sunday morning at 04:00 AM. The systems root user starts mysqlcheck under MySQL’s user ‘maintenance’.

That’s all basicly. Because mysqlcheck locks each table, and therefore unavailable to other sessions while it is being processed, you must not schedule this task on busy working day hours. Even though for check operations, the table is locked with a READ lock only. Table maintenance operations can be time-consuming, particularly for large tables. If you use the --databases or --all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time.