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

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

Post by datorexpert »

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 5.1.1.2491 build 20230815 (used as main storage + applications)
WD60EFRX-68L0BN1(x6) / WD80EFAX-68LHPN0(x1) Red HDDs -- RAID5: 8x6TB -- 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!
=
13 years of QNAP experience + Asustor and WD!
dehein2
Getting the hang of things
Posts: 51
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 »

not for a while (~6month), so nothing changed from the system side
dehein2
Getting the hang of things
Posts: 51
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 »

and the script is able to access the folder, otherwise I wouldn't have any backups, would i?
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 »

@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 »

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: 51
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 »

@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 »

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
Easy as a breeze
Posts: 266
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 »

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 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
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 »

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 »

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
abentzen
First post
Posts: 1
Joined: Sun Oct 25, 2020 4:32 pm

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

Post by abentzen »

Hi!

Never mind! Found it :-)
As I said - rookie :-)

Neewbie question, but I give up.
Where are the backupscript and the config file(s) so I can download and use?

-Alexander
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!
Thanks a lot fribse for your script.
Works perfect. It should be included in QTS by default in sql server configuration menu, isn't it ? :wink:
On your documentation, following Qnap wiki for cron, don't forget to reload the crontab and to restart the service to apply changes :
https://wiki.qnap.com/wiki/Add_items_to_crontab
Have a nice day and happy christmas to everyone!
karls0
Starting out
Posts: 10
Joined: Thu Mar 14, 2019 5:50 pm

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

Post by karls0 »

Thanks to fribse and moody_blue. I admit, I did not compare moody_blues version with the version of fribse since it was much newer.
I got some errors till I found out, that moody_blue is using port 3308 on his mySQL/MariaDB. I changed it to 3306 (in two locations) and now it seems to work.
I just put it into crontab and will see tomorrow.
Thanks again!
Karl
TVS-463 running QTS 5.1.2.2533 with two Seagate ST4000NM000A and two ST4000NM0035 as Raid5
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 »

Hi All
I am no longer maintaining this script, I started the script more than 10 years ago, because QNAP in their infinite wisdom FORGOT to backup the qnap databaase.
It has been a pleasure to see how many people have had benefit from this rather raw script. I never got around to make it a package, simply couldn't get my head around the way to place the files in the right locations :-)
I can see others have built upon the script with their own solutions, and I hope @goslingcools and @moody_blue will keep the torch burning together with the rest of you, I think it's fantastic to see that the script still has merit (and apauling that QNAP has still not provided at 'public' solution for doing smart backups.
Have fun all! Bye and thanks for all the fish :-)
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
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 »

Hi all,
i need help.
i am running Paperoffice DMS on a QNAP 453 and was really astonished that there is no native solution by QNAP to Backup the SQL-Server.
Paperoffice support is linking to this thread :)
Took me a day, but i have this script running.
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.

Thanks in advance
Thomas
Post Reply

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