Using Cron to automate MySQL maintenance

We recently needed to go through a list of around 50 sites that we host through HostGator and optimize all of their MySQL databases. Going through each one and optimizing the database tables manually was a very unappealing process, so my trusty compatriot Keith worked out a process to use Cron jobs to automate the procedure. It took a bit of tweaking to get the right arguments in place, and a lot of looking around on the internet to fix a problem with conflicting arguments in the script, but in the end we triumphed… and man, is it awesome!

 

This is taken in the context of our site hosting in HostGator, but the process will be similar (or the same) for any site that uses CPanel.

 

First, log into your CPanel and look for the Cron jobs icon:

clip_image001

 

Update the email address that you want your Cron job to go to: in this case, we’re specifying the output, but it never hurts to have this set for any future jobs you want to run that don’t need to have a specific email output configuration. (more on this in a bit).

clip_image002

 

The next section is where you create your Cron job – in order to test and make sure everything is working, create a new job to run every 15 minutes. What, you don’t believe in testing scripts before releasing them into the wild? I… I don’t even know you any more!

image

 

Paste the following code into the command section:

mysqlcheck -u root -ppassword --auto-repair --optimize --all-databases 2>&1 | mail -s "MySQL Database Optimization" myemail

 

And click on the Add New Cron Job button

image

 

*Replace root with your username and password with your password – note that while there is a space between the –u and your username, there is no space between –p and your password, and (most importantly), there are two dashes on the auto-repair, optimize, and all-databases switches. Also replace myemail with the email address that you’d like to have the results sent to (myemail@domain.com).

 

Here’s what the individual sections mean:

mysqlcheck –u root –ppassword --auto-repair --optimize –all-databases

 

This section is pretty straightforward – runs a check on all your databases and repairs and optimizes them. If you’d like to run this against a single database, change —all databases to db_name or —databases [database1 database2] and so on.

 

2>&1 | mail -s "MySQL Database Optimization" myemail

 

This is the bit that outputs to an email address that you specify, and allows you to specify a subject for your email. If you leave this option off entirely, it will send an email upon completion to the email that you specified earlier, but it will dump the entire script into the subject line of your email, which includes your username and password – yikes!

 

If you’d like to not have an email sent at all, add this section right after all-databases:

>/dev/null 2>&1

 

Your new job will show up immediately below, and should look like this:

image

 

Once you’ve received a confirmation email, and everything is working – simply go back and create a new job that runs on whatever schedule you’d like – in our case, we chose the 1st and the 15th of every month, but you can use any timeframe you’d like.

image

image

 

Delete your test job, and you’re all set to go!

image

5 thoughts on “Using Cron to automate MySQL maintenance

  1. I am getting the following error when i run this,

    mysqlcheck -u username -ppassword –auto-repair –optimize –all-databases 2>&1 | mail -s “MYSQL Database Optimization” email

    I got the below eror:
    mysqlcheck: Got error: 1045: Access denied for user ‘cognknet’@’localhost’ (using password: YES) when trying to connect

    Like

    1. This sounds like a credentials error – are you sure that the username and password is correct one? Often times the database can have a different set of credentials than the CPanel. If it still doesn’t work after that, maybe try creating another user on that database and see if you can run the task with those credentials. Good Luck!

      Like

      1. Thanks for the response.
        I have given the same sort of comment line in another cpanel and i am getting the below error,

        I pasted the following code into the command section:

        mysqlcheck -u sun -pmhO7p2L’%§%Z§%$$”$TG$§$V%% –auto-repair –optimize –all-databases 2>&1 | mail -s “MySQL Database Optimization” email

        /bin/sh: -c: line 0: unexpected EOF while looking for matching `”
        /bin/sh: -c: line 1: syntax error: unexpected end of file

        Like

      2. It’s hard to tell whether or not the formatting has been changed by pasting the code into the comments box, but it looks like your code has curly quotation marks (like this – “MySQL Database Optimization”) if this is the case, make sure you retype them so they’re plain text ones, like this. "MySQL Database Optimization" Also, you’re putting two dashes — in front of auto-repair, optimize, and all databases, right? Sorry if this part seems obvious, just wanted to make sure.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.