In the previous post, I provide a backup script for PostgreSQL. As promised, here is the same script written for MySQL.

Below are the setup instructions.

1. Copy the contents below into a file named mysql-backup.sh placed in the root account home (on Ubuntu it’s ‘/root’). For simplicity, I’m using the root account, but on your production servers, you may want to create a dedicated “backupuser” account that executes these actions.
   
   
   [cci lang="bash"]

   #!/bin/sh

   DATE=`date “+%Y-%m-%d-%I-%M-%S”`
   BACKUP_DIR=”mysqlbackups”

   #DO NOT CHANGE ANYTHING BELOW THIS LINE
   DUMP=”mysqldump”
   REMOTE=”"

   usage() {
   echo Usage:
   echo “\t$0 [options]”
   echo
   echo “Where options is as follows:”
   echo “\t-r\t–remote\t\tThe remote host to copy the DB backup to (mandatory)”
   echo “\t-u\t–user\t\t\tDatabase user name; defaults to the current linux user”
   echo “\t-d\t–database\t\tDatabase name; defaults to the database user name”
   echo “\t-p\t–password\t\tDatabase password; if none is specified you will be prompted to enter one”
   echo “\t-R\t–remote–destination\tThe directory on the remote host to upload the DB backup to; defaults to ~/$BACKUP_DIR”
   echo “\t-L\t–local–destination\tThe directory on local host to save the DB backup; defaults to $HOME/$BACKUP_DIR”
   }

   while [ "$1" != "" ]; do
   case $1 in
   -h | –help ) usage
   exit
   ;;
   -u | –user ) shift
   USER=$1
   ;;
   -d | –database ) shift
   DB=$1
   ;;
   -p | –password ) shift
   PASS=$1
   ;;
   -r | –remote ) shift
   REMOTE=$1
   ;;
   -R | –remote-destination ) shift
   REMOTE_DIR=$1
   ;;
   -L | –local-destination ) shift
   LOCAL_DIR=$1
   ;;
   esac
   shift
   done
   if [ "$REMOTE" = "" ]; then
   usage
   exit
   fi

   LOCAL_DIR=${LOCAL_DIR:=”$HOME/$BACKUP_DIR”}
   REMOTE_DIR=${REMOTE_DIR:=”~/$BACKUP_DIR”}
   USER=${USER:=`whoami`}
   DB=${DB:=$USER}
   DEST=”$LOCAL_DIR/$DB.$DATE.bz2″

   mkdir -p $LOCAL_DIR 2>/dev/null
   if [ "$PASS" ]; then
   `$DUMP -u$USER -p$PASS $DB | bzip2 > $DEST`
   else
   `$DUMP -u $USER -p $DB | bzip2 > $DEST`
   fi
   scp $DEST $REMOTE:$REMOTE_DIR

   [/cci]

   
   

2. Make the file executable:
   
   [cci lang="bash"]
   chmod 755 mysql-backup.sh
   [/cci]
   
   
3. If you don’t have one already, create an ssh key pair for the account that will be running the cron (‘root’ in mycase). I set the key type to RSA / 2048 bits. When prompted for a password by the keygen creator, leave it blank.
   
   [cci lang="bash"]
   ssh-keygen -t rsa -b 2048
   [/cci]
   
   
4. Now take the id_rsa.pub key that was created in the previous step ( by default, it is placed in ~/.ssh/ ) and put it on the remote machine that we want to copy the backups to. It will need to be placed in a specific file called “authorized_keys” in the ‘.ssh’ directory of the user you will be ssh-ing in as. Thus, if you are ssh-ing into the remote machine as “jsmith”, put the key in a file called “authorized_keys” in /home/jsmith/.ssh/. The key should be placed into ‘authorized_keys’ as one line. Make sure to also enable passwordless ssh on the remote box, else the script won’t automatically be able to scp the file over.
   
   You can test this users ability to ssh into the remote machine without being prompted by a password using simple ssh test:
   
   [cci lang="bash"]
   ssh jsmith@[remoteserver.com]
   [/cci]
   
   Only continue to the next step when you get password-less ssh working!
   
   
5. Next, create a cronjob to execute the script periodically. As root, you can use the “crontab -e” command to edit/create a crontab account. I’ve chosen every hour, on the 15th minute:
   
   [cci lang="bash"]
   15 */1 * * * sh /root/mysql-backup.sh -r [username]@[offsite.com] -u [dbusr] -p [dbpass] -d [dbname] -R [remote server path]
   [/cci]
   
   
6. You may want to confirm that your server time is correct, as the backup file names will be date stamped with their time of creation. Also confirm that the backup files made it into the local and remote machine directories. And ofcourse, test the integrity of the created backup files by untaring/ un-bzipping them and restoreing them in a scratch database.