[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
adnovea
Easy as a breeze
Posts: 490
Joined: Sat Jan 26, 2008 2:47 am

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

Post by adnovea »

Hi fribse,

I just found your post. That's great and THX.
So, you finally wrote the script for the whole database backup following our discussion in june http://forum.qnap.com/viewtopic.php?f=32&t=15310

I have the solution working daily since then without any trouble.
I also use a rolling list to keep only the latest archives.

May be a future feature ;-)
  • Create a folder with the date
  • keep the X latest archives

Code: Select all

CMD_AWK="/bin/awk"
CMD_RM="/bin/rm" 
SQL_BKP_NB=15	# Keep the latest 2 weeks archives
$CMD_RM -fR `ls -t  /share/Qbackup/mysql/*| $CMD_AWK 'NR>'$SQL_BKP_NB`
I have not checked the code, just did a quick translation of what I'm currently using
See my other projects at Sourceforge & Instructables
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

Post by fribse »

adnovea wrote:Hi fribse,
I just found your post. That's great and THX.
So, you finally wrote the script for the whole database backup following our discussion in june http://forum.qnap.com/viewtopic.php?f=32&t=15310

I have the solution working daily since then without any trouble.
I also use a rolling list to keep only the latest archives.

May be a future feature ;-)
  • Create a folder with the date
  • keep the X latest archives

Code: Select all

CMD_AWK="/bin/awk"
CMD_RM="/bin/rm" 
SQL_BKP_NB=15	# Keep the latest 2 weeks archives
$CMD_RM -fR `ls -t  /share/Qbackup/mysql/*| $CMD_AWK 'NR>'$SQL_BKP_NB`
I have not checked the code, just did a quick translation of what I'm currently using
Hi Adnovea
Yes, the find with the absolute paths solved a lot for me :-)
Well, the script above already cleans up after 7 days of backup.
It could be made prettier with variables I know, but anyways, it's there :-)
It also creates an archive named by the date, and I'm very glad for the achive structure for older backups, instead of a lot of folders, it sort of emphasizes that you're going back a while :-)

I haven't tried using the awk command, looks very nifty, but it's use here eludes me. You pipe the rm commands output to awk???
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

Post by fribse »

Ahh, got it, figured out what the script did (missed a `), I've altered the script, makes a bit more elegant :-)
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
jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

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

Post by jm45 »

Hi,

as a tester again: :)

copy/paste problem ?
something is missing as output ...! no?

$MYSQL_C -u backup -p $LINE --password=PNcCS9feVvVnn35W --single-transaction > $

pervious version contained something like > /share/Qbackup/mysql/$LINE.sql instead of > $
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
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

Post by fribse »

Hi JM45, you're absolutely right, bad copy paste from my ssh shell, I've corrected it 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
jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

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

Post by jm45 »

then , real test ok now on my TS-109, backup well done :)
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
maur
New here
Posts: 2
Joined: Sat Aug 22, 2009 5:10 pm

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

Post by maur »

Hi, I am a newbie to scripts on the qnap (and linux/unix for that matter). I plan to implement your script, it looks like a good piece of work. I am wondering what exactly do you mean by:
"remember to mark it with +x". ? Is this some kind of file naming standard?. Also btw, on my QNAP the path /opt/bin doesnt exist. There is only one file under the /opt. (nasconfig_fs.img.tgz)
I appreciate your reply. Regards, Maur.
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

Post by fribse »

maur wrote:Hi, I am a newbie to scripts on the qnap (and linux/unix for that matter). I plan to implement your script, it looks like a good piece of work.
Thankyou, flatter will get you anywhere :-)
maur wrote:I am wondering what exactly do you mean by:
"remember to mark it with +x". ? Is this some kind of file naming standard?.
No, to mark it with +x is a confusing statement I guess, if you know nothing about Linux :-)
To mark it with +x is to mark it executable (like with windows, you can writeprotect a file), linux won't execute a file unless it's marked executable.
To mark it with +x, you execute the aforementioned CHMOD statement.
maur wrote:Also btw, on my QNAP the path /opt/bin doesnt exist. There is only one file under the /opt. (nasconfig_fs.img.tgz)
I appreciate your reply. Regards, Maur.
Well, the TS-x09 series is structured a bit different.
You can place the script whereever you like, if you've placed it in a share, you can keep it there if it's the easiest for you, just remember the path for it, and remember to mark it executable. Or you could create the folder under /opt yourself, and place the script there.

To modify the path to the mysqldump command, you issue the find statement mentioned.
I hope this 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
maur
New here
Posts: 2
Joined: Sat Aug 22, 2009 5:10 pm

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

Post by maur »

Thanks for the crash course.. I feel ready to give it a go now! :D
patbaker82
Getting the hang of things
Posts: 63
Joined: Fri Aug 21, 2009 6:54 am

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

Post by patbaker82 »

hey there, good job. I just have some comments.

- If one step of the backup fails in your code, you're not checking $? for proper error handling
- Should store the password in a read only hidden file and source the file
- I dont have one of these devices yet, but if mysqldump is in the PATH wouldnt "which mysqldump" work and be more efficient than a find?
- I'd recommend doing the mysqldump with -eqQ as well for larger datasets
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

Post by fribse »

You are of course welcome to refine the script.
Good point with the -eqQ, that should be added.
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

Post by fribse »

I need your help, especially if you're not using a TS-639 as I am. Could you please try with the 'which mysqldump' command to see if it always finds the mysqldump command correctly?
I'm not sure it's always located in the path on all systems. Using the which will simplify the searching for the command.
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
jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

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

Post by jm45 »

Hi fribse, :)
i got trouble finding my Qbackup folder;
in fact, depending on the way we create (and link ..or not) Qbackup new folder,

Qbackup should be accessed by
BKUP_P="/share/HDA_DATA/Qbackup"
or
BKUP_P="/share/MDO_DATA/Qbackup"
and not
BKUP_P="/share/Qbackup"
............................................................................................
i got no result for the "which mysqldump" command
[/share/HDA_DATA/Qbackup] #
[/share/HDA_DATA/Qbackup] # which
BusyBox v1.01 (2009.07.07-21:09+0000) multi-call binary
Usage: which [COMMAND ...]
Locates a COMMAND.
[/share/HDA_DATA/Qbackup] # which mysqldump
[/share/HDA_DATA/Qbackup] # which grep
/bin/grep
[/share/HDA_DATA/Qbackup] # which getty
/sbin/getty
[/share/HDA_DATA/Qbackup] # which mysqldump
[/share/HDA_DATA/Qbackup] #

[/share/HDA_DATA/Qbackup] # echo $PATH
/bin:/sbin:/usr/bin:/usr/sbin:/usr/bin/X11:/usr/local/sbin

on my TS-109
MYSQL_C="/mnt/HDA_ROOT/mysql/bin/mysqldump"
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
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

Post by fribse »

Thanks for testing it jm45. I seemed to recall that it wasn't in the path on the x09's. That was why I used the find instead.
I would like to find out how I can determine if there is a RAID system on the NAS automatically, that could make the script easier to use.
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
jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

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

Post by jm45 »

mysqldump: why not running the find command inside your script and then get the result to set the MYSQL_C=

if raid or not, it should be nice to be determined too and have less variable to modify
:)
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
Post Reply

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