Automated MySQL backup for shared webhosting

What to do if you want to use mysqldump in a shared hosting environment without being able to access the shell (SSH…)? Use a Perl CGI script:

#!/usr/bin/perl -w

use strict;
use CGI::Carp qw(fatalsToBrowser);

print "Content-type: text/plain\nContent-disposition: attachment; filename=\"db_backup.sql\"\n\n";
print qx(mysqldump -uUSERNAME -pPASSWORD DATABASE 2>&1);

Replace USERNAME, PASSWORD and DATABASE with the configuration data for your database and drop the script into your cgi-bin directory. Don’t forget to make it executable (chmod +x) and apply some form of access restrictions! Here’s an Apache .htaccess file which serves well for this purpose:

AuthType Basic
AuthUserFile /path/to/.htpasswd
AuthName "SQL Backup"

Order deny,allow
Allow from all
Require valid-user

Create the .htpasswd file with htpasswd -c .htpasswd USERNAME

To automatically fetch a current backup and compress it, run the following shell script on another machine (probably as a cron job):

#!/bin/bash

USER=username
PW=password

IN=http://example.org/cgi-bin/dbdump.pl
OUT=/where/to/store/the/db_backup.sql.bz2

wget -nv --user=$USER --password=$PW -O - $IN | bzip2 > $OUT

Leave a Reply

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