[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: 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 Jan 02, 2017 8:47 pm

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 » Thu Jan 12, 2017 12:54 am

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: 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 Jan 12, 2017 2:10 pm

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 » Mon Jan 16, 2017 7:36 pm

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 » Thu Feb 02, 2017 9:59 am

Hi, is it also support QTS 4.3.2 ?

User avatar
Toxic17
Ask me anything
Posts: 5112
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 » Sat Feb 11, 2017 6:36 am

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

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.3.6.0993 • TVS-463-16GB 4.3.6.0993 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 •

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 » Fri May 26, 2017 8:31 pm

Brilliant post. Thank you for the work.

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 Jun 05, 2017 11:55 pm

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: 79
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 » Sat Jul 01, 2017 9:52 pm

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/ba477978451ff75febd9a652058ac4a920170701135724/3669b1ba1f4f0bd32bb637e3fabec14820170701135724/214048?utm_campaign=WT_email_tracking&utm_content=general&utm_medium=download_button&utm_source=notify_recipient_email

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 » Sat Jul 01, 2017 11:19 pm

@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: 79
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 » Sun Jul 02, 2017 4:56 pm

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: 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 » Sun Jul 02, 2017 6:07 pm

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 » Thu Jul 06, 2017 10:16 am

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: 5112
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 » Wed Aug 16, 2017 3:02 am

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

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.3.6.0993 • TVS-463-16GB 4.3.6.0993 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 •

trendchiller
Easy as a breeze
Posts: 303
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 » Wed Aug 16, 2017 5:21 am

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

Post Reply

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