Looking to Access MySQL Via a Cron Job?

How do we keep this site running? This post may contain affiliate links — the cost is the same to you, but we get a referral fee. Compensation does not affect rankings. Thanks!

Code on screenIt 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:

// 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:

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, you would enter the following:

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

Alex bring a series of in-depth articles on search marketing and content management systems as well as troubleshooting tips to We Rock Your Web's collection. He is an avid tennis player, nature enthusiast, and hiker, and enjoys spending time with his wife, friends, and dogs, Bella and Lily.

Leave a Reply

6 Comments on "Looking to Access MySQL Via a Cron Job?"

Sort by:   newest | oldest | most voted
Great post, i actually googled “wp missed schedule repair cronjob” and came here – i can’t find a way how to implement those lines here from the original plugin or here it doesn’t matter =) function fix_missed_shedule() { global $table_prefix; $now_time = strftime(“%Y-%m-%d %H:%M:%S”, _NOW_TIME); $sql = “SELECT `ID` FROM `”.$table_prefix.”posts` WHERE `post_date_gmt` <= ‘”.$now_time.”‘ AND `post_status` LIKE ‘future’ LIMIT 0 , 35″; $result = mysql_query($sql); while ($row = mysql_fetch_assoc($result)) { wp_publish_post($row[‘ID’]); } } Important summary: – the limit of 35 is not necessary, just changing the “post_status” from “future” to “publish” is important on posts that match the time… Read more »

I am using your cron job script, but how do you issue the command to only delete cache files older than 2 days old? For example:

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

Delete only older than 2 days. If this is not possible in a cron command, how would you write a PHP script to accomplish the same task, or preferably a Windows .bat file? I use both Windows and Linux, but would rather do my admin tasks in Windows.

We Rock Your Web
We Rock Your Web

You can use the following cron commands to control time functions:

-mtime +2

In this case, +2 is 2 days. You can also, optionally, control size with the following command:

-size +10M

where +10M is 10 MB.

Hi, Thanks for the good tutorial… I need my cronjob to do more than one task. I need to truncate a table, then insert data back into the table… truncate.php looks as follows: <?php require_once(‘/usr/www/users/amisea/sosams/db-connecter.php’); $query = “TRUNCATE TABLE tmpsessionmodule; INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>”” ); INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>”” ); INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module3 FROM session WHERE ((Module3) <>”” ); INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module4 FROM session WHERE ((Module4) <>”” ); INSERT INTO tmpsessionmodule (SessionNo,… Read more »
a web rocker
a web rocker

Try this:



$query = array();

$query[] = “TRUNCATE TABLE tmpsessionmodule;”;

$query[] = “INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module1 FROM session WHERE ((Module1) <>\”\” );”;

$query[] = “INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module2 FROM session WHERE ((Module2) <>\”\” );”;

foreach ( $query as $sql )


$result = mysql_query($sql) or die(“can not query “.mysql_error()); // run the query

if ( $result )


// echo “<b>$sql</b> successfully. <br />”;




Not for Beginners, but Good. This article is about the process of how to access a MySQL (SQL for short) database by using a cron job. Now, if all this sounds like Greek to you, or too much tech speak, I am sure you are not alone. Quite frankly, I have only looked at the SQL tables on some of my websites very rarely. I mean rarely enough that I could probably count the times on one hand and have a finger or two left over! Despite all this, I am also sure there are quite a few advanced website… Read more »