To ease the selection for the standard backup routines, ie. backup to USB disk etc.Don wrote:hi,
Why does the destination have to be a share?
Don
[HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8
- 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
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
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
- 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
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.
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
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
-
- Know my way around
- Posts: 107
- Joined: Thu Jan 17, 2008 11:45 pm
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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
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
- lentzit
- Been there, done that
- Posts: 518
- Joined: Thu Dec 27, 2007 7:32 pm
- Location: Sweden
- Contact:
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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.1eeeuser1 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
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)
-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)
- 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
And lentzit, it requires a second machine being on, silly when you have it all in the box itself
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
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
- lentzit
- Been there, done that
- Posts: 518
- Joined: Thu Dec 27, 2007 7:32 pm
- Location: Sweden
- Contact:
Re: [HOWTO] Make automatic backup of ALL MySQL databases
Its just another solution that might fit someone else toofribse wrote:And lentzit, it requires a second machine being on, silly when you have it all in the box itself
-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)
-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)
- 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 databases
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.
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 | https://cobiz.nl
- 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
Thankyou for your kind words, flatter will get you anywhere
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.
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
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
- 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
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
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
- 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 databases
MySQL Backup: Backup Successfull!
All 51 databases were included this time.
Thanks for the quick and working fix!
Regards,
Gosling Cools
All 51 databases were included this time.
Thanks for the quick and working fix!
Regards,
Gosling Cools
Gosling Cools | COBIZ webdevelopment | https://cobiz.nl
- 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
i can't seem to make this work.... I've added it to crontab but just tried running the command manually, and I get
I was getting more errors along the lines of
which corresponded to any blank lines in the script, so I've taken them all out. So lines 26 and 27 are these:
(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
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] #
Code: Select all
: command not foundp.sh: line 6:
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)
- 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
i AM an idiot.
File was Windows encoded, so I changed it in PSPad to Unix, saved and it worked fine.....
d'oh.
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)
- 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
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
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
-
- New here
- Posts: 5
- Joined: Tue Dec 22, 2009 11:57 am
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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 !
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 !
- 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
I think it's complicated, because linux saves configs in a LOT of files.
I would use the webui.
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
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