QNAP NAS Community Forum

Code: Select all

#!/bin/bash
# mysqlbackup v3.8
#
# Copyright 2009-2018 Kenneth Fribert
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.
#
# Script to make automatic backups of all your mysqldatabases
#
# Thanks to OneCD, adnovea, patbaker82, silas, Don, Micke for inspiration and
#           help, and jm45 and bramschats for testing
# PID file handling functions from "Subjectively" website
#
# There is no longer any user-modifyable section in this program, it's now in
# a seperate config file
# 

# Read config file
config="/etc/config/mysqlbackup.conf"
day_ret=$(/sbin/getcfg mysqlbackup day_retention -f $config)
week_ret=$(/sbin/getcfg mysqlbackup week_retention -f $config)
month_ret=$(/sbin/getcfg mysqlbackup month_retention -f $config)
weekday_rot=$(/sbin/getcfg mysqlbackup day_rotate -f $config)
share=$(/sbin/getcfg mysqlbackup share -f $config)
folder=$(/sbin/getcfg mysqlbackup folder -f $config)
user=$(/sbin/getcfg mysqlbackup user -f $config)
pw=$(/sbin/getcfg mysqlbackup pw -f $config)
level=$(/sbin/getcfg mysqlbackup errorlvl -f $config)
 
# Standard commands used in this script
rm_c="/bin/rm"
tar_c="/bin/tar"
awk_c="/bin/awk"
get_c="/sbin/getcfg"
ec_c="/bin/echo"
log_c="/sbin/write_log"
md_c="/bin/mkdir"
ls_c="/bin/ls"
date_c="/bin/date"

# Internal variable setup
arc=$($date_c +%y%m%d).tar.gz
dest=
cur_month_day=$($date_c +"%d")
cur_week_day=$($date_c +"%u")
mysqld_p=
mysqld_c=
mysqlc_p=
mysqlc_c=
error=
databases=
bkup_p=

# Error and logging functions

function error ()
{ $ec_c -e "MySQL Backup: ERROR: $1" ; if test "$level" -gt 0 ; then $log_c "MySQL Backup: ERROR $1" 1 ; fi ; exit 1 ; }

function warn ()
{ $ec_c -e "MySQL Backup: WARNING: $1" ; if test "$level" -gt 1 ; then $log_c "MySQL Backup: WARNING $1" 2 ; fi ;  }

function info ()
{ $ec_c -e "MySQL Backup: INFO: $1" ; if test "$level" -gt 2 ; then $log_c "MySQL Backup: INFO $1" 4 ; fi ; } 

# Functions for handling PID file

function pidfilename() {
  myfile=$(basename "$0" .sh)
  whoiam=$(whoami)
  mypidfile=/tmp/$myfile.pid
  [[ "$whoiam" == 'root' ]] && mypidfile=/var/run/$myfile.pid
  echo $mypidfile
}

function cleanup () {
  trap - INT TERM EXIT
  [[ -f "$mypidfile" ]] && rm "$mypidfile"
  exit
}

function isrunning() {
  pidfile="$1"
  [[ ! -f "$pidfile" ]] && return 1
  procpid=$(<"$pidfile")
  [[ -z "$procpid" ]] && return 1
  [[ ! $(ps -p $procpid | grep $(basename $0)) == "" ]] && value=0 || value=1
  return $value
}

function createpidfile() {
  mypid=$1
  pidfile=$2
  $(exec 2>&-; set -o noclobber; echo "$mypid" > "$pidfile") 
  [[ ! -f "$pidfile" ]] && exit #Lock file creation failed
  procpid=$(<"$pidfile")
  [[ $mypid -ne $procpid ]] && {
    isrunning "$pidfile" || {
      rm "$pidfile"
      $0 $@ &
    }
    {
    echo "MysqlBackup is already running, exiting"
    exit
    }
  }
}

# Start script
mypidfile=$(pidfilename)
createpidfile $$ "$mypidfile"
trap 'cleanup' INT TERM EXIT

# Checking if prerequisites are met
if [[ -z "$level" ]] ; then level="0" ; warnlater="Errorlevel not set in config, setting to 0 (nothing)" ; fi
$ec_c -e "\n"
info "MySQL Backup STARTED"

# Checking variables from config file
if [[ -n "$warnlater" ]] ; then warn "$warnlater" ; fi 
if [[ -z "$day_ret" ]] ; then day_ret="6" ; warn "days to keep backup not set in config, setting to 6" ; fi
if [[ -z "$week_ret" ]] ; then week_ret="5" ; warn "weeks to keep backup not set in config, setting to 5" ; fi
if [[ -z "$month_ret" ]] ; then month_ret="3" ; warn "months to keep backup not set in config, setting to 3" ; fi
if [[ -z "$weekday_rot" ]] ; then weekday_rot="0" ; warn "weekly rotate day not set in config, setting to sunday" ; fi
if [[ -z "$share" ]] ; then share="Backup" ; warn "share for storing Backup not set in config, setting to Backup" ; fi
if [[ -z "$user" ]] ; then user="User" ; warn "MySQL user for backup not set in config, setting to User" ; fi
if [[ -z "$pw" ]] ; then pw="Password" ; warn "MySQL password for backup not set in config, setting to Password" ; fi

# Check for backup share
bkup_p=$($get_c "$share" path -f /etc/config/smb.conf)
if [ $? != 0 ] ; then error "the share $share is not found, remember that the destination has to be a share" ; else info "Backup share found" ; fi

# Add subfolder to backup share
if [[ -z "$folder" ]] ; then
   info "No subfolder given";
   else
   {
   info "subfolder given in config";
   bkup_p="$bkup_p"/"$folder";
   # Check for subfolder under share
   $md_c -p "$bkup_p" ; if [ $? != 0 ] ; then error "the backup folder ($folder) under the share could not be created on the share $share" ; fi
   }
fi

# Check for backup folder on backup share
if ! [ -d "$bkup_p/mysql" ] ; then info "mysql folder missing under $bkup_p, it has been created" ; $md_c "$bkup_p/mysql" ; if [ $? != 0 ] ; then error "the folder mysql could not be created on the share $share" ; fi ; fi

# Check for day retention folder on backup share
if ! [ -d "$bkup_p/mysql.daily" ] ; then info "mysql.daily folder missing under the share $bkup_p, it has been created" ; $md_c "$bkup_p/mysql.daily" ; if [ $? != 0 ] ; then error "the folder mysql.daily could not be created on the share $share" ; fi ; fi

# Check for week retention folder on backup share
if ! [ -d "$bkup_p/mysql.weekly" ] ; then info "mysql.weekly folder missing under the share $bkup_p, it has been created" ; $md_c "$bkup_p/mysql.weekly" ; if [ $? != 0 ] ; then error "the folder mysql.weekly could not be created on the share $share" ; fi ; fi

# Check for month retention folder on backup share
if ! [ -d "$bkup_p/mysql.monthly" ] ; then info "mysql.monthly folder missing under the share $bkup_p, it has been created" ; $md_c "$bkup_p/mysql.monthly" ; if [ $? != 0 ] ; then error "the folder mysql.monthly could not be created on the share $share" ; fi ; fi

# Check for mysqldump command
for mysqld_p in /opt/QMariaDB /mnt/ext/opt/mysql /usr/local/mysql /mnt/HDA_ROOT/mysql /mnt/HDB_ROOT/mysql /mnt/HDC_ROOT/mysql /mnt/HDD_ROOT/mysql /mnt/HDE_ROOT/mysql /mnt/HDF_ROOT/mysql /mnt/HDG_ROOT/mysql /mnt/HDH_ROOT/mysql /share/MD0_DATA/.qpkg/Optware; do
  [ -f $mysqld_p/bin/mysqldump ] && mysqld_c="$mysqld_p/bin/mysqldump"
done
if [ -z $mysqld_c ] ; then error "mysqldump command not found."; else info "mysqldump command found" ; fi

# Check if mysqldump supports routines switch
if [[ "$($mysqld_c --routines)" = *"unknown"* ]]
 then
   mysqld_c=$mysqld_c" -eqQ --single-transaction"
 else
   mysqld_c=$mysqld_c" -eqQR --single-transaction"
fi

# Check for mysql command
for mysqlc_p in /opt/QMariaDB /mnt/ext/opt/mysql /usr/local/mysql /mnt/HDA_ROOT/mysql /mnt/HDB_ROOT/mysql /mnt/HDC_ROOT/mysql /mnt/HDD_ROOT/mysql /mnt/HDE_ROOT/mysql /mnt/HDF_ROOT/mysql /mnt/HDG_ROOT/mysql /mnt/HDH_ROOT/mysql /share/MD0_DATA/.qpkg/Optware; do
  [ -f $mysqlc_p/bin/mysql ] && mysqlc_c="$mysqlc_p/bin/mysql"
done
if [ -z $mysqlc_c ] ; then error "mysql command not found.";  else info "mysql command found" ; fi

# Listing all the databases individually, and dumping them
databases=$($mysqlc_c -u $user -p$pw -h127.0.0.1 -P3308 -Bse 'show databases' | sed "/\b\(information_schema\|performance_schema\)\b/d")
if [ $? != 0 ] ; then error "cannot list databases, is password correct?" ; fi

# Delete old daily backups
info "Cleaning out old backups. Keeping the last $day_ret daily backups"
full="$bkup_p/mysql.daily"
for target in $(ls -t "$full" | tail -n +$(($day_ret + 1 ))) ; do rm -f "$full/$target"; done
if [ $? != 0 ] ; then error "ereasing old daily backups" ; fi

# Delete old weekly backups
info "Cleaning out old backups. Keeping the last $week_ret week backups"
full="$bkup_p/mysql.weekly"
for target in $(ls -t "$full" | tail -n +$(($week_ret + 1 ))) ; do rm -f "$full/$target"; done
if [ $? != 0 ] ; then error "ereasing old weekly backups" ; fi

# Delete old monthly backups
info "Cleaning out old backups. Keeping the last $month_ret montly backups"
full="$bkup_p/mysql.monthly"
for target in $(ls -t "$full" | tail -n +$(($month_ret + 1 ))) ; do rm -f "$full/$target"; done
if [ $? != 0 ] ; then error "ereasing old montly backups" ; fi

info "Backing up current databases to $bkup_p/mysql"
while read line
do
  set $line
  $ec_c -e "Backing up database $line"
  $mysqld_c -h127.0.0.1 -P3308 -u $user -B $line --password=$pw > "$bkup_p/mysql/$line.sql"
  if [ $? != 0 ]; then error "creating new backup when trying to access the database $line" ; error=error ; fi
done<<<"$databases"

if [[ -z $error ]] ; then info "Backup Successfull" ; else error "Backup encountered errors, please investigate" ; fi
 
# Compress backup to an seleced archive

# On first month day do
if [ $cur_month_day == 01 ] ; then
  {
  dest=mysql.monthly;
  info "Creating a monthly archive";
  }
else
  # On selected weekday do
  if [ $cur_week_day == $weekday_rot ] ; then
    {
    dest=mysql.weekly;
    info "Creating a weekly archive";
    }
  else
    # On any regular day do
    {
    dest=mysql.daily;
    info "Creating a daily archive";
    }
  fi
fi

info "Compressing backup to $bkup_p/$dest/$arc"
cd "$bkup_p/mysql/"
sleep 5 
$tar_c 2> /dev/null -czvf "$bkup_p/$dest/$arc" * --remove-files &>/dev/null
if [ $? != 0 ] ; then error "compressing backup" ; else info "Done compressing backup" ; fi

info "Cleaning up after archiving"
$rm_c -f "$bkup_p/mysql/*"

info "MySQL Backup COMPLETED"

Code: Select all

#!/bin/bash