[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
Iceman24
Getting the hang of things
Posts: 95
Joined: Thu Nov 19, 2015 8:44 am

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

Post by Iceman24 »

trendchiller wrote:@Iceman24:
Does not seem you have any databases ;-)
Thanks. Good to know. :)
trendchiller
Easy as a breeze
Posts: 309
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller »

No prob ;-)
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

@Toxic17: Sure, good suggestion, I'll consider that, I'll probably build in a switch for those that don't want to use 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
damianh
New here
Posts: 9
Joined: Mon Nov 05, 2012 1:48 am

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

Post by damianh »

Hello,

The script seems to be working, thank you.

But I am receiving the following error when it runs " MySQL Backup: ERROR compressing backup". Can anyone point me in the right direction to fix this?

Image
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

Could you try and run the script on the commandline, and give the output from there?
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
aardvark
Starting out
Posts: 34
Joined: Tue Nov 20, 2012 11:58 am

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

Post by aardvark »

Kudos to Frisbee :DD . I just had to do a real restore of my databases (transition to TS-453A) and it worked like a dream! Well done, mate.
TS-453A 4 3TBWDReds in RAID5 4G Mem

40 years a programmer and still sane. -ish. Bring back COBOL - now!
trendchiller
Easy as a breeze
Posts: 309
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller »

Compression error persists with !TS 4.3.4 beta, too in the GUI.
It seems to be a cosmetic error though, the file ist compressed and working...

In the commandline it's all running fine:
Backing up database xxx
MySQL Backup: INFO: Backup Successfull
MySQL Backup: INFO: Creating a weekly archive
MySQL Backup: INFO: Compressing backup to /share/CACHEDEV4_DATA/Backups/MySQL/mysql.weekly/171127.tar.gz
MySQL Backup: INFO: Done compressing backup
MySQL Backup: INFO: Cleaning up after archiving
MySQL Backup: INFO: MySQL Backup COMPLETED


the script is run as admin, the crontab is the admins crontab.

Does anyone have any idea ?
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

Thankyou aardvark! :-)

@trendchiller:
So the tar command is running, if it compresses, so somehow the tar command returns something else than 0 as result, and that triggers the error report.

You could try and modify the tar command line, so instead of it piping the output of that command to &>/dev/null you pipe it to a text file you can read afterwards, and then post the output here?
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
trendchiller
Easy as a breeze
Posts: 309
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller »

a question besides:
should the errorlevel not be the same when run from the commandline or the crontab ?

---

so i modified the line as follows:

Code: Select all

$tar_c 2> /share/Backups/MySQL/mysqlbackup/tar_output_1.txt -czvf "$bkup_p/$dest/$arc" * --remove-files &>/share/Backups/MySQL/mysqlbackup/tar_output_2.txt
output is:
tar_output_1.txt:
empty

tar_output_2.txt:

FreshRSS.sql
kplaylist.sql
limesurvey.sql
mysql.sql
phpmyadmin.sql
piwigo.sql
solo.sql
TeemIp.sql
trendchiller
Easy as a breeze
Posts: 309
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller »

and now we habe it executed by cron and the errors are visible ;-)

output is:
tar_output_1.txt:
empty

tar_output_2.txt:
TeemIp.sql
/bin/tar:/bin/tar: iTop.sql: file changed as we r/bin/tar: TeemIp.sql: Cannot unlink: No such file or directory
/bin/tar: iTop.sql: Cannot stat: No such file or directory
/bin/tar: kplaylist.sql: Cannot stat: No such file or directory
/bin/tar: limesurvey.sql: Cannot stat: No such file or direc/bin/tar: kplaylist.sql: Cannot unlink: No such file or directory
/bin/tar: limesurvey.sql: Cannot stat: No such file or directory
/bin/tar: mysql.sql: Cannot stat: No such file or directory
/bin/tar: phpmyadmin.sql: Cannot stat: No such file or directory
/bin/tar: php/bin/tar: phpmyfaq.sql: file changed as we read it
/bin/tar: piwigo.sql: Cannot stat: No such file or directory
/bin/tar: solo.sql: Cannot stat: No such file or directory
/bin/tar: Exiting with failure status due to previous errors
vious errors

So there seems to be a difference between cron and commandline
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

When it runs in Cron it doesn't have the environment variables, such as path etc.
But that usually only affects commands.

Ok, so the lines to focus on are these:

Code: Select all

# Changes folder to backupfolder
cd "$bkup_p/mysql/" 
# Compressed file to tar and removes them afterwards
$tar_c 2> /dev/null -czvf "$bkup_p/$dest/$arc" * --remove-files &>/dev/null
#Checks if an error is reported
if [ $? != 0 ] ; then error "compressing backup" ; else info "Done compressing backup" ; fi
Question is, are the files present as they should be on the backup destination when tar runs?
The tar command is asked to clean out files after they are archived, so if you remove the parameter '--remove-files' the files should be left after the archive command has run, and you can see in the folder what size they have.
You could also try to copy the files to another location just to see if they are 'unlocked' and readable.
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
trendchiller
Easy as a breeze
Posts: 309
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller »

With cron AND cli the files are there when removing the parameter.
And the files are removable and movable.
What does „cannot unlink“ mean ? Access denied ?
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

That is very strange, who is marked as the owner of the file and folder? (ls -l)?
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
trendchiller
Easy as a breeze
Posts: 309
Joined: Sun Nov 29, 2009 6:43 pm
Location: Germany

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

Post by trendchiller »

Hi !
Sorry for the late reply...

permissions are as follows:

-rw-rw---- 1 admin administ 904827 Dec 15 12:05 iTop.sql
-rw-rw---- 1 admin administ 15326211 Dec 15 12:05 kplaylist.sql

for all files... (when cron runs the script)
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

Hmm, do you have any sort of soft or hard links set up for the storage path?
I've never heard of that error before.
You could try to add the '--overwrite' parameter to the tar 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
Post Reply

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