[HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8
-
- Easy as a breeze
- Posts: 411
- Joined: Tue Oct 07, 2008 6:30 pm
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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
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)
- 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 databases
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
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
-
- Easy as a breeze
- Posts: 411
- Joined: Tue Oct 07, 2008 6:30 pm
Re: [HOWTO] Make automatic backup of ALL MySQL databases
inside the script itself ....
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
- 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 databases
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
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
-
- Easy as a breeze
- Posts: 411
- Joined: Tue Oct 07, 2008 6:30 pm
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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
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)
- 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 databases
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
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
-
- Starting out
- Posts: 19
- Joined: Sun Aug 30, 2009 7:17 pm
Re: [HOWTO] Make automatic backup of ALL MySQL databases
Well done Job! Thanks.
Techman
My First Qn TS-239 Pro
Teaching is learning - Help others to help yourself
My First Qn TS-239 Pro
Teaching is learning - Help others to help yourself
-
- Starting out
- Posts: 37
- Joined: Fri Oct 26, 2007 10:47 am
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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
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
- 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 databases
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.
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
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
-
- Starting out
- Posts: 37
- Joined: Fri Oct 26, 2007 10:47 am
Re: [HOWTO] Make automatic backup of ALL MySQL databases
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.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.
Maybe its an idea to let the script send an email when there's an error encountered.
Rgrds,
Hellsblade
- 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 databases
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.
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...
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
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
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
- Don
- Guru
- Posts: 12289
- Joined: Thu Jan 03, 2008 4:56 am
- Location: Long Island, New York
Re: [HOWTO] Make automatic backup of ALL MySQL databases
Hi fribse,
Going to give your script a try. Great work BTW.
1 = an error message
2 = a warning message
4 = an informational message
Don
Going to give your script a try. Great work BTW.
Use the following command to write to the system log fileI 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...
Code: Select all
/sbin/write_log "your message here" [1 | 2 | 4]
2 = a warning message
4 = an informational message
Don
Use the forum search feature before posting.
Use RAID and 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.
NAS: TVS-882BR | F/W: 5.0.1.2346 | 40GB | 2 x 1TB M.2 SATA RAID 1 (System/VMs) | 3 x 1TB M.2 NMVe QM2-4P-384A RAID 5 (cache) | 5 x 14TB Exos HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-h674 | F/W: 5.0.1.2376 | 16GB | 3 x 18TB RAID 5
Apps: DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS3, Entware, DLstation, VS, +
Use RAID and 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.
NAS: TVS-882BR | F/W: 5.0.1.2346 | 40GB | 2 x 1TB M.2 SATA RAID 1 (System/VMs) | 3 x 1TB M.2 NMVe QM2-4P-384A RAID 5 (cache) | 5 x 14TB Exos HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-h674 | F/W: 5.0.1.2376 | 16GB | 3 x 18TB RAID 5
Apps: DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS3, Entware, DLstation, VS, +
- Don
- Guru
- Posts: 12289
- Joined: Thu Jan 03, 2008 4:56 am
- Location: Long Island, New York
Re: [HOWTO] Make automatic backup of ALL MySQL databases
Hi
I know you were looking for a way to determine the default volume. This will work.
Don
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 '/'
Use the forum search feature before posting.
Use RAID and 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.
NAS: TVS-882BR | F/W: 5.0.1.2346 | 40GB | 2 x 1TB M.2 SATA RAID 1 (System/VMs) | 3 x 1TB M.2 NMVe QM2-4P-384A RAID 5 (cache) | 5 x 14TB Exos HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-h674 | F/W: 5.0.1.2376 | 16GB | 3 x 18TB RAID 5
Apps: DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS3, Entware, DLstation, VS, +
Use RAID and 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.
NAS: TVS-882BR | F/W: 5.0.1.2346 | 40GB | 2 x 1TB M.2 SATA RAID 1 (System/VMs) | 3 x 1TB M.2 NMVe QM2-4P-384A RAID 5 (cache) | 5 x 14TB Exos HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-h674 | F/W: 5.0.1.2376 | 16GB | 3 x 18TB RAID 5
Apps: DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS3, Entware, DLstation, VS, +
- Don
- Guru
- Posts: 12289
- Joined: Thu Jan 03, 2008 4:56 am
- Location: Long Island, New York
Re: [HOWTO] Make automatic backup of ALL MySQL databases
hi,
Why does the destination have to be a share?
Don
Why does the destination have to be a share?
Don
Use the forum search feature before posting.
Use RAID and 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.
NAS: TVS-882BR | F/W: 5.0.1.2346 | 40GB | 2 x 1TB M.2 SATA RAID 1 (System/VMs) | 3 x 1TB M.2 NMVe QM2-4P-384A RAID 5 (cache) | 5 x 14TB Exos HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-h674 | F/W: 5.0.1.2376 | 16GB | 3 x 18TB RAID 5
Apps: DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS3, Entware, DLstation, VS, +
Use RAID and 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.
NAS: TVS-882BR | F/W: 5.0.1.2346 | 40GB | 2 x 1TB M.2 SATA RAID 1 (System/VMs) | 3 x 1TB M.2 NMVe QM2-4P-384A RAID 5 (cache) | 5 x 14TB Exos HDD RAID 6 (Data) | 1 x Blu-ray
NAS: TVS-h674 | F/W: 5.0.1.2376 | 16GB | 3 x 18TB RAID 5
Apps: DNSMasq, PLEX, iDrive, QVPN, QLMS, MP3fs, HBS3, Entware, DLstation, VS, +
- 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 databases
Hi DonDon wrote:Hi fribse,
Going to give your script a try. Great work BTW.
Use the following command to write to the system log fileI 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...
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
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