How to use mysqlcheck when your MySQL table gets corrupted. Check & repair tables with MySQL’s mysqlcheck, a command line table maintenance program. 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.
Mysqlcheck is ideal for automated optimizations of MySQL databases and tables.
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.
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 -p
Create the MySQL maintenance user and grant required privileges:
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)
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 (in one line):
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 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
--all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time.
Note: 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
If you want to step in to help me cover the costs for running this website, that would be awesome. Just use this link to donate a cup of coffee ($5 USD for example). And please share the love and help others make use of this website. Thank you very much!
My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.
Vulnerabilities in .NET Core, ASP.NET Core Could Allow Elevation of Privilege
Benchmarking WordPress, simple load & speed testing with ApacheBench
MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!
Recursive scp and symlinks
Install Varnish Cache on CentOS 6.7
Install Elasticsearch on CentOS 6.7