A MySql Backup Script (with Download Speeds & Hipchat Integration)

It’s a common problem we developers come across, we need to keep a backup of our databases so that if anything happens to our live database we still have a copy of the data.

This is a quick script which uses the mysqldump command to grab a copy of a database and save it. It saves the file as a gzip file (because it saves a mega amount of space over time).

More than just backing up the data, this script will also alert a hipchat room so that you know it has run.

Prerequisites:

sudo apt-get install mysql-server gzip pv curl

Script:

#!/bin/bash

# DB Backup Script

# Backup a MySql Database Script using mysqldump, pv, gzip and curl.
# Will dump the entire contents of a database into a gzipped file.

# Options:
# Database credentials
DBUSER=""
DBPASS=""
DBHOST=""
DBNAME=""

# Note: no need for trailing slash
SAVE_PATH="/home/$USER"

# Note: can't have space after the plus
# formats the final file like: 1970-01-01-13:00.sql.gz
date=$(date +"%Y-%m-%d-%H:%M")

# Check MySqlDump is actually installed. (How to: http://bit.ly/1PObIdX)
hash mysqldump 2>/dev/null || { echo >&2 "The 'mysqldump' command is required. Aborting."; exit 1; }

# The Important Step:
# Using the single-transaction flag is faster and doesn't seem to affect live usage too much.
# The dump command gets passed into pv which echos to screen the amount of data flowing 
# through the pipe (useful to know it's working).
echo "-- Downloading... --"
mysqldump \
    --user=$DBUSER \
    --password=$DBPASS \
    --host=$DBHOST \
    --single-transaction \
    --skip-lock-tables \
    $DBNAME | pv | gzip > $SAVE_PATH/$date.sql.gz

# (Optional) Notify HipChat room of completion, Based on: http://bit.ly/1UGfcLB
# Currently prints a random zen message, like "Mind your words, they are important."
ROOM_ID=""
AUTH_TOKEN=""
OWNER_ID=""
MESSAGE=$(curl -s https://api.github.com/zen)
COLOUR=purple

curl --header "content-type: application/json" \
     --header "Authorization: Bearer $AUTH_TOKEN" \
     -X POST \
     -d "{\"name\":\"dev\",\"privacy\":\"private\",\"is_archived\":false,\"is_guest_accessible\":false,\"topic\":\"cURL\",\"message\":\"$MESSAGE\",\"color\":\"$COLOUR\",\"owner\":{\"id\":$OWNER_ID}}" \
     https://api.hipchat.com/v2/room/$ROOM_ID/notification