MySQL Backup 1.1

This is an update to the earlier MySQL backup script, with this version I’ve added the code that (assuming your backup user has sufficient rights) allows the script to get a list of all the databases on the server, exclude the MySQL internal ones and use this as the list of databases to 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 uncomment the stop/start slave lines in place. If you’re running this on a master or single server then you can ignore these.

#!/bin/bash

# Author: Grant MacDonald
# Purpose: MySQL backup script
# Version: 1.1
# Date: 12/08/2015

# Version History
# 1.1 Query the server for the list of databases
# 1.0 Initial version

# Options
# USEROPTIONS If you need to set a username / password do it here.
# BACKUPDIR Location to store backups
# DBLIST Space separated list of database names to backup
# DUMPOPTIONS Any options you wish mysqldump to use, or if you have to specify a host/socket, etc.
# BINDIR Path to mysql executables mysqladmin / mysqldump
# DATE Path to the date command (means you're not reliant on any PATH being set)
# GZIP Path to the gzip command

#It's good practise to create a user with backup rights to use.
#USEROPTIONS="-ubackup -pQwErTy123"
USEROPTIONS=""
BACKUPDIR=/backup
DUMPOPTIONS="--lock-all-tables"
BINDIR=/usr/bin
DATE=/bin/date
GZIP="/bin/gzip -f"

# You can explicity set the list of databases to backup
#DBLIST="your list of databases here"

# Or if your user has the correct rights you can query and get a list of all databases
DBLIST=$(mysql ${USEROPTIONS} -s -e 'SELECT SCHEMA_NAME  FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ("mysql", "information_schema"
, "performance_schema")')

## 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=$(${DATE} +%Y%m%d)

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

# If you're running after midnight then you might want to use the previous day for the backup name.
# For example if you're running at 00:05 on Tuesday morning you might consider this to be "Monday nights" backup
BACKUPDATE=$(${DATE} -d yesterday +%a)

## Optional if you're a replication slave, remember to uncomment the START section too!
## 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

## Optional if you're a replication slave, remember to uncomment the STOP section too!
## Start accepting replication
#echo $(${DATE}) Start replication
#${BINDIR}/mysqladmin ${USEROPTIONS} start-slave

# Compress the backups
echo $(${DATE}) Compress Backup START
${GZIP} ${BACKUPDIR}/*_${BACKUPDATE}.sql
echo $(${DATE}) Compress Backup FINISH

## Optional rsync the files to the remote backup server
## Note:  This is sample code and not parameterised
#echo $(${DATE}) MYSQL RSYNC REMOTE_SERVER START
#${BINDIR}/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