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

[fribse]

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.

[fribse]

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.

[eeeuser1]

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

[lentzit]

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!

[fribse]

And lentzit, it requires a second machine being on, silly when you have it all in the box itself

[lentzit]

And lentzit, it requires a second machine being on, silly when you have it all in the box itself

Its just another solution that might fit someone else too

[goslingcools]

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.

[fribse]

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.

[fribse]

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

[goslingcools]

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

Thanks for the quick and working fix!

Regards,

Gosling Cools

[rainjam]

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

[rainjam]

i AM an idiot.

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

d'oh.

[fribse]

Well, you're not the first to do that *BLUSH*

[nicolas.caze]

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 !

[fribse]

I think it's complicated, because linux saves configs in a LOT of files.
I would use the webui.