HOW-TO: MYSQL database remote backup script

Thanks to amazing Linux server admin Alex, this site is extremely well backed up. Every hour we make a complete copy of the local harddrive, and then incrementally sync any new data to an offsite storage location.

If all this fails, we want to preserve the most important part of the site – the databases that hold three years of posts, wikis, and forum discussions. During the recent server maintenance we spun a new shell script to dump the databases, zip them up, and FTP a copy to a secondary offsite server.  It’s way more flexible than the dump script we used previously, and maybe someone out there will find it useful.

Script and walk-through follow below the fold. There is also a wiki version of this post if you’d like to contribute, or see the latest version.

This is a simple command line script for a Linux server that:

1. Dumps a list of databases to .SQL files
2. Zips the .SQL files
3. FTPs the .ZIP backups to a remote server
4. Deletes the .SQL backup files, the .ZIP files accumulate forever

There is no garbage collection or backup rotation. .ZIPed backups will accumulate on the local server and remote FTP server until you manually clear this. This might be updated in the future.

The script goodness

 #!/bin/bash

## list of database table to backup
 declare -a arr=(forum mediawiki track wordpress zencart)

## MYSQL backup user login and password
 ## should have SELECT and LOCK privilages on tables to backup
 user='backup_guy'
 pass=''

## Remote FTP backup location details
 ftpurl='ftp.remoteserver.com'
 ftpfolder='/' #if user's home directory be sure to keep the single /
 ftpuser='ftp_login'
 ftppass=''

## Home directory of the script, used to store ZIPs and delete the daily SQL dumps
 shdir='/home/dp/' #replace with your home directory

##Date and hostname strings
 day=$(date | awk '{ print $2"-"$3"-"$6}')
 hostname=$(hostname -s)

## now loop through the above array
 for i in ${arr[@]}
 do
 echo $i # current DB
 mysqldump -u $user --password=$pass $i > "$i.sql" #dump db to file
 wait
 zip back_$i-$day-$hostname.zip "$i.sql" #zip db to dated file
 wait
 ncftpput -u $ftpuser -p $ftppass $ftpurl $ftpfolder back_$i-$day-$hostname.zip
 wait
 done

rm -rf $shdir*.sql #clear out the SQL files, update with our 

Basic use

You’ll need some basic login and directory info to configure the script.

MYSQL table names

 ## list of database table to backup
 declare -a arr=(forum mediawiki track wordpress zencart) 

These are the databases you’ll back up. Enter with a space between each.

MYSQL backup user login

 ## MYSQL backup user login and password
 ## should have SELECT and LOCK privilages on tables to backup
 user='backup_guy'
 pass='' 

Enter the user name and password of the MYSQL backup user. This user should have SELECT and LOCK permissions on all the tables to backup. We use a dedicated user with only these permissions for security.

Remote FTP backup location info

 ## Remote FTP backup location details
 ftpurl='ftp.remoteserver.com'
 ftpfolder='/' #if user's home directory be sure to keep the single /
 ftpuser='ftp_login'
 ftppass='' 

Address and FTP login details for the server where the backups will be sent via FTP.

Home directory

 ## Home directory of the script, used to store ZIPs and delete the daily SQL dumps
 shdir='/home/dp/' #replace with your home directory 

Set the home directory of the script. This location is used to store ZIPs and delete the daily SQL dumps.

This should be the SAME directory the .sh script is run from!!! This could probably be taken from an environmental variable or something, but this works ok.

Test the script

1. Get the script onto your server, set your database list, MYSQL backup user, remote FTP login, and local shell location (for deleting the daily .SQL dumps)

2. Set execute permissions:

• chmod u+rx db.sh (for user only) -or-
• chmod +rx db.sh (all users, less safe)

3. Test from the command line

• ./db.sh

4. Add to your cron tab and schedule to run daily