Automatically backup your databases on cPanel using cronjobs

#!/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 = ‘[email protected]’; ## 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 = ‘[email protected]’;
   • ## 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:

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

Categorised in: Webmaster

This post was written by WillyNilly