[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
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.6

Post by fribse » Sun Sep 25, 2016 3:35 am

That might be a bug in the script, could you try changing the rights for the subfolders to 777?
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

nevaeh
Starting out
Posts: 12
Joined: Sat Aug 23, 2014 12:23 am

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.6

Post by nevaeh » Sun Sep 25, 2016 12:46 pm

Okay, I went ahead and used chmod on all the mysql folders in addition to the files, ran a backup manually and it still didn't clear anything. I did notice however, that every time I ran a manual backup, the script would overwrite the current days backup with the new backup, leading me to believe it may not permissions based, but possibly the code that does the compare and cleans out older backups.

I know you previously had to edit the script to account for shares with spaces in them, does the code that does the compare by chance need to be tweaked to account for that?

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.6

Post by fribse » Mon Sep 26, 2016 7:35 pm

Ahh, that might be it. I'd forgotten about that, I'll have a look at that asap.
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

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.6

Post by fribse » Sat Oct 22, 2016 4:31 pm

Still working on this, the problem is in the handling of spaces in path, as you thought.
This works

Code: Select all

/bin/rm -f `/bin/ls -t $bkup_p/mysql.daily/*  2> /dev/null | /bin/awk 'NR>'5`

This doesn't work

Code: Select all

/bin/rm -f `/bin/ls -t "$bkup_p/mysql.daily/*"  2> /dev/null | /bin/awk 'NR>'5`


It has to do with the shell embedding with `
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

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by fribse » Mon Oct 24, 2016 1:47 pm

Ok, got it solved, thanks to OneCD. Please test v3.7 of the script, I've done a lot of testing on the path thing, so it should be solid now.
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

Salegy1
Starting out
Posts: 14
Joined: Mon Oct 21, 2013 9:07 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by Salegy1 » Mon Oct 24, 2016 11:19 pm

Runs fine for me.

There are only 2 typo's for deleting old weekly and monthly backups:

Code: Select all

# # 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 +$(expr $day_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 +$(expr $day_ret + 1)) ; do rm -f "$full/$target"; done
if [ $? != 0 ] ; then error "ereasing old montly backups" ; fi



- Line 198: " $day_ret " has to be changed in " $week_ret "
- Line 204: " $day_ret " has to be changed in " $month_ret "

Thanks for your great work!

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by fribse » Mon Oct 24, 2016 11:24 pm

Yup, just saw that as well, I'll update NOW
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

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by fribse » Mon Oct 24, 2016 11:27 pm

Script is fixed, darned :-)
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

nevaeh
Starting out
Posts: 12
Joined: Sat Aug 23, 2014 12:23 am

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by nevaeh » Thu Oct 27, 2016 10:32 pm

For some reason when I drop in the new script, it can no longer find my share name. Was something changed with how it finds the share?

Please see the attached screenshot.
You do not have the required permissions to view the files attached to this post.

nevaeh
Starting out
Posts: 12
Joined: Sat Aug 23, 2014 12:23 am

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by nevaeh » Thu Oct 27, 2016 10:55 pm

nevaeh wrote:For some reason when I drop in the new script, it can no longer find my share name. Was something changed with how it finds the share?

Please see the attached screenshot.


I think I figured it out....I had to add quotes to $share in the following line:
bkup_p=$($get_c "$share" path -f /etc/config/smb.conf)

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by fribse » Fri Oct 28, 2016 12:53 pm

I see, odd, I had it running here with spaces. I'll update the script right away, thankyou for the fix!
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

User avatar
fribse
Experience counts
Posts: 2076
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases V3.7

Post by fribse » Fri Oct 28, 2016 1:08 pm

The script is updated now, there were a few more regression errors with quotes from when I cleaned it up. The lines handling if the 'mysql.*' folders are missing, also missed some quotes.
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

nevaeh
Starting out
Posts: 12
Joined: Sat Aug 23, 2014 12:23 am

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

Post by nevaeh » Mon Nov 14, 2016 4:07 am

Just wanted to say thank you for everything! It looks to be working perfectly now. :)

trendchiller
Easy as a breeze
Posts: 303
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller » Wed Nov 30, 2016 7:41 am

Big thanks for that !!!

User avatar
GTunney
Been there, done that
Posts: 591
Joined: Tue Oct 14, 2014 4:16 pm

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

Post by GTunney » Tue Dec 06, 2016 11:22 pm

Hi Fribse,

I'm trying to configure the mysql backup and have managed to get this to run manually via putty when I input the command and it runs absolutely fine.

How ever when I configure to run via cron I get an error when it tries to compress the database. any suggestions?
Model: TS-653B 8GB
FW: 4.4.1.1031 Build: 20190816
Disks: 3 x 4TB Western Digital WD40EFRX - RAID 5
Total Storage: 7.2TB
Applications: Plex MS | QCouchPotato | QSabnzbd+ | QSickrage | QHeadphones | HTPC Manager | Kodi v18.4 MySQL
Other Devices: Netgear D7000 AC1900 VDSL Router | FTTC - 80/20 | Netgear GS108 Gigabit Switch

Post Reply

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