[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
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 »

Sorry, didn't see the post until now, what's the error?
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
Kaizo
New here
Posts: 8
Joined: Mon Dec 12, 2016 2:21 pm

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

Post by Kaizo »

hello

Nice works.

I try to install the script with mariadb on docker.
I use mariadb on docker and not the native one because of sphinxsearch. Sphinx works well when I link the mariadb docker with sphinx but impossible to make him walk with mariadb in native.
I tried to put the script in the container but without success.
mariadb is on port 10.0.3.2 instead localhost

Do you have any idea to do that? 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 »

No, sorry. It uses the my.cnf as it just uses the standard mysql commands, so if the docker has a proper my.cnf I don't see why it shouldn't 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
Kaizo
New here
Posts: 8
Joined: Mon Dec 12, 2016 2:21 pm

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

Post by Kaizo »

fribse wrote:No, sorry. It uses the my.cnf as it just uses the standard mysql commands, so if the docker has a proper my.cnf I don't see why it shouldn't work?
thanks
il will try on container
tompub2007
New here
Posts: 4
Joined: Fri Dec 09, 2011 5:00 pm

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

Post by tompub2007 »

Hi, is it also support QTS 4.3.2 ?
User avatar
Toxic17
Ask me anything
Posts: 6469
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.7

Post by Toxic17 »

tompub2007 wrote:Hi, is it also support QTS 4.3.2 ?
works here ok.

though did add a couple more crontab entries just to repair and optimize the DB's before backing up.

Code: Select all

# Backup Database
0 1 * * * /mnt/ext/opt/mariadb/bin/mysqlcheck --auto-repair --all-databases -ppassword 2>/dev/null
5 1 * * * /mnt/ext/opt/mariadb/bin/mysqlcheck -o --all-databases -ppassword 2>/dev/null
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
Spacebrain
New here
Posts: 4
Joined: Sun Aug 11, 2013 5:42 pm
Location: Randers, Denmark

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

Post by Spacebrain »

Brilliant post. Thank you for the work.
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 »

Good idea, cleanup and optimization is always great. Though using the password directly in the cron is not my favourite :-)
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
Rene72
Getting the hang of things
Posts: 94
Joined: Thu Apr 23, 2015 3:36 pm

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

Post by Rene72 »

I have been busy to get the script to work but seems to run into the error of crontab creation and backup script manual run only create folders but no backup

I did screenshot every step I did, first I must get it working after that backup user settings and other settings will be more secured.

Due to board restrictions I uploaded it to Wetransfer. The link is active for 7 days, click on link and Agree then the .pdf will be available for download I hope someone can help me.

https://wetransfer.com/downloads/ba4779 ... ient_email
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 »

@Rene72: A few comments.
You should be aware of case sensitivity on user and password for mysql. It complains that it can't log in in the script.
The backup user only needs 'select' and 'view' rights, but need them globally.
Did you try to log in to the database with the credentials used? eg. on the console?
The cron problem is not something that I will not delve into, it's not a problem with the script, but I would try and place the mysqlbackup.sh script somewhere else, like in the backup share or something like that, and see if cron can work from there.
You don't have mysql installed in a docker or the likes, do you?
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
Rene72
Getting the hang of things
Posts: 94
Joined: Thu Apr 23, 2015 3:36 pm

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

Post by Rene72 »

no I use the sql on qnap not in docker. Is that a better way to use the SQL ?
I will continue to look for solution of the account thanks for the help.
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 »

No, if you use docker you MIGHT run into a whole range of other problems, so just making sure :-)
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
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 »

I'm trying to figure out if I have any MySQL databases that need backing up. As far as I know I don't. I haven't done anything with them. I don't really even understand them.

I mainly use Sonarr, CouchPotato, Plex Media Server, Sabnzbd, Syncthing. Do I have any databases of this sort?
User avatar
Toxic17
Ask me anything
Posts: 6469
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.7

Post by Toxic17 »

fribse wrote:Good idea, cleanup and optimization is always great. Though using the password directly in the cron is not my favourite :-)
Perhaps it could be built into the Backup 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
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 »

@Iceman24:
Does not seem you have any databases ;-)
Post Reply

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