[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: 2077
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse » Sun Oct 25, 2009 4:13 am

Don wrote:hi,

Why does the destination have to be a share?

Don


To ease the selection for the standard backup routines, ie. backup to USB disk etc.
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: 2077
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse » Sun Oct 25, 2009 4:39 am

Ok, so the script is updated to 2.2. The addition is logging to the system log.
I've made the logging to include almost all messages, except the message for the individual database.
I'm a bit in doubt whether it should also include the 'backed up xxx database' in the log. With a lot of databases, the log is going to get filled with messages. So for now I'm excluding those messages, let me know your opinion.
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

eeeuser1
Know my way around
Posts: 107
Joined: Thu Jan 17, 2008 11:45 pm

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

Post by eeeuser1 » Wed Jun 23, 2010 7:53 pm

Hi,
thanks for this, works great, and displays in the logs also, so excellent.

For all you GUI fans out there, although I'm comfortable enough with linux, I installed WinSCP and PuTTY on my windows PC and was able to accomplish nearly all of the command line stuff from within WinSCP, including editing your script for number of backups etc, and also the editing of crontab, as Vi is a bit of a monster. I still used PuTTY to test the command and also to get it into CRON.

thanks

User avatar
lentzit
Been there, done that
Posts: 516
Joined: Thu Dec 27, 2007 7:32 pm
Location: Sweden
Contact:

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

Post by lentzit » Thu Jun 24, 2010 3:10 am

eeeuser1 wrote:Hi,
thanks for this, works great, and displays in the logs also, so excellent.

For all you GUI fans out there, although I'm comfortable enough with linux, I installed WinSCP and PuTTY on my windows PC and was able to accomplish nearly all of the command line stuff from within WinSCP, including editing your script for number of backups etc, and also the editing of crontab, as Vi is a bit of a monster. I still used PuTTY to test the command and also to get it into CRON.

thanks


If you are a Windows person you also can install MySQL Administration which gives you a nice GUI for handling your database. Remeber though that you first need tou either allow the current admin to connect from the "outside" or create a new user that is given the right to access to the NAS SQL from another place other than 127.0.0.1

Link: http://dev.mysql.com/downloads/gui-tools/5.0.html

Theres is a newer version called Workbench, i find that harder to use than the above client!
-TVS-663/16GB QTS 4.2 , RAID5
-QGenie QG-103N - Wireless Storage in Classroom Environment
-Services: WEB | Virtual Station | QSYNC | FTP | SSL | Private Cloud | Photo Station
-Using it for my blog (http://blog.lentzit.com)

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

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

Post by fribse » Thu Jun 24, 2010 5:04 am

And lentzit, it requires a second machine being on, silly when you have it all in the box itself :-D
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
lentzit
Been there, done that
Posts: 516
Joined: Thu Dec 27, 2007 7:32 pm
Location: Sweden
Contact:

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

Post by lentzit » Sat Jun 26, 2010 10:51 pm

fribse wrote:And lentzit, it requires a second machine being on, silly when you have it all in the box itself :-D


Its just another solution that might fit someone else too :-)
-TVS-663/16GB QTS 4.2 , RAID5
-QGenie QG-103N - Wireless Storage in Classroom Environment
-Services: WEB | Virtual Station | QSYNC | FTP | SSL | Private Cloud | Photo Station
-Using it for my blog (http://blog.lentzit.com)

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

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

Post by goslingcools » Thu Oct 14, 2010 4:12 pm

Very nice script!

I noticed that some databases are skipped in the backup process, about 10 in my case of the 51 I currently have in use.
The log does not end with "Backup Successfull" but also does not tell me what went wrong...
I optimized all databases, phpadmin and the php sites can work fine with these skipped databases.
What's wrong?


Some tool like this should come standard with the Qnap's firmware.
Gosling Cools | COBIZ webdevelopment | http://www.cobiz.nl

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

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

Post by fribse » Thu Oct 14, 2010 6:45 pm

Thankyou for your kind words, flatter will get you anywhere :-D

I think it has to do with the way I list the databases. There are standard mysql commands to accomplish a complete list (mysql -u root -h 127.0.0.1 -ppassword -Bse 'show databases'
), but if I use that in a script, I get an error on the QNAP (though haven't looked at it recently, I'll have a look today with the newer firmwares).
Instead I do a ls of the databases, and process that list a bit with diverse commands, this is probably why it fails.
There are some standard routines built in to the script to do more or less logging, I could create a switch for 'verbose' or normal I guess :-)
I'll look into it, and if I can solve it the right way, I'll post an update later.
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: 2077
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse » Thu Oct 14, 2010 7:00 pm

I've changed the script, the database list command seems to work on the later firmwares, so it's using that now. See if that helps a bit...
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
goslingcools
Getting the hang of things
Posts: 83
Joined: Sun Oct 11, 2009 7:22 am

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

Post by goslingcools » Fri Oct 15, 2010 2:36 am

MySQL Backup: Backup Successfull!
All 51 databases were included this time.

Thanks for the quick and working fix!

Regards,

Gosling Cools
Gosling Cools | COBIZ webdevelopment | http://www.cobiz.nl

User avatar
rainjam
Getting the hang of things
Posts: 88
Joined: Thu Sep 24, 2009 11:40 pm

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

Post by rainjam » Mon Nov 01, 2010 7:45 pm

i can't seem to make this work.... I've added it to crontab but just tried running the command manually, and I get

Code: Select all

[/opt/bin] # /opt/bin/mysqlbackup.sh
'opt/bin/mysqlbackup.sh: line 26: syntax error near unexpected token `
'opt/bin/mysqlbackup.sh: line 26: `error ()
[/opt/bin] #


I was getting more errors along the lines of

Code: Select all

: command not foundp.sh: line 6:


which corresponded to any blank lines in the script, so I've taken them all out. So lines 26 and 27 are these:

Code: Select all

error ()
{ $EC_C -e "\nERROR $1\n" ; $LOC_C "MySQL Backup: ERROR $1" 1 ; exit 1 ; }




(I'm editing with PSPad on Windows and then copying over the old file, rather than on the script itself using vi, as I don't trust myself poking around with files directly.... so I don't know if this is a Windows/Linux encoding problem or something)

Apologies in advance if I'm being an idiot with this (almost certainly the case)

Cheers

Nick
TS-EC1279, 120TB, 8GB RAM (main) and TS-809, 32TB, 2GB RAM (backup)

User avatar
rainjam
Getting the hang of things
Posts: 88
Joined: Thu Sep 24, 2009 11:40 pm

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

Post by rainjam » Mon Nov 01, 2010 7:53 pm

i AM an idiot.

File was Windows encoded, so I changed it in PSPad to Unix, saved and it worked fine.....

d'oh.
TS-EC1279, 120TB, 8GB RAM (main) and TS-809, 32TB, 2GB RAM (backup)

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

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

Post by fribse » Mon Nov 01, 2010 8:05 pm

:-D Well, you're not the first to do that *BLUSH*
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

nicolas.caze
New here
Posts: 5
Joined: Tue Dec 22, 2009 11:57 am

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

Post by nicolas.caze » Fri Jan 14, 2011 8:27 am

Hi
is it possible to back-up (and restore on the same or different QNap) at least part of the system configuration without using the web interface, in a way similar to this (very well crafted and extremly useful) script ?

Or alternatively, what config files are critical to copy / backup to minimize manual setup time in case of QNap crash and restore ?

Thanks for your answer, and for sharing your expertise !

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

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

Post by fribse » Sat Jan 15, 2011 5:11 am

I think it's complicated, because linux saves configs in a LOT of files.
I would use the webui.
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)”