Backup script for MariaDB

Here wizards, magicians, sorcerers and everybody can rest a bit and talk about anything they like.

Just remember to respect the rules.

Backup script for MariaDB

Postby madeye » Apr 6th, '14, 17:02

I've been working on a script to backup my MariaDB databases. From the tests I have run with it, it seems to be working correctly.
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.
- Madeye

When I supply commands in an answer, please make sure you understand them before you run them! Use google or man to check!
User avatar
madeye
 
Posts: 110
Joined: Jul 23rd, '11, 12:36
Location: Aabenraa, Denmark

Return to The Wizards Lair

Who is online

Users browsing this forum: No registered users and 1 guest

cron