[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 » Sat Jul 21, 2018 2:31 am

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: 2076
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 » Thu Aug 16, 2018 8:14 pm

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: 6
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 » Thu Sep 13, 2018 1:54 am

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: 2076
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 » Mon Sep 24, 2018 1:39 pm

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: 2076
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 » Mon Sep 24, 2018 1:40 pm

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: 5158
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 » Sat Jan 05, 2019 5:37 am

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

QTS 4.x User Guidex

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


NAS: TS-473-32GB QM2-2P QXG-10G1T 4.4.1.1064 • TVS-463-16GB 4.4.1.1064 QM2-2S10G1TB • TS-459 Pro 2GB 4.2.6 • TS-121 4.3.3.0998 • APC Back-UPS ES 700G •
QPKG's: TwonkyServer 8.51 • Apache73 • QSonarr 3.0.1.503 • QNBZGet 21.0 • phpMyAdmin 4.9.0.1 • Qmono 5.20.1.19 • McAfee 2.2.1 • Lychee 3.2.15 • HBS 3.0.190802 • LEgo v3.0.0
Network: VM Hub 3.0 <500/35> • UniFi USG Pro 4 • UniFi USW-16-150W • UniFi USW-8-60W • UniFi CloudKey Gen2+• UniFi G3-Flex • UAP AC Pro • UAP AC Lite • SLM2008 • Dell 7050 MFF •

User avatar
fribse
Experience counts
Posts: 2076
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 » Sat Jan 05, 2019 7:04 pm

@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
Ask me anything
Posts: 6245
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 » Sun Jan 06, 2019 6:48 am

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:

production NAS: TS-569 Pro with Debian 9.9 'Stretch' (power on/off times are < 1 minute)
backup NAS: TS-559 Pro+ with QTS 4.2.6 #20190730

one.cd.only@gmail.com

Image Image Image Image

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 » Tue Jan 22, 2019 11:16 pm

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 » Fri Apr 12, 2019 6:24 pm

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: 2076
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 » Thu Apr 18, 2019 6:23 am

@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
Ask me anything
Posts: 6245
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 » Thu Apr 18, 2019 6:49 am

@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?

production NAS: TS-569 Pro with Debian 9.9 'Stretch' (power on/off times are < 1 minute)
backup NAS: TS-559 Pro+ with QTS 4.2.6 #20190730

one.cd.only@gmail.com

Image Image Image Image

User avatar
fribse
Experience counts
Posts: 2076
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 » Fri Apr 19, 2019 3:49 pm

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
Ask me anything
Posts: 6245
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 » Fri Apr 19, 2019 4:18 pm

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.

production NAS: TS-569 Pro with Debian 9.9 'Stretch' (power on/off times are < 1 minute)
backup NAS: TS-559 Pro+ with QTS 4.2.6 #20190730

one.cd.only@gmail.com

Image Image Image Image

dehein2
Starting out
Posts: 32
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 » Tue Sep 17, 2019 10:10 pm

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)”