MySQL fix. Image via Flickr by Luis M. Gallardo D

Check, repair and optimize MySQL tables with mysqlcheck

The mysqlcheck client is a command line table maintenance program for MySQL. Mysqlcheck checks, repairs, optimizes, or analyzes MySQL tables. In this post I’ll show you how to use mysqlcheck to optimize all MySQL tables using a cronjob.

MySQL mysqlcheck

Use mysqlcheck when your MySQL table gets corrupted. Mysqlcheck is ideal for automated optimizations of MySQL databases and tables.

The mysqlcheck maintenance program can run under an unprivileged user who only needs the 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 (in my case, my maintenance user also needs reload as privilege):

mysql -u root -pCode language: Bash (bash)

Second, create the MySQL maintenance user and grant required privileges:

mysql> create user 'maintenance'@'localhost';
mysql> grant select, insert, reload on *.* to 'maintenance'@'localhost';Code language: SQL (Structured Query Language) (sql)

Third: flush all privileges to make it effective:

mysql> flush privileges;
Query OK, 0 rows affected (0.15 sec)Code language: SQL (Structured Query Language) (sql)

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/mysqlcheckCode language: Bash (bash)

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

That’s all basicly. Because mysqlcheck locks each table, making it unavailable to other sessions while it is being processed, you must not schedule this task on busy working day hours. Even 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.

Use mysqlcheck when your MySQL table gets corrupted.

mysqlcheck options:
MariaDB 5.5 doesn’t like multiple contradicting commands as mysqlcheck parameters, so something like the following would fail with an error: --auto-repair --check --optimize --all-databases

Protip: Donate $10, 20 or 30 through Paypal (or see my donate page) and support this site. Thank you <3

Jan Reilink

Hi, my name is Jan. I am not a hacker, coder, developer or guru. I am merely an application manager / systems administrator, doing my daily thing at Embrace - The Human Cloud. In the past I worked for clidn and Vevida. With over 20 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. I blog at

Oldest Most Voted
Inline Feedbacks
View all comments
01/01/2018 16:11

mysqlcheck: Got error: 1045: Access denied for user ‘maintenance’@’localhost’ (using password: YES) when trying to connect

Craig Engbrecht
Reply to  Jan Reilink
30/01/2021 08:31

Hey Jan,
I think your posts are VERY helpful, got a question though.
I am getting the same error as the guy above, I followed your example to the letter(I think), and the error is the same.
To my knowlege from what I am reading, there is no password being set, should I be setting a password, and where would said password be passed to the command?
Output from the command using a run command now option:
mysqlcheck: Got error: 1045: Access denied for user ‘maintenance’@’localhost’ (using password: YES) when trying to connect