[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.
jelv1
New here
Posts: 4
Joined: Wed Nov 11, 2015 12:19 am

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

Post by jelv1 »

Having been running this script successfully for many months but a week or so ago it started giving this error:
ERROR compressing backup
Having modified the script to remove the redirect of stderr on the tar command and then running the script using putty I found it was giving the error:
/bin/tar: databasename.sql: file changed as we read it
where databasename is the last database.

I think the timings must have changed and it is trying to tar the file before it has properly closed.

I've added a sleep 5 after the line cd $BKUP_P/mysql/ and it now runs without error via putty - hopefully the overnight cron will now also run correctly!

Posting this in case it helps others.

Edit: Confirmed overnight runs without error.
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 »

I see, that is odd, thankyou for this input, I'll add it to the next update!
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
talekksna
New here
Posts: 7
Joined: Fri Aug 14, 2015 12:35 am

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

Post by talekksna »

Hi,
i am very grateful for this script. Is there a way to backup specific mysql base? Not all bases always, but to choose witch one I want to backup. To define in scrip what I want to backup.
Maybe you discus it, but I could not find that information.

Thank you.
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 »

Hi Telekksna

Thankyou for your kind word, well currently it's not designed to only take a specific DB, and I don't think I'll change the code for it, but it's quite easy to modify it.
In the section 'Listing all the databases...'
You could just change the databases=
to
databases="my database" ;

Best regards
Kenneth
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 / MariaSQL databases on QNAP V3.7

Post by fribse »

jelv1 wrote:I've added a sleep 5 after the line cd $BKUP_P/mysql/ and it now runs without error via putty - hopefully the overnight cron will now also run correctly
Hi jelv1

Did it solve the problem for good? Then I'll add the bit to the code.
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
Toxic17
Ask me anything
Posts: 6468
Joined: Tue Jan 25, 2011 11:41 pm
Location: Planet Earth
Contact:

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

Post by Toxic17 »

History
Can now be found in the file
changes.zip
Changes.zip is not there any longer. do you have a current change log?

Also:
fribse wrote: Wed Aug 16, 2017 5:33 pm @Toxic17: Sure, good suggestion, I'll consider that, I'll probably build in a switch for those that don't want to use it
did you ever build in a switch to autoreapir/optimise databases before backup in your script?
Regards Simon

Qnap Downloads
MyQNap.Org Repository
Submit a ticket • QNAP Helpdesk
QNAP Tutorials, User Manuals, FAQs, Downloads, Wiki
When you ask a question, please include the following


NAS: TS-673A QuTS hero h5.1.2.2534 • TS-121 4.3.3.2420 • APC Back-UPS ES 700G
Network: VM Hub3: 500/50 • UniFi UDM Pro: 3.2.9 • UniFi Network Controller: 8.0.28
USW-Aggregation: 6.6.61 • US-16-150W: 6.6.61 • 2x USW Mini Flex 2.0.0 • UniFi AC Pro 6.6.62 • UniFi U6-LR 6.6.62
UniFi Protect: 2.11.21/8TB Skyhawk AI • 3x G3 Instants: 4.69.55 • UniFi G3 Flex: 4.69.55 • UniFi G5 Flex: 4.69.55
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.8

Post by fribse »

@Toxic17: Very odd about the changes.zip, I've removed the two files, and added them back in. No I haven't built that in yet. I've tried to build a QPKG, but I must be stupid, because I can't figure out how to make it work :-)
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
OneCD
Guru
Posts: 12010
Joined: Sun Aug 21, 2016 10:48 am
Location: "... there, behind that sofa!"

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

Post by OneCD »

fribse wrote: Sat Jan 05, 2019 7:04 pm I've tried to build a QPKG, but I must be stupid, because I can't figure out how to make it work :-)
Can I help? :geek:

ImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImage
cheffe666
First post
Posts: 1
Joined: Tue Oct 15, 2013 10:36 pm

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

Post by cheffe666 »

Thanks fribse - woohoo finally it works!
-------------------------------------------------- --------------
I got itto work after a few failed attempts on my TS-470 PRO, but had to read almost all of the posts in this thread - hence my brief guide for noobs like me:
1. created a share "backup" with QNAP-shares within my raid6
2. created a database user "nas_backup" with global "Select, Show View and Show Databases" rights
2. copied mysqlbackup.sh untouched to the share and set with WINSCP chmod 700
3. edited mysqlbackup.conf with PSPAD and Format UNIX (LF) - for no carrier return marks in the lines (took me an hour to find the problem :ashamed: )!!!
-> share=backup
-> user=nas_backup
-> pw = **********
->folder=MYSQL
4. copied with WINSCP in etc/conf and set chmod 600
5. went with console "cd /share/backup/" into the directory and tested it with command ./mysqlbackup.sh
6. If an error "/ bin / bash ^ M: bad interpreter: No such file or directory" is reported via the console:
6.1 vi /share/backup/mysqlbackup.conf
6.2: set fileformat = unix
6.3: wq
6.4 back to 5.
7. When the manual backup goes through creating the cronjob through the console
7.1 echo "10 1 * * * /share/backup/mysqlbackup.sh" >> / etc / config / crontab
7.2 crontab / etc / config / crontab
7.2 /etc/init.d/crond.sh restart

Everything should work now
adnanbutt050
New here
Posts: 2
Joined: Fri Apr 05, 2019 8:26 pm

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

Post by adnanbutt050 »

Hi there ,

I am working with the QNAP MySQL backup script, unfortunately, the script is not working as I got this error

Backing up database
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
MySQL Backup: ERROR: creating new backup when trying to access the database




anyone, please explain
I am very thankful if someone can help me.

Kind Regards,
Adnan
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.8

Post by fribse »

@OneCD: You would be more than welcome to help me figure this out :-)

@adnanbutt050: Did you create a user for the database with select rights? Did you create a share for the backup?
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
OneCD
Guru
Posts: 12010
Joined: Sun Aug 21, 2016 10:48 am
Location: "... there, behind that sofa!"

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

Post by OneCD »

@fribse: to get you started, please checkout this generic QPKG template. It can be compiled into a new QPKG, but being a template, won't do anything useful.

Examine the initpackage.sh shell-script file. There's not much to it. It's run by QTS whenever the NAS starts-up and shuts-down and when the user 'starts' or 'stops' a QPKG.

To customise the package, change the values in qpkg.cfg, and edit the package init script.

The package_routines file is used to add extra actions that are only performed when the package is first installed or is about to be removed from the target NAS.

If I was to guess, I'd say you'll need your package to make your dB backup script available for execution and add a new crontab entry - correct?

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

Post by fribse »

Yes, that was my idea. Copy the config file, install the script, and create a crontab entry.
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
OneCD
Guru
Posts: 12010
Joined: Sun Aug 21, 2016 10:48 am
Location: "... there, behind that sofa!"

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

Post by OneCD »

No worries, that’s easy enough to do. :)

Have you picked a package name yet? The internal package name can not contain any whitespace, but the package display name (shown in the App Center) can.

The package init script should also be named after your package. It must be unique, as QTS will create a symlink to it in [/etc/init.d] along with all other installed QPKGs. Your init script name should also be entered in the [qpkg.cfg] file so QTS knows which script filename it should call to start and stop your package.

ImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImage
dehein2
Getting the hang of things
Posts: 51
Joined: Fri Oct 07, 2011 5:32 pm

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

Post by dehein2 »

Hi,

thanks for the great backup script. It's working fine so far but since a few days I'm getting the error:

Code: Select all

Error	2019/09/09	01:10:17	System	127.0.0.1	---	---	MySQL Backup: ERROR compressing backup	
The backup seems to be created fine as far as I can tell. Also I got the following message:

Code: Select all

Error	2019/09/17	01:10:01	System	127.0.0.1	---	---	MySQL Backup: ERROR ereasing old weekly backups
I didn't get any messages before and I didn't change anything realted to mysql or folder permissions.

Any idea where this i comming from?`
Thanks a lot
Dennis
Post Reply

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