====== mariadb-backup.sh ======

//This script has moved to [[https://gitlab.com/unxis/mariadb-backup.sh|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.

<code bash>
#!/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
</code>