User Tools

Site Tools


scripting:mariadb-backup.sh

mariadb-backup.sh

This script has moved to GitLab and is no longer updated on the wiki.

This script can be used to dump a MariaDB (or MySQL) database to disk and to rotate through a given number of backups. It supports two command line arguments:

  • -q run silently (for use in crontabs or other non-interactive settings)
  • -k N number of recent backups to keep (default is 7)

The script assumes that backup user has a ~/.my.cnf file storing the database connection (or passwordless access to the database as the current userid). It's a good idea to create a MariaDB user that has the appropriate permissions for dumping databases instead of running this script as the unix root user.

#!/bin/bash
#
# mariadb-backup.sh - a simple script to dump and rotate MariaDB backups
#
# Copyright (c) 2015 Gabriel M. O'Brien
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
#
 
backup_root=/srv/backups/mariadb
 
# by default keep 7 backups (can be overriden by command line arg)
keep=7
 
# set verbose to true by default
v=true
 
# get our options
while getopts qk: opt; do
  case $opt in
  q)
      v=false
      ;;
  k)
      keep=$OPTARG
      ;;
  esac
done
shift $((OPTIND - 1))
 
# set a righteous mask
umask 0027
 
# create backup path
stamp=`date +%Y-%m-%d.%H%M%S`
backup_dir=${backup_root}/${stamp}
mkdir -p ${backup_dir}
$v && printf 'Keeping %s backups.\n' $keep
$v && printf 'Backup location: %s\n' $backup_dir
 
# get the database list and remove garbage
db_list=`mysqlshow |
sed -r '/Databases|information_schema|performance_schema/d' |
awk '{ print $2 }'`
 
# run the backup
for db in $db_list; do
   $v && printf 'Backing up "%s" database...' $db
   nice -n 19 mysqldump $db | nice -n 19 gzip > ${backup_dir}/${db}.sql.gz
   $v &&printf ' done.\n'
done
 
# remove old backups
cd $backup_root
 
# create a link to current backup
rm -f latest && ln -s ${stamp} latest
 
# find out how many backups are in this directory
dirnum=`ls | wc -l`
diff=$(expr $dirnum - $keep)
 
# figure out if we need to delete any old backups
if [ "$diff" -gt "0" ]; then
  $v && printf 'Removing %s old backup(s):\n' $diff
  for d in `ls | sort | head -n $diff`; do
    $v && printf '  %s\n' $d
    rm -rf ./${d}
  done
else
  $v && printf 'No old backups to remove (found %s).\n' $dirnum
fi
scripting/mariadb-backup.sh.txt · Last modified: 2019-08-10 17:26 by gabriel