The following Perl program allows you to make a remote back up of the MySQL database used by the well known Open Source bulletin board package phpBB. I have been using this Perl program for several months to back up the data of two phpBB message boards.
Feel free to test the beta-version of this backup program; feed back is welcome.
The current version of the backup program also supports the re-authentication needed for more recent versions of the phpBB package.
#!/usr/bin/perl
#
# pbbackup.pl
#
# © Copyright 2005 By John Bokma, http://johnbokma.com/
#
# $Id$
#
# 2005-12-07 - Now supports re-authentication
use strict;
use warnings;
use Getopt::Long;
use Pod::Usage;
use LWP::UserAgent;
use HTML::TreeBuilder;
sub show_help {
print <<HELP;
Usage: pbbackup [options] [url]
Options:
-l user login with specified admin name
-pw passw login with specified admin password
-f filename of the back up (ignores -d)
-d write backup to the specified directory,
using yyyymmdd-hhmmss.sql.gz as filename
format
-h this message
HELP
exit 1;
}
my $username;
my $password;
my $filename;
my $dir = '.';
my $verbose = 0;
my $help = 0;
GetOptions(
"l=s" => \$username,
"pw=s" => \$password,
"f=s" => \$filename,
"d=s" => \$dir,
'h' => \$help,
) or show_help;
$help and show_help;
my $url = shift;
defined $username or show_help;
defined $password or show_help;
defined $url or show_help;
substr( $url, -1 ) eq '/' or $url .= '/';
my $ua = LWP::UserAgent->new();
# make POST redirectable
push @{ $ua->requests_redirectable }, 'POST';
# login as board administrator
my $login_url = "${url}login.php";
my $response = $ua->post(
$login_url, [
username => $username,
password => $password,
autlogin => 'off',
redirect => '',
login => 'Log in',
]
);
$response->is_success or
die "Login failed: ", $response->status_line, "\n";
# obtain the session id
my $root = HTML::TreeBuilder->
new_from_content( $response->content );
my $sid_link = $root->look_down( _tag => 'a',
href => qr/sid=[a-z0-9]+$/ );
defined $sid_link or
die "No link containing a session id found\n";
my ( $sid ) = $sid_link->attr( 'href' )
=~ /sid=([a-z0-9]+)$/;
defined $sid or
die "No session id found\n";
$root->delete;
# Re-authenticate (required for more recent phpBB versions)
$response = $ua->post(
$login_url
. "?redirect=admin/index.php&admin=1&sid=$sid", [
username => $username,
password => $password,
autlogin => 'off',
redirect => 'admin/index.php?admin=1',
admin => 1,
login => 'Log in',
]
);
$response->is_success or
die "Re-authentication failed: ", $response->status_line, "\n";
# Initiate the back up process
my $db_utilities_url =
"${url}admin/admin_db_utilities.php?sid=$sid";
$response = $ua->post(
$db_utilities_url, [
backup_type => 'full',
additional_tables => '',
gzipcompress => 1,
perform => 'backup',
drop => 1,
backupstart => 'Start Backup',
]
);
$response->is_success or
die "Start Backup failed: ", $response->status_line, "\n";
# Obtain the link for downloading of the back up.
$root = HTML::TreeBuilder->
new_from_content( $response->content );
my $refresh = $root->look_down( _tag => 'meta',
'http-equiv' => 'refresh' );
defined $refresh or
die "No backup download link found\n";
my $content = $refresh->attr( 'content' );
$root->delete;
my ( $delay, $download_url ) = split /;url=/, $content;
sleep $delay;
# Do the actual download
unless ( defined $filename ) {
my @time = localtime;
my $stamp = sprintf "%d%02d%02d-%02d%02d%02d",
$time[ 5 ] + 1900,
$time[ 4 ] + 1,
reverse @time[ 0 .. 3 ];
$filename = "$dir/$stamp.sql.gz";
}
$response = $ua->get(
"${url}admin/$download_url",
':content_file' => $filename
);
$response->is_success or
die "Download failed: ", $response->status_line, "\n";
# Log out
$response = $ua->get(
$login_url,
logout => 'true',
sid => $sid
);
$response->is_success or
die "Log out failed: ", $response->status_line, "\n";Note how the POST method is added to the list of redirectable requests. Without this addition the Perl program reports "Login failed: 302 Found" and aborts.
The Perl program supports options to specify the username (-l) and the password (-pw) of the board administrator. Furthermore you can specify the filename of the downloaded backup (-f) or specify a directory for saving the backup to. In the later case the program automatically names the backup file using the current date and time.
After the options you have to specify the URL (web address) of your board without "index.php", e.g. for http://toxicice.com/index.php the value should be http://toxicice.com/.
Examples of program usage:
pbbackup.pl -l user -pw password -d backup http://example.com/
pbbackup.pl -l user -pw password -f db.sql.gz http://example.com/The first example creates a back up file in the backup directory, with the date and time encoded in the filename. The second example creates a backup file named db.sql.gz.
I use the following batch program to create a backup, decompress it using gzip, and checking it into a Subversion repository. Because pbbackup is in my case a batch program that calls the actual Perl program, call has to be used otherwise the next two lines are not executed.
call pbbackup -l username -pw password -f toxicice.sql.gz http://toxicice.com/
gzip -df toxicice.sql.gz
svn ci -m "Updated via automatic back up" SQL-backup/toxicice.sqlThe -f option of gzip is used to force overwriting of toxicice.sql.gz in case this file already existst, for example because a backup failed for one reason or another.
I use the Scheduled Tasks of Windows XP to call the above batch program daily.