[HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post your questions about Web Server usage and Apache + PHP + MySQL/SQLite web applications.
Post Reply
User avatar
moody_blue
Easy as a breeze
Posts: 277
Joined: Tue Jan 10, 2017 9:23 am

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by moody_blue »

trusch wrote: Tue May 04, 2021 3:57 pm I would like to do 2 Backups a day 12.00 and 17.00 i can change the crontab to do so,but he does not keep more than 1 a day he simply overwrites the last one.
what do i have to change in the script? i am not really into scripting yet and if someone has a solution i would be happy.
My suggestion is to use two scripts using two different mysqlbackup.conf files (almost identical, except the folder parameter). One runs at 12:00 and the other runs at 17:00.

You can specify the .conf file name in line 29 of the script.
QNAP TS-253A 8G QTS 5.0.1.2145
Plex Media Server 1.29.0.6209
OpenHAB 3.4.0.M2
Unifi 7.2.92
Apache80 2454.8230
GLPI 10.0.3
trusch
New here
Posts: 3
Joined: Thu Apr 29, 2021 2:13 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by trusch »

moody_blue wrote: Wed May 05, 2021 8:36 am
trusch wrote: Tue May 04, 2021 3:57 pm I would like to do 2 Backups a day 12.00 and 17.00 i can change the crontab to do so,but he does not keep more than 1 a day he simply overwrites the last one.
what do i have to change in the script? i am not really into scripting yet and if someone has a solution i would be happy.
My suggestion is to use two scripts using two different mysqlbackup.conf files (almost identical, except the folder parameter). One runs at 12:00 and the other runs at 17:00.

You can specify the .conf file name in line 29 of the script.
Good idea, i will try that
thank you
trusch
New here
Posts: 3
Joined: Thu Apr 29, 2021 2:13 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by trusch »

ok,
first i renamed mysqlbackup.conf -> mysqlbackup12.conf , copied it and changed the name of the copy to mysqlbackup17.conf
same procedure with mysqlbackup.sh --> mysqlbackup12.sh and mysqlbackup17.sh
then i altered the folder in the conf file to Backup12 / Backup17
finally in mysqlbackup12.sh and mysqlbackup17.sh i put in the corresponding name of the mysqlbackup.conf-File
then i edited crontab, put in 2 new lines


20 10 * * * /share/homes/Backupscript/mysqlbackup12.sh
35 10 * * * /share/homes/Backupscript/mysqlbackup17.sh
This is just for testing purposes, finally the lines will be

0 12 * * * /share/homes/Backupscript/mysqlbackup12.sh
0 17 * * * /share/homes/Backupscript/mysqlbackup17.sh



now i have 2 folders
Backup12 with the 12.00 Backups
and
Backup17 with the 17.00 Backups
.
.
.
WORKS ! yeah thank you

now i have to edit Hybridbackup to save the right folders, then im done

thomas
tuxonroller
New here
Posts: 5
Joined: Thu Mar 14, 2019 5:24 am

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by tuxonroller »

Hi,

I've installed QTS 5 and mariadb10 and changed some things in this (excellent!) script in order to work fine in this new environment:
- add socket parameter : -S /var/run/mariadb10.sock
- add path of mariadb in the searching command section : /share/CACHEDEV1_DATA/.qpkg/MariaDB10
Tested successfully !
@Kenneth Fribert : I let you integrate these changes in the source and change number version if necessary! And thanks for your job!

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 /share/CACHEDEV1_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/CACHEDEV1_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" ; fi

# Listing all the databases individually, and dumping them
databases=$($mysqlc_c -S /var/run/mariadb10.sock -u $user -p$pw -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 -S /var/run/mariadb10.sock -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"
farao
New here
Posts: 5
Joined: Thu Nov 09, 2017 11:49 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by farao »

Hi,

I tried to backup my MariaDB which is use by Home Assistant to store history.
Because I'm not so technical guy, maybe I missed something or that HA database cannot be backed up while is online.
The error message is pretty simple : "mysql backup error creating new backup when trying to access database hass".

Help me understand:
1. if the backup can be done while database is online ;
2. if the access level for other users for this database can stop script to backup database

https://ibb.co/7vwfHtk

Thank you
Aarto
New here
Posts: 5
Joined: Sat Dec 01, 2018 3:30 am

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by Aarto »

Hello all
I ahev QTS 5.0 and MySQL is not installed in this version. Instead, MariaDB is used.
I cannot see any option to turn on network access for the media library database. If i open the MariaDB icon in control panel, it just prompts to install MariaDB as a separate database instead of allowing me to change any options or the existing media library database.

Does anyone know how i could use this backup script for my existing Media library database in QTS 5.0?

I got this reply from QNAP support
"SQL Server is no longer available in QTS 5.0.0, but instead it was replace with MariaDB5.
To use the PHPAdmin, you need to install the MariaDB5 first for you to able create an SQL database.
When installing the MariaDB5, you have to create your database."

Which kind defeats the point as i need to be able to connect phpmyadmin to the media library database, not a new database

With kind regards
Aarto
ApPzLaNd
New here
Posts: 5
Joined: Sun Aug 09, 2009 5:05 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by ApPzLaNd »

Hi,

Big thank your to Kenneth Fribert and tuxonroller for the update.
On my QTS 5.xx I use the Script under "Cronicle" https://www.qnapclub.eu/index.php/en/qpkg/973 because I want to have a Cron UI.

Since it took me a while to get everything working, I would like to write down my hints here, even if they were already in the thread and I should have read them over.
On my QNAP was MariaDB5 and I have then installed the MariaDB10. MariaDB10 runs on a different folder and port what tuxonroller has fixed by his adjustments.

With me the script did not want to run and grumbled:
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50557, now running 100508. Please use mariadb-upgrade to fix this error (1558)

After running on QNAP from:
# /share/CACHEDEV1_DATA/.qpkg/MariaDB10/bin/mariadb-upgrade -S /var/run/mariadb10.sock -u root -p 'PASSWORD'
the script worked fine.
Vasekdvor
New here
Posts: 2
Joined: Tue Mar 28, 2017 7:48 am

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by Vasekdvor »

tuxonroller wrote: Fri Oct 15, 2021 5:16 am Hi,

I've installed QTS 5 and mariadb10 and changed some things in this (excellent!) script in order to work fine in this new environment:
- add socket parameter : -S /var/run/mariadb10.sock
- add path of mariadb in the searching command section : /share/CACHEDEV1_DATA/.qpkg/MariaDB10
Tested successfully !
@Kenneth Fribert : I let you integrate these changes in the source and change number version if necessary! And thanks for your job!

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 /share/CACHEDEV1_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/CACHEDEV1_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" ; fi

# Listing all the databases individually, and dumping them
databases=$($mysqlc_c -S /var/run/mariadb10.sock -u $user -p$pw -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 -S /var/run/mariadb10.sock -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"
Thanks for your correction, i have to modify it for myself because i have mariaDB10 in CACHEDEV3_DATA (/share/CACHEDEV3_DATA/.qpkg/MariaDB10) and it fails with error that "mysqldump command not found."
so i had to change your corrected lines:

Line 167:

FROM

Code: Select all

for mysqld_p in /share/CACHEDEV1_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 
TO

Code: Select all

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
Line 181:

FROM

Code: Select all

for mysqlc_p in /share/CACHEDEV1_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
TO

Code: Select all

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
Simply changed all CACHEDEV1_DATA to CACHEDEV3_DATA

And now it works fine!
Owner of QNAP TS-673A-8G & QNAP TS-251
haumichblau
Getting the hang of things
Posts: 53
Joined: Sat Jul 05, 2014 4:28 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by haumichblau »

Hello,

I am using this script for backing up my databases since about 1 year.

Since the last QTS-Update to 5.0.1.2234 I get the following error message in the event log:

MySQL Backup: ERROR compressing backup

But I can't find any issue.
The file 20221212.tar.gz is available in the backup directory and can be extracted. When I start the script on the command line, no other messages occur.

Output of the script when I execute manually:

Code: Select all

[/share/backup/ts-453be/mariadb] # /opt/bin/mysqlbackup.sh


MySQL Backup: INFO: MySQL Backup STARTED
MySQL Backup: INFO: Backup share found
MySQL Backup: INFO: subfolder given in config
MySQL Backup: INFO: mysqldump command found
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
MySQL Backup: INFO: mysql command found
MySQL Backup: INFO: Cleaning out old backups. Keeping the last 6 daily backups
MySQL Backup: INFO: Cleaning out old backups. Keeping the last 5 week backups
MySQL Backup: INFO: Cleaning out old backups. Keeping the last 3 montly backups
MySQL Backup: INFO: Backing up current databases to /share/CACHEDEV3_DATA/backup/ts-453be/mariadb/mysql
Backing up database MyVideos119
Backing up database mysql
Backing up database test
MySQL Backup: INFO: Backup Successfull
MySQL Backup: INFO: Creating a weekly archive
MySQL Backup: INFO: Compressing backup to /share/CACHEDEV3_DATA/backup/ts-453be/mariadb/mysql.weekly/221212.tar.gz
MySQL Backup: INFO: Done compressing backup
MySQL Backup: INFO: Cleaning up after archiving
MySQL Backup: INFO: MySQL Backup COMPLETED
[/share/backup/ts-453be/mariadb] #
I think that /bin/tar has a return code <> 0 but I am not able to find the reason for this.

Best regards,
Christian
haumichblau
Getting the hang of things
Posts: 53
Joined: Sat Jul 05, 2014 4:28 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by haumichblau »

Does anyone have an idea what the reason could be?

Best regards,
Christian
m8ichael
New here
Posts: 4
Joined: Sat Sep 12, 2015 11:11 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by m8ichael »

haumichblau wrote: Tue Dec 13, 2022 2:40 am Since the last QTS-Update to 5.0.1.2234 I get the following error message in the event log:

MySQL Backup: ERROR compressing backup

But I can't find any issue.
The file 20221212.tar.gz is available in the backup directory and can be extracted. When I start the script on the command line, no other messages occur.
Hi,

has anyone here already found a solution? The problem I have is that the archive is created, but it was not written without errors, so the error display is generally correct (the archive file is created, parts are also written, but not completely).

But: If I run the script via console, everything works without any problems. Very strange.

Greetings
Michael
User avatar
moody_blue
Easy as a breeze
Posts: 277
Joined: Tue Jan 10, 2017 9:23 am

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by moody_blue »

No issues on my side using the script (with some modifications) using QTS 5.1.2.2533 and MariaDB 10.5.8. Script is executed daily @ 5AM.

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 /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" ; fi

# Listing all the databases individually, and dumping them
databases=$($mysqlc_c -h127.0.0.1 -P3307 -u $user -p$pw -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 -P3307 -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"

QNAP TS-253A 8G QTS 5.0.1.2145
Plex Media Server 1.29.0.6209
OpenHAB 3.4.0.M2
Unifi 7.2.92
Apache80 2454.8230
GLPI 10.0.3
m8ichael
New here
Posts: 4
Joined: Sat Sep 12, 2015 11:11 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by m8ichael »

Hi,
moody_blue wrote: Mon Oct 23, 2023 3:05 am No issues on my side using the script (with some modifications) using QTS 5.1.2.2533 and MariaDB 10.5.8. Script is executed daily @ 5AM.
Everything worked as it should until the last update. I compared the two scripts and actually only noticed a few differences:

Code: Select all

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

Code: Select all

$ec_c -e "Backing up database $line"
  $mysqld_c -S /var/run/mariadb10.sock -u $user -B $line --password=$pw --default-character-set=utf8mb4 > "$bkup_p/mysql/$line.sql"
  if [ $? != 0 ]; then error "creating new backup when trying to access the database $line" ; error=error ; fi
done<<<"$databases"
The rest is completely identical and the error actually only occurs when compressing and only if the script is called via cron. What irritates me, however, is that the error is logged twice, as if the compression command was executed twice.

Edit:
I have now noticed that the following errors occur with individual files during compression (these do not occur if the script is started manually via the console):

Code: Select all

/bin/tar: xyz.sql: file changed as we read it
/bin/tar: xyz.sql: Cannot unlink: No such file or directory
Michael
User avatar
fribse
Experience counts
Posts: 2090
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by fribse »

tuxonroller wrote: Fri Oct 15, 2021 5:16 am I've installed QTS 5 and mariadb10 and changed some things in this (excellent!) script in order to work fine in this new environment:
- add socket parameter : -S /var/run/mariadb10.sock
- add path of mariadb in the searching command section : /share/CACHEDEV1_DATA/.qpkg/MariaDB10
Tested successfully !
@Kenneth Fribert : I let you integrate these changes in the source and change number version if necessary! And thanks for your job!
Hi @tuxonroller

Thankyou for the updates to the script. I'm glad that people still finds this usefull. I'm still baffled that QNAP doesn't provide this per default.
As you can see from the thread, I started doing this script 15 years ago, but unfortunately I no longer have the time to maintain it.
It would be lovely if somebody took over the project and maybe tried packaging it in a QPKG, I tried back then, but didn't really have the time to learn how to build a QPKG correctly.
Also, back then I never used GITHUB, I just had the source code in folders :-) Making revisions complicated.
I'm currently busy doing a danish youtube channel about home automation called Kenneths Teknik (completely hidden commercial :DD ), feel free to look it up, it's in danish though.

My journey with QNAP is ongoing, back from when I started with a TS-409, then getting the honour of being a betatester for QNAP, and now rocking a TS-853A, I still think they make great NAS's.
Best regards
Fribse

NO, RAID is NOT backup - Use RAID-Certified 24x7 drives for raid
HOWTO's:
Make automatic backup of ALL MySQL databases http://forum.qnap.com/viewtopic.php?f=32&t=15628

NAS-659 Pro II (3 GB Ram), Raid1, Survailance station, local backup destination + NAS-853A (16 GB Ram), Virtualization Station, Plex, iDrive
Network: Fritz!Box 7560 + 24p SG200 + 2x8p SG200 + 8p POE EdgeCore + 300/300 mbit Internet
eule68
First post
Posts: 1
Joined: Sat Oct 05, 2024 4:49 pm

Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8

Post by eule68 »

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.8) 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
Post Reply

Return to “Web Server & Applications (Apache + PHP + MySQL / SQLite)”