Automatically backup your databases on cPanel using cronjobs

July 29, 2009 9:12 pm Published by 4 Comments

Have you ever wondered how to backup all your cPanel databases automatically and have it emailed to you using a cronjob? As someone who works on a lot of sites hosted with cPanel I have!

cPanel does have a handy backup facility built into it, but there are two major drawbacks. Firstly it will only backup your data to another location on the same host – fine for recovering development mistakes, but not a very effective disaster recovery option because if your server goes down you lose everything. Secondly there is no scheduling feature so you can’t just set and forget; so what are the chances of you logging on every day to manually backup!?

I had to dig around a bit and experiment with some code but I’ve finally found a solution that will backup all the databases from my cPanel hosting account and email them to me – what’s more it can easily be configured as a cron job so you can literally set and forget!

The code for the script is as follows:

#!/usr/bin/perl

use strict;
use MIME::Lite;
use DBI;

## open(STDOUT, “>/dev/null”);
## open(STDERR, “>/dev/null”);

## Set your e-mail address
my $email = ‘youremail@youremail.com’;

## Set the message of the backup e-mail
my $subject = ‘Database Backup’;

## Set your cpanel username
my $user = ‘yourusername’;

## Set your cpanel password
my $pass = ‘yourpassword’;

my @databases;

my $dbh = DBI->connect(‘DBI:mysql::localhost’, $user, $pass);
my $sth = $dbh->prepare(“show databases”);
$sth->execute;
while (my($db) = $sth->fetchrow_array) {
push(@databases, $db);
}
$sth->finish;
$dbh->disconnect();

if (!-d “/home/” . $user . “/sqldumps”) {
system(“mkdir”,”-p”,”/home/” . $user . “/sqldumps”);
}

foreach my $database (@databases) {
system(“/usr/bin/mysqldump -u ” . $user . ” -p'” . $pass . “‘ ” . $database . ” >/home/” . $user . “/sqldumps/” . $database . “.sql”);
}

my $mime_msg = MIME::Lite->new(
To => $email,
Subject => $subject,
Type =>’multipart/mixed’
);

foreach my $database (@databases) {
$mime_msg->attach (
Path => “/home/” . $user . “/sqldumps/” . $database . “.sql”,
Disposition => ‘attachment’
);
}
MIME::Lite->send(‘sendmail’, “/usr/sbin/sendmail -t -oi”);
$mime_msg->send();

foreach my $database (@databases) {
unlink(“/home/” . $user . “/sqldumps/” . $database . “.sql”);
}

close(STDERR);
close(STDOUT);

  1. Copy and paste this code into a text editor and save it as backup.pl (obviously you can give the file any name you want, for security purposes it may even be beneficial to name it something less obvious – just make sure you use the extension .pl and don’t forget what it’s called!)
  2. Change the following 4 parameters:
    • ## Set your e-mail address
      my $email = ‘youremail@youremail.com’;
    • ## Set the message of the backup e-mail
      my $subject = ‘Database Backup’;
    • ## Set your cpanel username
      my $user = ‘yourusername’;
    • ## Set your cpanel password
      my $pass = ‘yourpassword’;
  3. upload backup.pl to your cPanel hosting account – again for security don’t upload to your public_html directory as this script contains the username and password for your hosting account, instead put it in your root directory above public_html (for cPanel hosting this is usually ‘home/username’)
  4. Set the permissions of backup.pl to 700
  5. Create a cron job to execute the file as and when you require. To create a cron job:
    1. Under the advanced tab in your cPanel dashboard click on the calendar icon ‘Cron Jobs’
    2. For most users click on the standard interface
    3. In the ‘command to run’ field enter the path to your script – if you’ve followed the instructions above and haven’t renamed the file that should be ‘/home/yourusername/backup.pl’
    4. Configure the job to run as and when you require. For example I set mine up to run every week on a Sunday at 5am in the morning with the following settings:

crontab settings

That’s it! You should now receive a weekly email with a number of .sql attachments (one for each of your databases).

I only use this script for my own blog and personal sites, however there are a couple of additional considerations if you are thinking of using this for a business or client site:

  1. Email security: if your databases contains sensitive information you need to ensure your email is secure and encrypted otherwise you may be compromising the data
  2. Database size: if your database grows beyond 10mb (which is highly likely for medium/large sites) you may have issues receiving attachments of that size depending on your email provider

I’d welcome any comments or suggestions on this and how it might be improved, or any alternative backup solutions.

Tags: , ,

Categorised in:

This post was written by WillyNilly

4 Comments

  • sam says:

    good job. Would you tell me how can I backup “public_html” only?

    please email if you are free.

  • admin says:

    Hi Sam, this script is only intended for backing up your database which will change frequently on your webserver.

    It will not backup any of the files stored in public_html. The core files that make up your website should not change therefore you do not need to make regular backups from your webserver, just ensure you have a local copy and make backups from there.

    If you have user generated content that is being saved into your public_html folder that you need to be backed up I would reccommend using a decent hosting provider that includes backups as part of their service e.g. VidaHost

  • You need to check out my desktop software that automates this whole process and allows downloading website backups (files or databases) from cPanel to your local computer.

    No need for cronjobs, it’s quick and easy.

    It’s called Backup Smart, and runs on Win, Mac, or Linux.

    Aaron

  • Webster says:

    Thank you for this script.

    It is working great.

    I have one question. Can you make the backups in a gzip format and not just plain .sql backups? If yes how can this be accomplished please.

    Thanks for your help in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *