[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
mr.Loma
New here
Posts: 4
Joined: Thu May 23, 2013 6:45 pm

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

Post by mr.Loma »

hmm seems like you're right, it's not working on qnap, that's a bit weird
mr.Loma
New here
Posts: 4
Joined: Thu May 23, 2013 6:45 pm

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

Post by mr.Loma »

will it stay through a firmware update? or do you have to redo this every time?
User avatar
schumaku
Guru
Posts: 43579
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

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

Post by schumaku »

mr.Loma wrote:will it stay through a firmware update? or do you have to redo this every time?
Hard to say. Ask QNAP what new ideas the have. :shock: In general, QNAP unrelated entries are retained.
mr.Loma
New here
Posts: 4
Joined: Thu May 23, 2013 6:45 pm

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

Post by mr.Loma »

ok thanks, I'll have to check after next update ;)
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 databases V3.4

Post by fribse »

I've upgraded several times, also through major upgrades, its stayed in there most every time :-)
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: 2090
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse »

br14nh wrote:Having learned the hard way that sql tables are not backed up by NAS to NAS backups..... Excellent script Fribse, many thanks!!
Yup, been there as well :-(
Anyway, given that your script is being used by many of the less experienced (like myself) I just wanted to ask if you could amend your instructions in the first post to clarify:
1) the creation of the share in the QNAP's admin console,
2) if there is a way to recognise subdirectories in existing shares like Web, some clues on getting it to work?
Again, many thanks for your efforts - over many years, yet!
You are most welcome Brian.
I've written a few extra words on the instructions.
Of course you can use another share, it's setable in the config.
But it's on purpose that I'm not making it use a long path to back up to.
This is safety first :-) Some users wants to access the backup files via windows, and windows has a problem with paths longer than 255 chars, so to uncomplicate things I've just required a share.
You can create a share on a subdirectory of another share if you wish, inside QNAP's config.
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: 2090
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse »

Script is updated to 3.5 :-)
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
ensignvorik
Easy as a breeze
Posts: 364
Joined: Sat Jul 14, 2012 8:24 pm

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

Post by ensignvorik »

Reading the notes for 4.2 firmware, I see it's going to replace MySQL with MariaDB (Still on an older version of 4.1.x) whilst I can see one comment above that this script broke for someone else, unless I'm being blind I can't see that it's 100% resolved or if at all?

So, I guess my basic question is, if I upgrade to 4.2 and my DB converts to MariaDB, will this still work? Have any paths or instructions changed?
Unless I'm being blind, I can't find the setting to change what kind of QNAP I have on my profile. I now own a TS-253A
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 databases V3.5

Post by fribse »

It should work without any problems, the MariaDB post was not because of the switch.
I've gone to MariaDB here a long time ago, and haven't noticed anything not working, but instead it's all working a bit faster.
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
datorexpert
Been there, done that
Posts: 777
Joined: Mon Nov 16, 2009 4:14 pm
Contact:

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

Post by datorexpert »

I´m running fw 4.2.0 B0925 and the backup is no working any more.
How can I troubleshoot?
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!
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 databases V3.5

Post by fribse »

Run it 'by hand', make the loglevel higher, check the crontab
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
Koecher
First post
Posts: 1
Joined: Sat Jan 03, 2015 10:29 pm

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

Post by Koecher »

Verry cool script, thanks a lot for that!

As Qnap now uses maria-db I've added the path:

Code: Select all

for MYSQLD_P in /mnt/ext/opt/mariadb ...

Code: Select all

for MYSQLC_P in /mnt/ext/opt/mariadb ...
To be honest, I havent tested if it works without that :' Infact, it works with that :mrgreen:
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 databases V3.5

Post by fribse »

You're most welcome.
Good idea, I'll update the script to include that as well :-)
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.5

Post by nevaeh »

Hello,

I'm hoping someone can point me in the correct direction or if I'll get lucky enough for fribse to respond!

I'm trying to set up this script on a volume named "Drive 1 (3TB)". I have created the share "DBBackup" and ran through the steps outlined on the first post..the script gets to the part where it checks to see if the "mysql" subfolder exists in the "DBBackup" share, doesn't find it and tries to create it.

The problem is that either the script or the OS is not treating spaces correctly and instead stops at: "/share/HDA_DATA/Drive".

Any advice besides needing to re-initialize my QNAP with a different volume name?

Thank you,

Brandon
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 databases V3.5

Post by fribse »

Hi Brandon

I'll have a look at it, and see if I can figure out a clever way of handling spaces in the path...

I just tested, you're not allowed to create a volume with a space in it, only a share.
I'll continue to have a look at it, but for now, just create a share without a space in 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
Post Reply

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