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