[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
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 Sep 21, 2009 4:54 pm

Hi,
after NAS reboot is done, backuppass file is no more in my /etc
is it on purpose we have to re-create it?
context: TS-109 II firmware version: 3.1.0 Build 0708T
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

User avatar
fribse
Experience counts
Posts: 2077
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 Sep 21, 2009 8:05 pm

Hmm, no. Darned, now where should we put it?
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 Sep 22, 2009 5:17 am

inside the script itself .... 8)
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

User avatar
fribse
Experience counts
Posts: 2077
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 Sep 22, 2009 6:57 pm

Move the password file to /etc/config, I'll update the script, and the instructions...
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 23, 2009 2:04 am

Hi,
new 2.1 script tested ok; "Backup Successfull"
(but i have not still done the reboot test to check if backuppass is kept)

just a remark about old comment remaining:
MYSQL_USER="backup" # User for accessing the databases, password stored in the file /etc/backuppass
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)

User avatar
fribse
Experience counts
Posts: 2077
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 23, 2009 4:47 am

Ok, fixed :-) Didn't do a new version, just changed the comment. Thanks.
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

techman
Starting out
Posts: 19
Joined: Sun Aug 30, 2009 7:17 pm

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

Post by techman » Fri Sep 25, 2009 3:19 pm

Well done Job! Thanks.
Techman
My First Qn TS-239 Pro
Teaching is learning - Help others to help yourself

hellsblade
Starting out
Posts: 37
Joined: Fri Oct 26, 2007 10:47 am

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

Post by hellsblade » Wed Sep 30, 2009 2:09 am

Great script. At last. This should be integrated in the admin interface by default. Qnap guys... do the right thing.
I have a question also. For some reason the script claims there is an arc database, but it doesn't exist.
When i look with mysql admin there its not there. Any idea?

mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'arc' when selecting the database
ERROR creating new backup when trying to access the database arc

Rgrds,
hellsblade

User avatar
fribse
Experience counts
Posts: 2077
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 Oct 01, 2009 3:30 am

Thankyou for the kind words, nice to know that somebody else than me, and a few select users, benefits of it.

You have a directory under the mysql database directory called arc.
There should only be databases under the mysql database directory!
If you've made a backup or archive and put it in a directory there, move it elsewhere.
It doesn't use a list of databases, it just looks for the available directories.
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

hellsblade
Starting out
Posts: 37
Joined: Fri Oct 26, 2007 10:47 am

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

Post by hellsblade » Fri Oct 02, 2009 6:25 am

fribse wrote:Thankyou for the kind words, nice to know that somebody else than me, and a few select users, benefits of it.

You have a directory under the mysql database directory called arc.
There should only be databases under the mysql database directory!
If you've made a backup or archive and put it in a directory there, move it elsewhere.
It doesn't use a list of databases, it just looks for the available directories.


There is indeed an arc directory in one of the database directories (PHPBB). It seems it has something to do with this bug. http://bugs.mysql.com/bug.php?id=17823, because i never put i there. I moved it out and i hope it does'nt come back.

Maybe its an idea to let the script send an email when there's an error encountered.

Rgrds,
Hellsblade

User avatar
fribse
Experience counts
Posts: 2077
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 Oct 05, 2009 2:18 am

I'm looking into getting the list of databases from the mysql command, and I've so far found a way, but for some reason, the while loop fails, hmmm.

Code: Select all

# Listing all the databases

echo 'show databases;' | mysql -u username--password=password | sed '1d' | while read LINE
do
  echo "Backing up database $LINE to /mysql/$LINE.sql"
done


Anybody that can tell me what I'm doing wrong here?

I would love to be able to send a message to the log of the server, but I'm not aware of how to do that...
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
Don
Guru
Posts: 11986
Joined: Thu Jan 03, 2008 4:56 am
Location: Long Island, New York

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

Post by Don » Wed Oct 21, 2009 12:25 am

Hi fribse,

Going to give your script a try. Great work BTW.

I would love to be able to send a message to the log of the server, but I'm not aware of how to do that...

Use the following command to write to the system log file

Code: Select all

/sbin/write_log "your message here" [1 | 2 | 4]

1 = an error message
2 = a warning message
4 = an informational message

Don
Read the Online Manuals and use the forum search feature before posting.

It is a recommended to use RAID and have external backups. RAID will protect you from disk failure, keep your system running, and data accessible while the disk is replaced and the RAID rebuilt. Backups will allow you to recover data that is lost or corrupted, or from system failure. One does not replace the other.

Submit bugs and feature requests to QNAP via their Helpdesk app.

NAS: TVS-882BR | F/W: 4.3.6.0895 | 40GB | 2 x M.2 SATA RAID 1 (System/VMs) | 4 x M.2 NMVe QM2-4P-384A RAID 5 (Cache) | 5 x 4TB HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-663 | F/W: 4.4.1.1086 | 16GB | 2 x M.2 NMVe QM2-2P RAID 1 (Cache) | 4 x 4TB RAID 5
Apps: Boinc, Squid, DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS, Entware, DLstation, +others

User avatar
Don
Guru
Posts: 11986
Joined: Thu Jan 03, 2008 4:56 am
Location: Long Island, New York

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

Post by Don » Wed Oct 21, 2009 12:34 am

Hi

I know you were looking for a way to determine the default volume. This will work.

Code: Select all

/sbin/getcfg Public path -f /etc/config/smb.conf -u | cut -f 3 -d '/'

Don
Read the Online Manuals and use the forum search feature before posting.

It is a recommended to use RAID and have external backups. RAID will protect you from disk failure, keep your system running, and data accessible while the disk is replaced and the RAID rebuilt. Backups will allow you to recover data that is lost or corrupted, or from system failure. One does not replace the other.

Submit bugs and feature requests to QNAP via their Helpdesk app.

NAS: TVS-882BR | F/W: 4.3.6.0895 | 40GB | 2 x M.2 SATA RAID 1 (System/VMs) | 4 x M.2 NMVe QM2-4P-384A RAID 5 (Cache) | 5 x 4TB HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-663 | F/W: 4.4.1.1086 | 16GB | 2 x M.2 NMVe QM2-2P RAID 1 (Cache) | 4 x 4TB RAID 5
Apps: Boinc, Squid, DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS, Entware, DLstation, +others

User avatar
Don
Guru
Posts: 11986
Joined: Thu Jan 03, 2008 4:56 am
Location: Long Island, New York

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

Post by Don » Wed Oct 21, 2009 12:43 am

hi,

Why does the destination have to be a share?

Don
Read the Online Manuals and use the forum search feature before posting.

It is a recommended to use RAID and have external backups. RAID will protect you from disk failure, keep your system running, and data accessible while the disk is replaced and the RAID rebuilt. Backups will allow you to recover data that is lost or corrupted, or from system failure. One does not replace the other.

Submit bugs and feature requests to QNAP via their Helpdesk app.

NAS: TVS-882BR | F/W: 4.3.6.0895 | 40GB | 2 x M.2 SATA RAID 1 (System/VMs) | 4 x M.2 NMVe QM2-4P-384A RAID 5 (Cache) | 5 x 4TB HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-663 | F/W: 4.4.1.1086 | 16GB | 2 x M.2 NMVe QM2-2P RAID 1 (Cache) | 4 x 4TB RAID 5
Apps: Boinc, Squid, DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS, Entware, DLstation, +others

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

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

Post by fribse » Sun Oct 25, 2009 4:13 am

Don wrote:Hi fribse,

Going to give your script a try. Great work BTW.

I would love to be able to send a message to the log of the server, but I'm not aware of how to do that...

Use the following command to write to the system log file


Hi Don

Excellent, just what I was looking for, I'll be incorporating it right away :-)

Thanks
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

Post Reply

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