Hello all,
thanks for the script, which gave me a great quickstart:
Since I have multiple instances of MariDB on my QNAP (QTS 5.1.
I have pimped it a little bit.
I needed different setups for the instances which differ in port numbers used.
So I have added a -c <configfile> flag to use different config files. If this is omitted, the file
mysqlbackup.conf is used (in the same directory as the script resides)
Also I have added a
port parameter to the configfile to handle the different instances.
with parameter
databases you can specify the databases to be backed up. If this parameter is empty, all databases will be selected.
Maybe this is helpful for anybody, so I post the updated script here.
Configfile:
Code: Select all
[mysqlbackup]
day_retention=6
week_retention=5
month_retention=3
day_rotate=1
share=Backup
user=BackupUser
pw=BackupUserPW
port=3307
errorlvl=2
folder=Database/MariaDB_10
databases=Database1 Database 2
Script:
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
while getopts c: flag
do
case "${flag}" in
c) config=${OPTARG};;
esac
done
if [[ -z "$config" ]] ; then config="./mysqlbackup.conf" ; fi
echo
echo
if [ ! -e $config ]
then
echo "ERROR : Configfile $config not found !"
exit
fi
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)
user=$(/sbin/getcfg mysqlbackup user -f $config)
pw=$(/sbin/getcfg mysqlbackup pw -f $config)
port=$(/sbin/getcfg mysqlbackup port -f $config)
level=$(/sbin/getcfg mysqlbackup errorlvl -f $config)
folder=$(/sbin/getcfg mysqlbackup folder -f $config)
databases=$(/sbin/getcfg mysqlbackup databases -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=
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 using configfile $config"
# 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
if [[ -z "$databases" ]] ; then info "No databeses specified -> Backup all databases." ; 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 /share/CACHEDEV3_DATA/.qpkg/MariaDB10 /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 /share/CACHEDEV3_DATA/.qpkg/MariaDB10 /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 in $mysqlc_c" ; 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
# Get all the databases if not specified
if [[ -z "$databases" ]] ; then databases=$($mysqlc_c -h127.0.0.1 -P$port -u $user -p$pw -Bse 'show databases' | sed "/\b\(information_schema\|performance_schema\)\b/d"); fi
if [ $? != 0 ] ; then error "cannot list databases, is password correct?" ; fi
info "Selected databases: $databases"
info "Backing up databases to $bkup_p/mysql"
while read line
do
set $line
$ec_c -e "Backing up database $line"
$mysqld_c -h127.0.0.1 -P$port -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"
Regards Andreas