MySQL Backup

This is a simple script that can be used to take backups of your MySQL databases, it can be run as a cron job and outputs enough of a log to allow you to keep track of what’s going on.

There’s a few sections you’ll need to edit and an extra stage that uses rsync to send the backups to a remote server which you may or may not need.  If you’re running this on a MySQL replication slave (recommended) then leave the stop/start slave lines in place.  If you’re running this on a master or single server then you can comment those lines out.

#!/bin/bash

# Author: Grant MacDonald
# Purpose: MySQL backup script
# Version: 1.0
# Date: 14/04/2012

# Version History
# 1.0 Initial version

# Options
# BACKUPDIR Location to store backups
# DBLIST Space separated list of database names to backup
# USEROPTIONS If you need to set a username / password do it here.
# DUMPOPTIONS Any options you wish mysqldump to use
# BINDIR Path to mysql executables mysqladmin / mysqldump

BACKUPDIR=/backup
DBLIST="your list of databases here"

#It's good practise to create a user with backup rights to use.
#USEROPTIONS="-ubackup -pQwErTy123"

USEROPTIONS=""
DUMPOPTIONS="--lock-all-tables"
BINDIR=/usr/bin

## Script Logic Starts Here
echo $(date) MySQL Backup START

# Date to append to database names

# Uncomment the line below to use full dates
# Remember to make the day of week line a comment if you want to use the full dates.
#BACKUPDATE=$(/bin/date +%Y%m%d)

# The versions gives day of the week backups, eg mydatabase.Mon.sql, mydatabase.Tue.sql
BACKUPDATE=$(/bin/date +%a)

# Stop accepting replication while we backup
echo $(date) Stop replication
${BINDIR}/mysqladmin ${USEROPTIONS} stop-slave

# Iterate through the list of databases and take a dump
for DATABASE in ${DBLIST} ; do
echo $(date) Dumping ${DATABASE} START
${BINDIR}/mysqldump ${USEROPTIONS} ${DUMPOPTIONS} ${DATABASE} > ${BACKUPDIR}/${DATABASE}_${BACKUPDATE}.sql
echo $(date) Dumping ${DATABASE} FINISH
done

# Start accepting replication
echo $(date) Start replication
${BINDIR}/mysqladmin ${USEROPTIONS} start-slave

# Compress the backups
echo $(date) Compress Backup START
${BINDIR}/gzip -f ${BACKUPDIR}/*_${BACKUPDATE}.sql
echo $(date) Compress Backup FINISH

echo $(date) MYSQL RSYNC REMOTE_SERVER START
rsync -avz --delete -e "ssh -i /root/.ssh/vps" ${BACKUPDIR}/* remote_server:/data/backup/
echo $(date) MYSQL RSYNC REMOTE_SERVER FINISH

# Thank you and goodnight
echo $(date) MySQL Backup FINISH

# Elvis Has Left The Building

And that’s it.