Apr 16 2009
Last update:

Cron Job - MySQL

Filed under: Web Programming » MySQL, Cron, PHP,
 

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() );
?>

Need to Organize? Visit Shoeboxed.com (Affiliate)

 

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 Smiling 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"

Your rating: None Average: 3.7 (19 votes)
Your rating: None Average: 3.7 (19 votes)
Email this page PDF of this page Print this page
Leave a comment
(6)



wp missed schedule cronjob to execute in DB

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 http://wordpress.org/extend/plugins/missed-schedule-wordpress-plugin-fix/ or http://wordpress.org/extend/plugins/wp-missed-schedule/ 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 before "date/time now"
- the connection to the database is like in your description, each blog gets his DB, USER, IP/HOST, TABLE

I have been looking for an answer on this question for 3 days. LOL

Cron to perform regular update of system files

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 owners who would be interested in knowing more about SQL and Cron jobs.

Very simply, a Cron job is just a process that is scheduled to execute itself at a given time and interval. This is a continuously repeating task, every day (or whatever interval you specify) at the same time without fail. A simple common task usually associated with cron is to perform a regular update of your system files. Another common job is to connect to the internet and download your email. Obviously, all of these jobs or tasks are performed automatically.

You may be wondering why someone would choose to use cron jobs. The main reason lies in its automation. If you have never run a website, or are only running a rather small site, you may not be aware of the amount of work that it may require. These repetitive tasks can almost seem overwhelming at times. In this respect, having Cron to help can be almost a lifesaver. It is powerful, like running scripts on a site. More importantly, once you get the knack for creating these jobs (which are really just lines of code added in the appropriate places), almost all of your daily administrative tasks can be put on automatic pilot.

Actually, the article does an excellent job of taking you through the actual code to use for a simple job used to truncate a cache table. I am quite sure that simply by following along, anyone who is really interested will be able to duplicate the results. While the job described in the article is to be performed on a site which is running the drupal content management system, it should be emphasized that this can actually be run on almost any type of system. Of course, this is a Unix based programming language, so just make sure that your server supports this (most do).

Assistance!

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, Module) SELECT SessionNo, Module5 FROM session WHERE ((Module5) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module6 FROM session WHERE ((Module6) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module7 FROM session WHERE ((Module7) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module8 FROM session WHERE ((Module8) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module9 FROM session WHERE ((Module9) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module10 FROM session WHERE ((Module10) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module11 FROM session WHERE ((Module11) <>"" );
INSERT INTO tmpsessionmodule (SessionNo, Module) SELECT SessionNo, Module12 FROM session WHERE ((Module12) <>"" );"
$result = @mysql_query($query);
?>

This does not work?! Can you please assist?
Thanks for your help!!

Regards,
Brad

 

Try this code

Try this:

<?php
require_once('/usr/www/users/amisea/sosams/db-connecter.php');

$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 />";
}
}
?>

Great Tutorial

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.

Time commands in cron

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.

The content of this field is kept private and will not be shown publicly.
 

We use this trick question to try and prevent automated spam comments.