How to optimize MySQL tables automatically using cron

Busy websites which has a lot of insert/delete transactions may introduce fragmentation in MySQL tables. Fortunately, users and optimize mysql tables with ‘OPTIMIZE TABLE’ command, but how to execute it automatically?

Here’s how:
The mysql-client package in Ubuntu installation comes with a tool called mysqlcheck which is handy for optimizing table in mysql. This command can be executed from bash and can be executed using cron.

to do that, just run this command.

[bash]
cron -e

#in the crontab file– add this line
59 23 * * * /usr/bin/mysqlcheck -o -v -u <mysql username> -h localhost <database_name> -p <password>
[/bash]

This will tell cron to execute mysqlcheck and optimize mysql table of the specified database exactly on 11:59pm, every day. You can change the setting to suit your need.