[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
datorexpert
Been there, done that
Posts: 745
Joined: Mon Nov 16, 2009 4:14 pm

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

Post by datorexpert » Tue Sep 17, 2019 10:18 pm

When did you upgrade the firmware the last time?
Is it a permission issue (guessing)?
QTS 4.1.n/4.2.n/4.3.n/4.4.n MANUAL
Submit QNAP Support Ticket - QNAP Tutorials, Wiki - Product Support Status - Moogle's QNAP FAQ help V2 - QNAP Security Advisory
When you ask a question, please include the following:
Data md_checker nasreport (release 20180525)
=
Model: TVS-873e -- RAM: 20G -- FW: QTS 4.4.2.1302 build 20200512 (used as main storage + applications)
WD60EFRX-68L0BN1(x5) / WD80EFAX-68LHPN0(x1) Red HDDs -- RAID5: 6x6TB -- Cold spare: None
=
Model: TS-453B -- RAM: 8G -- FW: QTS 4.4.1.0978 build 20190626 -- BROKEN
=
Model: SS-839 -- SOLD after 12 years use! Never lost ANY data!
=
12 years of QNAP experience + Asustor and WD!

dehein2
Getting the hang of things
Posts: 50
Joined: Fri Oct 07, 2011 5:32 pm

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

Post by dehein2 » Wed Sep 18, 2019 1:56 am

not for a while (~6month), so nothing changed from the system side

dehein2
Getting the hang of things
Posts: 50
Joined: Fri Oct 07, 2011 5:32 pm

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

Post by dehein2 » Mon Sep 23, 2019 9:03 pm

and the script is able to access the folder, otherwise I wouldn't have any backups, would i?

User avatar
fribse
Experience counts
Posts: 2079
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 » Wed Dec 18, 2019 7:20 pm

@dehein2: Darned, sorry I haven't received your messages, for some reason I don't get notified, and at the moment I'm doing a lot of home automation in Home Assistant, and setting up LED light and all, so I've sort of lost track of this project.
Try two things, set loglevel up in the settings, and try and run the script by hand to see what it outputs.
What NAS is it, and what OS is it?
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

Pompe
New here
Posts: 2
Joined: Tue Oct 11, 2011 5:48 am

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

Post by Pompe » Fri Dec 27, 2019 7:53 am

Hi

Thanks for a great script, I am close to getting it to work on my NAS, TS-219II with 4.3.3.1098

I got this error msg and the compressed file where empty
share/Backups/mysqlbackup/bin/mysqlbackup.sh: line 212: [: missing `]'
MySQL Backup: INFO: Backup Successfull
MySQL Backup: INFO: Creating a daily archive
MySQL Backup: INFO: Compressing backup to /share/MD0_DATA/Backups/MYSQL/mysql.daily/191227.tar.gz
MySQL Backup: ERROR: compressing backup
Any ideas of a solution would be appreciated with great thanks.
Best Regards
Pontus
_____________________________
Qnap TS-219P II
Firmware: 4.3.3.1098

dehein2
Getting the hang of things
Posts: 50
Joined: Fri Oct 07, 2011 5:32 pm

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

Post by dehein2 » Fri Jan 10, 2020 8:12 pm

@fribse

thanks for getting back. For whatever reason ;) it works flawless again
Best

User avatar
goslingcools
Getting the hang of things
Posts: 86
Joined: Sun Oct 11, 2009 7:22 am
Contact:

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

Post by goslingcools » Wed Jul 29, 2020 2:49 am

Hi Fribse,

I have used your script for years now. (Still not implemented by QNAP itself...)
Recently I have installed QMariaDB 10.5.4.0 by Qoolbox https://www.qnapclub.eu/nl/qpkg/525 but I can't get your script to backup its db tables. It keeps grabbing the default QMariaDB (5.x I believe) db tables.
There must be some clever coding possible in your script to now grab the QMariaDB 10.x db tables but I just can't get it to work.
Do you have any suggestions?

Regards,

Gosling
Gosling Cools | COBIZ webdevelopment | https://cobiz.nl

User avatar
moody_blue
Know my way around
Posts: 104
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 » Wed Aug 05, 2020 10:49 pm

goslingcools wrote:
Wed Jul 29, 2020 2:49 am
There must be some clever coding possible in your script to now grab the QMariaDB 10.x db tables but I just can't get it to work.
I was able to make the backup using this modified mysqlbackup.sh file

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"
QNAP TS-253A 4G QTS 4.4.3.1354
Plex Media Server 1.19.4.2935
OpenHAB 2.5.6
Unifi 5.13.29
GLPI 9.4.6

User avatar
goslingcools
Getting the hang of things
Posts: 86
Joined: Sun Oct 11, 2009 7:22 am
Contact:

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

Post by goslingcools » Thu Aug 06, 2020 4:39 am

Hey, very cool. Thanks!
I'll have a go with that.

Regards,

Gosling
Gosling Cools | COBIZ webdevelopment | https://cobiz.nl

User avatar
goslingcools
Getting the hang of things
Posts: 86
Joined: Sun Oct 11, 2009 7:22 am
Contact:

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

Post by goslingcools » Thu Aug 06, 2020 7:35 am

Okay that worked great!
Only had to remove the first line of your code

Code: Select all

#!/bin/bash
.
Thanks!

Gosling
Last edited by goslingcools on Thu Aug 06, 2020 7:36 am, edited 1 time in total.
Gosling Cools | COBIZ webdevelopment | https://cobiz.nl

Post Reply

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