In an earlier post (Scripting Backups of MySQL with Perl via mysqldump), I showed you how to use Perl to script a mysqldump backup of selected databases. Reminder – using mysqldump to backup your databases is not as safe or effective as using MySQL Enterprise Backup.

A blog reader sent me an email asking me how they could automate sending the backup files via FTP to another server (preferably one that is off-site). This was easily accomplished by adding a series of FTP commands to the original script.

As in the first post, we use a text file that contains a list of the databases that we want to backup. See this post for more information on using the script to backup your files – I don’t want to duplicate the entire post here.

For the FTP part of this script, you will need to edit the script to make sure that you change the following to match your systems:

# this is your MySQL user name and password
$mysql_user_name = "root";
$mysql_user_password = "my_password";

# this is your FTP site name, user name and password
$ftp_site = "my_hostname.com";
$ftp_user_name = "my_user_name";
$ftp_password = "my_password";
# create this folder on your FTP backup site under your root/login directory
$ftp_backup_directory = "backups";

# set the local directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cgi/mysql/mysqlbak';

The script will create a folder on the local server under the $backup_folder directory, with the naming convention of YYYY-MM-DD-HHMM, and place the backup files for each database in this directory. It will then create the same folder on your FTP server under the $ftp_backup_directory, backup each database, and then send all of those files to your FTP server to be placed in the same directory. The script does a backup for each database individually, compresses the file, and then sends each file via FTP before it backs up the next database.

For example, on your local server, it will create a directory such as “2011-09-07-1756″ under the $backup_folder directory (in this example – /Users/tonydarnell/cgi/mysql/mysqlbak). The script will also create the same directory “2011-09-07-1756″ on the FTP server under the $ftp_backup_directory named “backups”. This $ftp_backup_directory should be created under the root directory (or the default login directory) for your FTP user prior to running the script.

Of course, it will be up to you to remove the older backups from the local server and FTP server. I also have quite a few print statements showing you each step of the process. Feel free to comment out these lines if you don’t want the script to print anything.

Here is a sample output from the script, backing up one database named cbgc:

# /usr/bin/perl mysql_backup_ftp.pl
Backing up cbgc...
FTP - 2011-09-07-2008_cbgc.sql.Z... Goodbye.
FTP Server ready.
User u36379513-777 logged in
CWD command successful
Type set to I
Opening BINARY mode data connection for 2011-09-07-2008_cbgc.sql.Z
Transfer complete
Finished backing up cbgc...


Here is the script:


#!/usr/bin/perl

use Net::FTP;
use warnings;
use File::Basename;

# this is your MySQL user name and password
$mysql_user_name = "root";
$mysql_user_password = "my_password";

# this is your FTP site name, user name and password
$ftp_site = "myhostname.com";
$ftp_user_name = "my_user_name";
$ftp_password = "my_password";
# create this folder on your FTP backup site
$ftp_backup_directory = "backups";

# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cgi/mysql/mysqlbak';

# we use a config file to keep a list of all of the databases that we want to backup
# You may use a comment to bypass any database that you don't want to backup
# example config file
# # unwanted_database (commented - will not be backed up)
# database_name1
# database_name2

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);
$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min < 10;

# create the name of the backup folder that will contain all of the backup files
my $folder = "$year-$mon-$mday-$hour$min";
mkdir($folder) or die("Cannot create a folder called '$folder'");

    # login to the FTP server and create the folder to store the backup files
    $ftp = Net::FTP->new("$ftp_site", Debug => 0)
    or die "Cannot connect to $ftp_site: $@\. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->login("$ftp_user_name","$ftp_password")
    or die "Cannot login with $user_name. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->cwd("/$ftp_backup_directory")
    or die "Cannot change working directory to $ftp_backup_directory. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->mkdir("$folder")
    or die "Cannot create directory $folder. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->quit;
    # end FTP

# backup each database contained in the @databases array
foreach my $database (@databases) {
    next if ($database eq '');
    chomp($database);

    my $table = '';
    # Get just 1 table in the database - if there is a ' '(space) in the db name
    if(index($database,' ')+1) {
     my @parts = split(' ',$database);
     $database = $parts[0];
     $table = $parts[1];
    }

    print "Backing up $database...\n";

    # we add the year, month, day and time of day to the backup filename
    my $file = "$year-$mon-$mday-$hour$min\_$database";
    $file .= '_' . $table if($table ne '');
    $file .= ".sql";

    # perform a mysql dump on each database
    # change the path of mysqldump to match your system's location
    # and add/change any of the backup options - this is just an example
    # make sure that you change the root password above to match the correct password
    `/usr/local/mysql/bin/mysqldump -R --triggers -u $mysql_user_name --password=$mysql_user_password $database $table | compress > $folder/$file.Z`;

    # start FTP
    print "FTP - $file.Z... ";

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp = Net::FTP->new("$ftp_site", Debug => 0)
    or die "Cannot connect to $ftp_site: $@\. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->login("$ftp_user_name","$ftp_password")
    or die "Cannot login with $user_name. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->cwd("/$ftp_backup_directory/$folder")
    or die "Cannot change working directory to $ftp_backup_directory. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->binary()
    or die "Change to binary mode failed. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->put("$folder/$file.Z")
    or die "Put failed - $folder. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->quit;
    # FTP end

    print "Finished backing up $database...\n\n";
}

exit;

# this subroutine simply creates an array of the list of the databases
sub getFileContents {
    my $file = shift;
    open (FILE,$file) || die("Can't open '$file': $!");
    my @lines=<FILE>;
    close(FILE);

    return @lines;
}

# remove any commented tables from the @lines array
sub removeComments {
    my @lines = @_;

    @cleaned = grep(!/^\s*#/, @lines); #Remove Comments
    @cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines

    return @cleaned;
}

—————————————–