Cron Job - MySQL

Looking to Access MySQL Via a Cron Job?

It can be handy to use a cron job (a process that executes at a specified interval and repeats) to perform MySQL tasks. In particular, we're going to look at how to use a cron job to execute MySQL functions.

Using Cron Jobs to Schedule MySQL Commands

In our example, we've deployed a Drupal site that is still running the old 4.7.x version. Due to a high traffic load, we have the site's chaching mechanism turned on. The problem is, unlike in Drupal's 5.x version, the cache table seems to grow out of control. Because internal Drupal functions aren't working as they should, we're going to access our MySQL database directly to truncate the cache table periodically. In this case, every day.

To setup a cron tab, you can access your Apache server via shell, or you can log into cPanel, as in our example, and select Cron Jobs. We select the Advanced (Unix Style) cron interface, and then proceed to select a timeframe for the cron job to execute. 0 0 * * * will have the cron job execute every day at midnight (based on the server's timezone settings).

Cron Job - MySQL : Truncate Database Table

Now that we have our time interval set, we're going to point the Cron tab at our actual PHP files, which will be doing the heavy lifting. We're going to use one PHP file to connect to our database, and another to execute a MySQL statement to truncate our cache table. You'll need the following files:

db-connect.php file with:
<?php
// set db access info as constants
define ('DB_USER','username');
define ('DB_PASSWORD','pw');
define ('DB_HOST','localhost');
define ('DB_NAME','dbname');

// make connection and select db
$dbc = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );
mysql_select_db(DB_NAME) OR die ('Could not select the db: ' . mysql_error() );
?>

truncate.php file with:
<?php
require_once('/home/ffsports/mysql/db-connect.php'); // connect to db
$query = "TRUNCATE TABLE cache";
$result = @mysql_query($query); // run the query
?>

Of course you can substitute in any MySQL functions you wish to execute above.

Putting it all together - now you'll want to link to these files via Cron tab. Back on the cPanel Cron Job page, you'll want to enter the following in the Command field, substituting your own values for the path to your PHP file.:

00*** /usr/local/bin/php -q /home/user/includes/truncate.php

Voila You've setup a Cron Job that will execute your MySQL statement at the specified interval. Since this was such a simple MySQL command, you could have executed the entire thing in the Cron Job Command line. We'll show you how this is done in the next example.

MySQL Cron Job - Direct Command

For simple MySQL commands, you don't need to link to a separate PHP file. You can both access your MySQL database and execute the Cron Job within Cron's Command line as follows:

mysql -uUSER -pPWD -hDB-HOSTNAME/IPADDRESS -e "truncate table db.cache"

For example, if your username is "root", your password "password", and your IP address 192.168.1.1, you would enter the following:

mysql -uroot -ppassword -h192.168.1.1 -e "truncate table db.cache"