- Code: Select all
#!/bin/bash
###############################################################################
#
# Script to make a complete backup of all MySQL/MariaDB databases.
# It rotates directories automatically
#
# Created 2014 by Rene Rasmussen
#
# Loosely based on the backup script from:
# http://blog.sleeplessbeastie.eu/2012/11/22/simple-shell-script-to-backup-mysql-databases/
# and
# https://forums.mageia.org/en/viewtopic.php?f=41&t=5957&hilit=a+lifesaving+backup
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor,
# Boston, MA 02110-1301, USA.
#
###############################################################################
# Backup drive mountpoint
backup_drive="/mnt/backupdrive"
# Parent backup directory
backup_parent_dir="${backup_drive}/mysql"
# Number of backups
# (number of directories in rotation)
# count starts from 0, so below value defines directories+1
backup_dir_count=12
# MySQL user settings
# (use an unprivileged user with only SELECT and LOCK TABLES permission)
mysql_user="sqlunprivuser"
mysql_password="thesecurepassword"
#initialize errors variable
errors=0
# Check if drive is mounted
# (check for mount_OK file)
okFile=${backup_drive}/mount_OK
if [ -f $okFile ]
then
# If the destination folder does not exist, it will be created
if [ ! -d $backup_parent_dir ]
then
mkdir -p $backup_parent_dir
chmod 750 $backup_parent_dir
fi
# Rotate backup directories
for (( c=$backup_dir_count; c>=1; c-- ))
do
if [ $c -eq $backup_dir_count ]
then
dirPath=${backup_parent_dir}/backup$c
#echo "0 = $dirPath" #debug
if [ -d $dirPath ]
then
rm -rf $dirPath
fi
fi
dirPath1=${backup_parent_dir}/backup$((c-1))
dirPath2=${backup_parent_dir}/backup$c
#echo "1 = $dirPath1" #debug
#echo "2 = $dirPath2" #debug
if [ -d $dirPath1 ]
then
mv $dirPath1 $dirPath2
fi
done
# Set error flag if MySQL password is empty
if [ -z "${mysql_password}" ]; then
errors=3
else
# Check MySQL password
echo exit | mysql --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
if [ "$?" -gt 0 ]; then
errors=2
fi
fi
if [ $errors == 0 ]
then
# Create backup directory and set permissions
backup_dir="${backup_parent_dir}/backup0"
mkdir -p "${backup_dir}"
chmod 700 "${backup_dir}"
# Get MySQL databases
mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} --password=${mysql_password} -B | sed /^Database$/d`
# Backup and compress each database
for database in $mysql_databases
do
if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
additional_mysqldump_params="--skip-lock-tables"
else
additional_mysqldump_params=""
fi
mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.gz"
chmod 600 "${backup_dir}/${database}.gz"
done
# create datestamp (empty) file
touch "${backup_dir}/MyBackup from $(date '+%A, %d %B %Y, %T')"
fi
else
errors=1
fi
# Error handling
if [ $errors != 0 ]
then
# Select error message based on errors value
# errors = 1 => Backup drive not mounted
# errors = 2 => MySQL user or password incorrect
# errors = 3 => MySQL password empty
fdesc="ERROR with database backup"
case $errors in
1)
fbody="Backup location not accessible! Please investigate cause!"
;;
2)
fbody="MySQL user ${mysql_user} does not exist, or wrong password!"
;;
3)
fbody="MySQL password is empty!"
;;
*)
fbody="Something went wrong. Please investigate!"
;;
esac
# Error message for screen
echo $fdesc
echo $fbody
## error email for admin
receiver="user@workstation.com"
echo $fbody | mail -s "$fdesc" $receiver
exit
fi
There are probably places in the script that can be optimized. But this was what my brain would extend to this weekend
Pointers on improvements are gladly accepted. And should you need to backup your database, then you are very welcome to use this script and post your experience with it.
I have a similar script for backing up my files with rsync. If anyone is interrested then I'll post it here as well.