Thanks. Good to know.trendchiller wrote:@Iceman24:
Does not seem you have any databases
[HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8
-
- 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
-
- Easy as a breeze
- Posts: 310
- Joined: Sun Nov 29, 2009 6:43 pm
- Location: Germany
- 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
@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
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: 9
- Joined: Mon Nov 05, 2012 1:48 am
Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.7
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?
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?
- 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
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
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
-
- 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
Kudos to Frisbee . 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!
40 years a programmer and still sane. -ish. Bring back COBOL - now!
-
- Easy as a breeze
- Posts: 310
- Joined: Sun Nov 29, 2009 6:43 pm
- Location: Germany
Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.7
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 ?
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 ?
- 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
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?
@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
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
-
- Easy as a breeze
- Posts: 310
- Joined: Sun Nov 29, 2009 6:43 pm
- Location: Germany
Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.7
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:
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
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
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
-
- Easy as a breeze
- Posts: 310
- Joined: Sun Nov 29, 2009 6:43 pm
- Location: Germany
Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.7
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
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
- 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
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:
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.
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
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
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
-
- Easy as a breeze
- Posts: 310
- Joined: Sun Nov 29, 2009 6:43 pm
- Location: Germany
Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.7
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 ?
And the files are removable and movable.
What does „cannot unlink“ mean ? Access denied ?
- 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
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
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
-
- Easy as a breeze
- Posts: 310
- Joined: Sun Nov 29, 2009 6:43 pm
- Location: Germany
Re: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.7
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)
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)
- 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
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?
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
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