CrunchBang - Code, Design & GNU/Linux

Bash Script: MySQL Backup

I thought that it might be a good idea to start posting a few of my scripts; it'll be handy to have them on my site for future reference. Also, I learn a lot by reading example scripts — I guess others might be able to learn from mine.

I wrote the following Bash script to perform a backup of a remote MySQL database. The script first connects via SSH and performs a MySQL dump, saving the results to file. It then connects via SFTP and downloads the file. Once the file has been downloaded, it restores the database to my local MySQL server.

It is quite a simple Bash script and it should be fairly straightforward to follow.

#!/bin/sh
# Settings
#############################
REMOTEHOST="example.com"
REMOTEBACKUPDIR="backup/sql"
SQLHOST="localhost"
SQLDB="database_name"
SQLUSER="username"
SQLPASS="password"
SQLFILE="database_name.sql"
LOCALBACKUPDIR="backup/sql"
#############################
# Start main
echo "* Connecting via SSH..."
ssh $REMOTEHOST <<**
echo "* Performing SQL dump..."
if [ -d $REMOTEBACKUPDIR ]; then
    cd $REMOTEBACKUPDIR
else
    mkdir $REMOTEBACKUPDIR
    cd $REMOTEBACKUPDIR
fi
mysqldump -h $SQLHOST --user="$SQLUSER" --password="$SQLPASS" $SQLDB > $SQLFILE
echo "* Closing SSH connection..."
exit
**
cd ~
if [ -d $LOCALBACKUPDIR ]; then
    cd $LOCALBACKUPDIR
else
    mkdir $LOCALBACKUPDIR
    cd $LOCALBACKUPDIR
fi
echo "* Connecting via SFTP..."
sftp $REMOTEHOST <<**
cd $REMOTEBACKUPDIR
get $SQLFILE
exit
**
echo "* Restoring SQL dump to local server..."
mysql --user "$SQLUSER" --password="$SQLPASS" $SQLDB < $SQLFILE
echo "* SQL backup complete."
cd ~
exit 0

Notes

1. For automation purposes, this script assumes that SSH and SFTP have been configured for automatic login. See "Creating Private/Public SSH Keys"
2. It also assumes there is a mirrored MySQL server and user account running on the local machine.
3. The script can be automated using Crontab.
4. Lacks any error handling and/or logging!?
5. I've worked with some commercial hosting providers who do not grant table locking privileges to their MySQL users — table locking can be bypassed by adding the "--skip-lock-tables" option to the "mysqldump" command. Use with caution.