HOWTO - Automating MySQL Database Backup and Retrieval from Hosted Domains

Assumptions - Hosted domain, MySQL database, a secure directory requiring SSL connections.

Tools - mysqldump, cron, bash shell scripts, curl.

On the Remote Server Side

ssh into your remote domain and create a local .my.cnf file. This allows the shell script we are going to write to run the mysqldump command to create the backup dumpfile without having a hardcoded mysql password on the command line. The entries in this file will securely provide the password for connecting to your mysql database. The local configuration file must have the leading dot in the name (.my.cnf), and must be in your home directory on the remote host, not in the webserver document root. The configuration file contents are in this format:

[client]
# The following password will be sent to all standard MySQL clients

password="YourMySQLUserPassword"

Use chmod to change the permissions on .my.cnf to 600 for security.

Now cd into your website's secure directory that you've set to require SSL connections. Create a shell script there to run the mysqldump command to create the dumpfile. We'll use the filename format dbname_dump_cron.sh:

#!/bin/sh
# Run mysqldump to create backup file.
# We'll retrieve using cron from remote workstation.
# Create a filename concatenating the database name, the date
# and file extensions indicating a compressed sql dumpfile.

BFILE_1=~/public_html/secure/dbname_$(date +%Y%m%d).sql.gz

# We'll pipe the output of mysqldump into gzip at its highest
# compression setting (-9) and then out to the filename we created.
# Creating the .my.cnf configuration file means we don't have to
# manually provide the MySQL connection password.

mysqldump -u mysql_user db_name | gzip -9 > $BFILE_1

Use chmod to change the permissions on the dbname_dump_cron.sh file to 770 so it will execute.

Now run the command crontab -e to create the cron job that will execute your shell script. I've set this one to run every night at 2:07am:

# Backup mysql using mysqldump.

07 2 * * * ~/public_html/secure/dbname_dump_cron.sh

On the Local Workstation Side

Create a directory to store your retrieved MySQL dumpfiles and cd into it. Create a shell script that will use curl to retrieve the dumpfile from the webserver's secure directory. We're going to match the filename we used on the remote server for the dumpfile, and we're going to email the status result of the cron job to ourselves. I use mutt and msmtp for my mail use. If you use sendmail, make the changes necessary for that. Note that we are using https for our transport protocol. For the shell script we'll use the filename format get_dump_dbname.sh:

#!/bin/sh
# A cron script to retrieve a mysqldump file
# from a remote host. The dump file is created
# by a cron job running on the remote server.
# The remote cron job runs at 2:07 am. We'll
# run this one an hour later.

# Create the url and filename based upon today's date.

URL=https://myhosteddomain.com/secure/
FILENAME=dbname_$(date +%Y%m%d).sql.gz
FULLPATH=$URL$FILENAME

# Use curl to retrieve the mysqldump file from the remote server.
# Note that the SSL directory username and password are separated
# by a colon. We add the argument --fail so that curl will return
# an error code if it cannot find the file, instead of retrieving
# the webserver's 404 page and assuming all is well.

curl -u ssl_user:ssl_password --fail --silent --output /path/to/backup/$FILENAME --url $FULLPATH

# Note the curl exit code.

RETURN=$?

# Make sure the download went okay.
if [ $RETURN -ne 0 ]
then
        # curl had problems.
        EMAIL_BODY="Could not retrieve $FILENAME Curl exit code was $RETURN"
else
        # all went well.
        EMAIL_BODY="Success at retrieving $FILENAME"
fi

echo $EMAIL_BODY | mutt -s 'Database Backup Status' foo@bar.com

Use chmod to change permissions on the shell script to 770 so it will execute. Then execute the crontab -e command to create the cron job. I set this script to run nightly, an hour after the script on the remote server.

# Automated backup of mysqldump files from remote servers.

07 3 * * * /path/to/backup/get_dump_dbname.sh

I use rsync over ssh every 4 hours to backup all of my workstation data files to another server on my internal network, so I have the database backup files on three hard drives. Once a month I burn them to a DVD and then delete them from the remote server.