If you, like me, use backups for MySQL or SQL (I guess we all probably should!) then you can do daily backups of your .sql or .bak databases dumps and put them somewhere where you can access them in the (hopefully lack of) event of a disaster occurring with your database or server.
Now, I run a script (like the one I explained in an earlier post for
Scheduled SQL Server Backups ) which like most good ideas for backups, takes them away from the actual server - it never ceases to amaze me how some people consider a backup to the same server as the database as a valid way of performing incremental backups, anyway...so I have the SQL files backed up offsite, fine.
However, the database files I have backed up are becoming quite..chunky in size, one bumping the Gb mark and a few others climbing in size as content and complexity are added. This can cause a problem on the server I save the backups to, as I only pay for a limited amount of disk storage space and to be honest I (and my clients) will be unlikely to want to retrieve or restore a database which is older than a weeks worth of daily backups so I needed a way of easily trimming the list and keeping the backup number at a manageable size.
Getting the plan together
So looking at the specification of what I wanted to do:
- list the files I want to examine
- identify those older than 5 days
- delete them
- send me an email to let me know the task ran ok
- commit the job to Crontab
OK so first things first. The PHP we needed to use would list the files, look at modified date and remove (unlink in PHP terms) from the folder, easy enough:
$dateminusone = date("Y/m/d", strtotime( '-5 days' ) );
$list = glob('*.zip');
usort(
$list,
create_function('$a,$b', 'return filemtime($a) - filemtime($b);')
);
foreach($list as $file)
{
$filemoddate = date('Y/m/d', filemtime($file));
deloldfile($file,$filemoddate,$dateminusone);
}
sendconfirmation("me", "my email address");
breaking it down
$dateminusone = date("Y/m/d", strtotime( '-5 days' ) );
sets a variable to set a time to delete back to:
$list = glob('*.zip');
the PHP5.3 function "glob()" allows us to list files of a certain type in a particular folder
usort( $list, create_function('$a,$b', 'return filemtime($a) - filemtime($b);'));
I used the usort element in the script as I initially echoed the list back to the screen, but it was a handy way of listing the items in date ascending order according the modified time functio: filemtime(). After creating the usort array of files we iterated through the list with the foreach() loop and ran an internal function to delete the file
deloldfile($file,$filemoddate,$dateminusone);
If we look at the function deloldfile() in its glory we can see that it does a quick check on the modified date versus the set date and if it's older, it's deleted:
function deloldfile($file,$mdate,$startdate){
if( $startdate>$mdate ){
unlink($file);
#echo "marked for deleted";
} else {
#echo ":)";
}
}
Once that's done and the loop is complete, we just ping an email to me to tell me that it ran ok
sendconfirmation()
The function which emails me is a simple little SMTP email script which makes it more likely to be sent through an authenticated email account:
function sendconfirmation($name, $address){
$email = 'emailaddress;
$name = 'backup task' ;
$subject = 'SQL backups deleted successfully' ;
$content = '<html><body style="font-family:arial, sans-serif; font-size:12px">The SQL backup delete task ran successfully on :'.date("D dS M,Y h:i a",time())."<br />";
require_once('class.phpmailer.php');
$mail = new PHPMailer();
$mail->IsSMTP();
$mail->Host = "smtp_mail_server_name";
$mail->SMTPDebug = 1;
$mail->SMTPAuth = true;
$mail->Port = 25;
$mail->Username = "smtp_email_address_account_name";
$mail->Password = "account_password";
$mail->SetFrom($email, $name);
$mail->AddReplyTo($email, $name);
$mail->Subject = $subject;
$mail->MsgHTML($content);
$sendaddress = $address;
$sendname = $name;
$mail->AddAddress($address, $sendname);
if(!$mail->Send()) {
echo "Mailer Error: " . $mail->ErrorInfo;
} else {
}
}
This uses the excellent
SMTP PHPMailer library and I've never had any problems with this script.
Placing it all together and we have a working script which runs perfectly, so how can we automate this? We will create the Linux version of a Scheduled Task through a cron job. As my servers run off Plesk and Centos the UI is presented quite nicely and so long as you do the right set up of the task you should have no problems.
Creating the Cron Job
In Plesk the cron jobs are set up as "scheduled tasks" under tools and settings (10 & 11)
Click on Tools and Settings and then Scheduled Tasks and then set one up as the Root user (I've found problems can occur when running system commands as other users)
Click on Add a Scheduled Task and you should see something quite daunting like this completely unhelpful screen:
A personal word here, I've come to conclusion that Parallels and Plesk vendors do not want any old wannabe to access system things like tasks so they seldom offer any advice for setting them up: if yo mess it up then they have no wish to be held to account.
So setting up a cron job needs a little further explanation:
* means ALL THE TIME
that's the main explanation over. Seriously though, if you place * in all the fields for times the task will run EVERY minute until doomsday (or your server dies whichever is sooner) so you need to give some serious consideration to the frequency you want to execute the task.
For my situation, I wanted to run it once a week, around 11.00am on a Saturday so I have this:
This means that a 1 minute pass 11.00 every month on a Saturday my command will be executed.
So the command (my URL is blurred out) is:
/usr/bin/wget -O - -q http://URL_TO _RUN_FROM/deletebackups.php > /dev/null 2>&1
We want to run a command so we'll fire the WGET command line utility to open a fully declared web page ( A word to the wise, I attempted to set up cron jobs which fire a URL type command and have only ever got this to work well with WGET at the command line)
/usr/bin/wget because in the server these command line utilities tend to reside in the usr/bin folder
There are a number of switches which are available to append to the WGET directive, we are using:
-O - -q
-O : we need to tell the script that it will expect a file to have to interact with
-q : we want to run it in quiet mode, we don't really care what the response is as that is captured by the email alert at the end of the script
http://URL_TO _RUN_FROM/deletebackups.php kinda obvious but a fully qualified domain name and executable script
..and finally, your friend:
> /dev/null 2>&1
If you leave this out and your email address is the root address for your server you will get an email each time this script runs, not a problem if you are running it weekly, but some of my scripts run each 15 mins, I personally can't be bothered with getting emails each time every scheduled task runs!
Add in > /dev/null 2>&1 and this blocks in default email owner function.
Save that job and let it run!
