MySQL : Backup script in Bash with compression and retention

De Wiki de Romain RUDIGER
Aller à : navigation, rechercher

Introduction

Simple script to backup one or multiple or all databases from one MySQL instance.

Use at your own responsibility, always make a backup, never use on production and a backup is valid if you try to restore the content.

Usage

./bkp_mysql.sh --help
Usage: bkp_mysql.sh [-server=<127.0.0.1*|test.domain>] [-backup_history=<2*|[[:digit:]]>] [-databases=<all*|mysql|"mysql test base2">] -backupdir=</bkp_dir>
   [-server]                    : Adress of the mysql server (127.0.0.1 by default)
   [-backup_history]    : Delete all export_mysql* file(s) older than 1 day in the directory -backupdir.
   [-databases]         : Database(s) to be backed up
   -backupdir           : Backup dir for the dumps (all databases: export_mysql__201409141325_all-databases.sql, by database: export_mysql__201409141325_${BASE}.sql*)
   *                    : Default value
INFO-20140914-1325-supervisor-Duration time: 0m 1s
INFO-20140914-1325-supervisor-Return code: 1
INFO-20140914-1325-supervisor-Log file: ./bkp_mysql.sh-20140914-1325.log

Script

Script name: bkp_mysql.sh

#!/bin/bash
# -*- coding: UTF8 -*-

# Script to backup a mysql database.

####
# FUNCTIONS

# Display running time and quit the script
#
# Arg : <exit code>
#
# Global vars : ${Start_Time} ${Log}
#
# Exemple : exit_script 1
function exit_script
{
   if [[ ! -z $Start_Time ]]; then
      Duration_Time=$(expr $(date +%s) - $Start_Time)
      Duration_Time_Mn=$(expr $Duration_Time / 60)
      Duration_Time_Sec=$(expr $Duration_Time - \( $Duration_Time_Mn \* 60 \) )
      if [[ -z ${Log} ]]; then
         info_nolog "Duration time: ${Duration_Time_Mn}m ${Duration_Time_Sec}s"
         info_nolog "Return code: $1"
      else
         info "Duration time: ${Duration_Time_Mn}m ${Duration_Time_Sec}s"
         info "Return code: $1"
         info "Log file: ${Log}"
      fi 
   fi 
   exit $1
}  

# Catch kill of the script
function handle_trap
{
   if [[ -z ${Log} ]]; then
      error_nolog "This script have been killed by an user ($1)!"
   else
      error "This script have been killed by an user ($1)!"
   fi 
   exit_script 1
}  
trap "handle_trap SIGTERM" SIGINT SIGTERM

# Usage
function usage {
        echo "Usage: ${Program} [-server=<127.0.0.1*|test.domain>] [-backup_history=<2*|[[:digit:]]>] [-databases=<all*|mysql|\"mysql test base2\">] -backupdir=</bkp_dir>"
        echo "   [-server]                      : Adress of the mysql server (127.0.0.1 by default)"
        echo "   [-backup_history]      : Delete all export_mysql* file(s) older than 1 day in the directory -backupdir."
        echo "   [-databases]           : Database(s) to be backed up"
        echo "   -backupdir             : Backup dir for the dumps (all databases: export_mysql_${host_exec}_${date_exec}_all-databases.sql, by database: export_mysql_${host_exec}_${date_exec}_\${BASE}.sql*)"
        echo "   *                      : Default value"
        exit_script 1
}

# Get date
#
function get_date
{
   echo $(date +%Y%m%d-%H%M)
}

# Display and write in log an ERROR message
#
# Arg : <message description>
#
# Exemple : error "Can't create the file."
function error
{
   echo -e "ERROR-$(get_date)-$(hostname)-$1"
   echo -e "ERROR-$(get_date)-$(hostname)-$1" >> ${Log}
}

# Display and write in log an INFO message
#
# Arg : <message description>
#
# Exemple : info "Start of the script..."
function info
{
   echo -e "INFO-$(get_date)-$(hostname)-$1"
   echo -e "INFO-$(get_date)-$(hostname)-$1" >> ${Log}
}

# Display and write in log a WARNING message
#
# Arg : <message description>
#
# Exemple : warning "Be aware, the dump may be incomplete!"
function warning
{
   echo -e "WARNING-$(get_date)-$(hostname)-$1"
   echo -e "WARNING-$(get_date)-$(hostname)-$1" >> ${Log}
}

# Check the last return code, display a message and do somethnig
#
# Arg : <message description> [return code (0)*] [action if the return code is different (exit_script 1)*]
# *=default value
#
# Exemple : check_cmd "Error, the last command failed." 0 "exit_script 5"
function check_cmd   
{
   return_code=$?
   [[ -z $2 ]] && code_retour=0 || code_retour=$2
   [[ -z $3 ]] && action="exit_script 1" || action="$3"
   [[ ${return_code} -ne ${code_retour} ]] && { error "$1"; ${action}; }
}

# Check a regular expression
#
# Args : <value to test> <regexp>
#
# Exemple : test_regExp $VAR "^([[:digit:]]{1,3}\.){3}[[:digit:]]{1,3}$"
#  check if $VAR is an IPv4 address, return 0 if ok.
function test_regExp 
{
        Dest_OS="$(uname)"
        if [[ "${Dest_OS}" == "Linux" ]]; then
                grep_cmd="/bin/grep"
        else if [[ "${Dest_OS}" == "SunOS" ]]; then
                grep_cmd="/usr/xpg4/bin/grep"
        else
                grep_cmd="grep"
        fi fi
        echo "$1" | ${grep_cmd} -Eq "$2" > /dev/null
        return $?
}

####
# Main code

# Fixed options
MYSQLDUMP_BIN=/usr/bin/mysqldump
MYSQL_BIN=/usr/bin/mysql
MYSQL_USER=root
MYSQL_USER_PWD="blablabla"
MYSQL_DUMP_OPTIONS="--add-drop-table --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset --single-transaction"


# default options value
Start_Date=$(get_date)
Start_Time=$(date +%s)
server=localhost
databases=all
backup_history=1; # 1 days by default
date_exec=$(date "+%Y%m%d%H%M")
Program=$(basename $0)
Program_dir=$(dirname $0)
Log=$(dirname $0)/$(basename $0)-${Start_Date}.log

# read the arguments 
while [[ -n $1 ]]; do
        arg=$(echo "$1" | sed -r "s/=.*$//")
        argVal=$(echo "$1" | sed -r "s/^\-[a-z_-]+=//")
        if [[ "$arg" == "-help" ]]; then
                usage
        fi
        if [[ "$arg" == "--help" ]]; then
                usage
        fi
        if [[ $(test_regExp "$arg" "^-server$" && test_regExp "$argVal" "^.+$" ; echo $?) -eq 0 ]]; then
                server=$argVal
                shift && continue
        fi
        if [[ $(test_regExp "$arg" "^-backup_history$" && test_regExp "$argVal" "^[[:digit:]]+$" ; echo $?) -eq 0 ]]; then
                backup_history=$argVal
                shift && continue
        fi
        if [[ $(test_regExp "$arg" "^-databases$" && test_regExp "$argVal" "^.+$" ; echo $?) -eq 0 ]]; then
                databases=$argVal
                shift && continue
        fi
        if [[ $(test_regExp "$arg" "^-backupdir$" && test_regExp "$argVal" "^.+$"; echo $?) -eq 0 ]]; then
                backupdir=$argVal
                shift && continue
        fi
        error "Unknow or invalid argumenent: $arg"
        usage
done

[[ "$server" == "localhost" ]] && host_exec=$(hostname --long | sed "s/\./-/g") || host_exec=${server}
[[ -z $backupdir ]] && error "backupdir option is required" && usage

[ -f $MYSQLDUMP_BIN -a -f $MYSQL_BIN -a -d ${backupdir} ]
check_cmd "$MYSQLDUMP_BIN or $MYSQL_BIN or ${backupdir} doesn't exist."

if [[ "${databases}" == "all" ]]; then
        # export all databases
        info " Export all databases..."
        nice -n 19 $MYSQLDUMP_BIN --user ${MYSQL_USER} -p${MYSQL_USER_PWD} -h${server} $MYSQL_DUMP_OPTIONS --all-databases --log-error=$Log | bzip2 > ${backupdir}/export_mysql_${host_exec}_${date_exec}_all-databases.sql.bz2
        cmd_state=${PIPESTATUS[@]}
        test_regExp "$cmd_state" "^0\ 0$"
        check_cmd "Export of all databases failed."
fi
# export by base
[[ "${databases}" == "all" ]] && databases=$($MYSQL_BIN -u${MYSQL_USER} -p${MYSQL_USER_PWD} -h${server} --batch -N -e "show databases;" | grep -v "information_schema")
for i in ${databases}; do
        info " Export database ${i}..."
        nice -n 19 $MYSQLDUMP_BIN -u${MYSQL_USER} -p${MYSQL_USER_PWD} -h${server} $MYSQL_DUMP_OPTIONS --databases ${i} --log-error=$Log | bzip2 > ${backupdir}/export_mysql_${host_exec}_${date_exec}_${i}.sql.bz2
        cmd_state=${PIPESTATUS[@]}
        test_regExp "$cmd_state" "^0\ 0$"
        check_cmd "Export database ${i} failed."
done
# nettoyage des anciens backups
find ${backupdir} -type f -name "export_mysql*" -mtime +$backup_history -print -exec rm -f {} \; 2>&1 | tee -a $Log
# nettoyage des anciens log
find $(dirname $0) -type f -name "$(basename $0)-*.log" -mtime +$backup_history -print -exec rm -f {} \; 2>&1 | tee -a $Log
# affichage resultat 
info "Generated backups :"
IFS=$(echo -ne "\n") 
for file in $(ls -lh --time-style=long-iso ${backupdir}/export_mysql_${host_exec}_${date_exec}* | awk '/^-........./ {printf " -%-115s date: %s-%s\t size: %s\n", $8, $6, $7, $5}' | sed -re "s/\/.*\///" | sort); do
        info "$file" 
done
unset IFS
exit_script 0

Output

INFO-20140914-1314-supervisor- Export database mysql...
INFO-20140914-1314-supervisor- Export database zabbix...
INFO-20140914-1345-supervisor-Generated backups :
 -export_mysql_supervisor-novalan-priv_201409141314_mysql.sql.bz2                                 date: 2014-09-14-13:14   size: 80K
 -export_mysql_supervisor-novalan-priv_201409141314_zabbix.sql.bz2                                date: 2014-09-14-13:39         size: 598M
INFO-20140914-1345-supervisor-Duration time: 31m 10s
INFO-20140914-1345-supervisor-Return code: 0
INFO-20140914-1345-supervisor-Log file: /root/bkp_mysql.sh-20140914-1314.log

Restore

To restore a database without uncompressing the archive in a file system:

bunzip2 < export_mysql_supervisor-novalan-priv_201412070522_zabbix.sql.bz2 | mysql -uroot -p