Check and Optimize MySQL Database Automatically with
Crontab/Cron
MySQL is a very popular free yet powerful database system. But
even in great databases, the tables may get fragmented with
overhead due to continuous update, or delete and insert operation
on data stored in database. Beside, it’s also possibility
that the databases may get corrupted. Thus, performing health check
on database and optimize MySQL server regularly is an important
task.
It’s a bit troublesome if the
database administrators have to login to the server or launching
the phpMyAdmin to optimize the databases one by one or table by
table manually. Sometimes DB admin may simply forgets to do the job
or set the frequency of optimization to lesser times. It’s
recommended that every tables in MySQL databases are checked at
least once a day on a busy server.
It’s possible to automate the
optimization of MySQL process by using crontab function in
Linux/Unix/CentOS/FreeBSD. The cron job to check and optimize MySQL
databases can be created by using mysqlcheck client utility comes
MySQL installation. mysqlcheck client can checks, repairs,
optimizes, and analyzes tables in MySQL database.
To create a new cron job, login to the
server as root or any other user, and then edit the crontab file
(in most operating system, crontab -e will open crontab file in
default text editor) to add in the following line of text. For
users using cPanel, click on “Cron job” where you can
set up crontab at daily, hourly and other interval. Experience
webmasters can also set up a crontab file in rc.hourly or rc.daily
or other cron directory. Note that if you login as a MySQL or
normal user with no access privileges to all database, it’s
not possible to optimize all databases, unless user ID and password
for root is specified as in example below.
0 1 * * * mysqlcheck -Aao
–auto-repair -u root -p[password] > /dev/null
The above statement has the syntax similar
to “mysqlcheck [options] –all-databases”, where
–all-databases parameter is the default action is no
databases is specified thus can be omitted. The command will run
mysqlcheck client to automatically analyze and optimize all
databases at 1 am everyday. Note that there is not space between -p
and your password for root. You can change the running time to your
preference, and also change the options for mysqlcheck command. If
you just want to check and optimize certain databases or certain
tables without the database, use the following syntax:
mysqlcheck [options] db_name
[tables]
mysqlcheck [options] –databases
DB1 [DB2 DB3...]
You may want to remove –auto-repair
switch from the above command, as a table repair operation might
cause data loss under some circumstances the operation due to
causes include but are not limited to filesystem errors. For those
who has changed the character set and collation of MySQL databases
may also need to use –default-character-set option. More
information on all available switches and options available can be
found here.
|
