[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: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Mon Aug 24, 2009 7:57 pm

The find command can take quite a lot of time, so it's not viable to run it every time the backup is to be done.
But there might be another way, look in your pm
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

jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by jm45 » Mon Aug 24, 2009 9:04 pm

ok, i should have thought about the time needed at every launch :oops:
PM answered
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

User avatar
fribse
Experience counts
Posts: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Tue Aug 25, 2009 1:12 am

Hi jm45

Thanks for the feedback, it is much appreciated, the script is updated again, please note the change in the archive name of old backups.
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

jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by jm45 » Tue Aug 25, 2009 1:30 am

everything is fine, nothing to change in the script,

every paths are well found.
nice job
:D
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by jm45 » Tue Aug 25, 2009 2:01 am

now:
once the backup is done, i get files.sql as
(example with the database of my forum )

/share/HDA_DATA/Qbackup/mysql/forum.sql

then what to do with this file if i wish to restore ?

a cp should no fit ...

hence /share/HDA_DATA/.@mysql/forum/ contains a lot of files like x.frm x.MYD x.MYI
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

User avatar
fribse
Experience counts
Posts: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Tue Aug 25, 2009 3:11 am

You are absolutely right.
The restore procedure was a relick of previous methods to do backup.
You import the database via phpMyAdmin, the procedure has been updated.
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

jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by jm45 » Tue Aug 25, 2009 3:23 am

brillant! fribse.
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

User avatar
fribse
Experience counts
Posts: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Wed Aug 26, 2009 6:16 pm

I updated the script to a bit better error handling, and also cleaned up the code a bit.
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

jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by jm45 » Wed Aug 26, 2009 6:26 pm

latest v1.8 tested,
all working fine :D

just a cosmetic about output log:
maybe it would be usefull to change text, and remind the full path for oldmysql folder
changing:
"Compressing old backup to 090826.tar.gz"
..
"Done compressing old backup"
as
Compressing previous backup to /share/HDA_DATA/Qbackup/oldmysql/090826.tar.gz
..
Done compressing previous backup

-------------------------------------------------------------------------------------
Cleaning out old backups. Keeping the last 15 backups

Compressing old backup to 090826.tar.gz

/share/HDA_DATA/Qbackup/mysql/
/share/HDA_DATA/Qbackup/mysql/mysql.sql
/share/HDA_DATA/Qbackup/mysql/test.sql
/share/HDA_DATA/Qbackup/mysql/forum.sql

Done compressing old backup

Backing up current databases

Backing up MySQL database mysql
Backing up MySQL database test
Backing up MySQL database forum

All databases backed up


#
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

bramschats
Easy as a breeze
Posts: 433
Joined: Thu Apr 23, 2009 1:51 am

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by bramschats » Wed Sep 09, 2009 10:35 pm

Hello,

I have installed the script as discribed but since it all is nearly out of my league i have a few questions.
1. how can manually start the script to see if the backup is working?
2. how can i see in the eventlog or something if everything is backed up correctly?

I use wordpress and i need to backup the databases!

Hope someone can help me with this one.
Qnap TS-459pro II | Qnap TS-239pro | HDI Dune prime 3.0 |

User avatar
fribse
Experience counts
Posts: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Wed Sep 09, 2009 11:04 pm

No entry in the eventlog.

But you can run it from a SSH session, like any other command.

Log in with Putty username admin and your admin password.
type the full path, and commandname (including .sh), and it will show what happens on the screen, and also give errors out.
If you see .sql files in the share you have chosen to backup to (under the mysql folder), then the backup is running.
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

bramschats
Easy as a breeze
Posts: 433
Joined: Thu Apr 23, 2009 1:51 am

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by bramschats » Thu Sep 10, 2009 1:00 am

What is wrong if i am getting the following message.


I think i have done everything right.

Code: Select all

[~] # /opt/bin/mysqlbackup.sh
ERROR mysqldump command not found. Please use the command 'find / -name mysqldump' and send the result to the author


Code: Select all

[~] # find / -name mysqldump
/mnt/ext/opt/mysql/bin/mysqldump


what do i have to do to solve this.....

One other thing i do not understand:
"Create the mysqlbackup.sh file with pspad.exe on one of your normal shares"

thanks in advance.
Qnap TS-459pro II | Qnap TS-239pro | HDI Dune prime 3.0 |

User avatar
fribse
Experience counts
Posts: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Thu Sep 10, 2009 1:44 am

Answered in PM
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: 2079
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by fribse » Wed Sep 16, 2009 2:05 pm

The script was updated to take care of the TS-219 without raid, and it will also create the dir's under the share itself now, including some error checking.
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

jm45
Easy as a breeze
Posts: 411
Joined: Tue Oct 07, 2008 6:30 pm

Re: [HOWTO] Make automatic backup of ALL MySQL databases

Post by jm45 » Wed Sep 16, 2009 4:41 pm

Hi,
V2.0 tested ok
on my TS-109 II firmware version: 3.1.0 Build 0708T

Backup Successfull :)
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

Post Reply

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