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

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

Post by fribse »

This script is no longer maintaned by fribse

I've constructed a script that will do a backup of all the databases in the MySQL / MariaSQL, a thing which is not natively implemented in the firmware, so with a crash or a bad firmware update you risk loosing all your databases.

FEATURES
  • * Each database will be backed up to a seperate file
    * Every day the backup will be tar'ed and compressed to a file, tar archive is placed depending on the week and month day.
    * Backups are put in a daily, weekly or monthly archive
    * Old backups will be removed when they are X days old (you can of course change how many backups you want to keep by changing the value in the configfile, you can adjust both how many daily, weekly and monthly you wish to keep, defaults are 6,5,3).
    * You choose which day you want to keep the weekly backup (default is 1 = monday)
    * When you run the script you should see a bit information on what it's doing.
    * The System log will reflect the script status.
    * Backups can be placed in a subfolder under the share, use the parameter folder in the config.
Requirements
  • * A share set up in QNAP's admin interface (setable in the config)
    * It is possible to have the backup placed under a folder under the share, use the folder= parameter in the config
    * A directory under the above share/folder called mysql (it will be created automatically if it does not exist) this is used for temporary storage of databases.
    * Directories under the above share/folder called mysql.daily, mysql.weekly and mysql.monthly (they will be created automatically if they do not exist)
    * A MySQL user (set in the config) that has global SELECT and global VIEWS rights, and has 'localhost' access, for security, don't use root, but create a new user, default is called backup.
    * The mysqldump command
Loglevel
It appears that not all are on page with the log function, the levels are:
  • 0 Nothing is logged
    1 Errors are logged
    2 Warnings are logged
    3 Informationals are logged
Only messages with priority of this or higher is sent to the system log.

Working on a RAID system or not?
The path changes if you use RAID or not, this is now compensated for in the script.

The MysqlBackup user for the script
The script needs a valid user for accessing the MySQL databases. The user for the script can be set in the configfile.
The user needs 'localhost' access and needs global SELECT and Global SHOW VIEW rights.
The easiest way to set this up, is via phpMyAdmin.
Place the users password in the config file.

The config file is to be placed here: /etc/config/mysqlbackup.conf

How to find the mysqldump command
The script knows a few standard locations for it, if it fails to find the command, please use the command below and send the result to me for addition:

Code: Select all

find / -name mysqldump
This will return the path to the mysqldump.

Backup:
mysqlbackup.sh
mysqlbackup.zip
History
Can now be found in the file
changes.zip
The script can be saved anywhere you like, eg. under /opt/bin (remember to mark it executable with chmod +x mysqlbackup.sh).
NOTE: /opt/bin is only created if you have installed Optware, and if you uninstall optware /opt/bin gets deleted (learned it the hard way :-()

Place the mysqlbackup.sh script on your NAS
First save the file to a share (we call it YOURSHARE)
Then copy it to where you want it to go (YOURPREFERREDLOCATION).
Go to the nas with putty.exe

Code: Select all

# cp /share/YOURSHARE/mysqlbackup/bin/mysqlbackup.sh /YOURPREFERREDLOCATION
# chmod 700 /YOURPREFERREDLOCATION/mysqlbackup.sh
Now the command is implemented.

Place the mysqlbackup.conf config file under /etc/config

Code: Select all

# cp /share/YOURSHARE/mysqlbackup/conf/mysqlbackup.conf /etc/config
# chmod 600 /etc/config/mysqlbackup.conf
The config file is now in place.

Scheduled backup:
If you include the command in cron, you will get a scheduled backup.
To edit the crontab, log in via SSH with putty.exe and start the editor with

Code: Select all

# vi /etc/config/crontab
Insert a line by pressing i

Code: Select all

10 1 * * * /opt/bin/mysqlbackup.sh
Remember, the path has to be the same as you used above.

Press ESC to leave the insert mode

Press :wq
to save the file

Finally make cron aware of the changed crontab

Code: Select all

# crontab /etc/config/crontab
See the new crontab by using

Code: Select all

# crontab -l
That's it, the backup will run every night at 1:10

Restore:

Connect to the box with putty.

cd /share/BACKUPSHARE/mysql(.daily/.weekly/monthly) choose the archive you wish to use
/bin/tar -xvf BACKUPNAME

1) Open up phpMyAdmin
2) Change the database to the one you wish to restore
3) Select Import
4) Select the file you wish to import to the database
5) Press Execute
You do not have the required permissions to view the files attached to this post.
Last edited by fribse on Sun Apr 18, 2021 3:20 pm, edited 41 times in total.
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: 2090
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse »

Oh, as jm45 remarked to me, the setup is made for NAS's with a RAID system (not Q-RAID, but real raid).
If you do NOT have a RAID system, the disk is not called MD0_DATA, but rather HDA_DATA.

Thanks jm45!
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
lentzit
Been there, done that
Posts: 518
Joined: Thu Dec 27, 2007 7:32 pm
Location: Sweden
Contact:

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

Post by lentzit »

And for the Windows guys....download the admintool for MySQL (for Windows) -http://dev.mysql.com/downloads/gui-tools/5.0.html, install it and connect to your qnap device. You need to enable TCP/IP Networking for remote access.
my-sql.png
I also created another user via phpMyAdmin that is used for remote access, the default root user has only access locally.
my-sql-phpadmin.png
With the GUI you can show all content on the qnap SQL and also schedule backups.
You do not have the required permissions to view the files attached to this post.
-TVS-663/16GB QTS 4.2 , RAID5
-QGenie QG-103N - Wireless Storage in Classroom Environment
-Services: WEB | Virtual Station | QSYNC | FTP | SSL | Private Cloud | Photo Station
-Using it for my blog (http://blog.lentzit.com)
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 databases

Post by fribse »

Just to be clear, the backup user does NOT need all privileges.
Only select is necessary, and of course the right must be global.
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
mr_hyde
Easy as a breeze
Posts: 409
Joined: Sun Sep 02, 2007 3:51 pm
Location: Germany

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

Post by mr_hyde »

Hi,

seems to work great.

Nevertheless, i had to make some changes in the script:

Line 2: remove the second 'find'
change: /bin/sed '1d' raw > /tmp/list to /bin/sed '1d' /tmp/raw > /tmp/list


I will see, if the cronjob will be executed
QNAP TS-453A
4 * Hitachi 3 TB HDD (RAID 5)
FW: 4.5.1 1540
hornetbzz
Getting the hang of things
Posts: 97
Joined: Tue Apr 07, 2009 8:10 am

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

Post by hornetbzz »

Thanks again Frisbee, it works well.

I'm looking as well to run a php script, and there I'm facing difficulties.

It works using a wget as follow for example :

Code: Select all

*/1 * * * * wget http://localhost/cron/test_cron.php
but I can't call for a php script from the crontab as I guess there is not php client installed in the NAS php version. So I'm not able to apply the follwing to run my php script

Code: Select all

My php script :
#!/usr/bin/my_php_client_somewhere
<?php ... my script ?>

my crontab line :
*/1 * * * * php /share/MD0_DATA/Qweb/my_domain/cron/test_cron.php

Would you pls have any hints in order to avoid the use of "wget", like downloading a php client for insistance ?

Thx
The Force to be with you !!
--------------------------------------
TS509 Pro (2.1.5 Build 0408T), Apache/2.2.6 (Unix) DAV/2 mod_ssl/2.2.6 OpenSSL/0.9.8e , PHP 5.2.9, 5 x 1.5 To (RAID5), 1 nux Firewall, 1 Netgear GS724T, 3 other machines (Debian)
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 »

Hi,
Once backup are well done with the shell script,
what kind of commands should we have to prepare in case of restore needed ?
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
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 databases

Post by fribse »

mr_hyde wrote:Hi,

seems to work great.

Nevertheless, i had to make some changes in the script:
Line 2: remove the second 'find'
change: /bin/sed '1d' raw > /tmp/list to /bin/sed '1d' /tmp/raw > /tmp/list
I will see, if the cronjob will be executed
Thanks, mistypings, when moving / editing the script.
I've changed the script accordingly.
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: 2090
Joined: Mon Feb 11, 2008 2:50 am
Location: Greve, Denmark
Contact:

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

Post by fribse »

hornetbzz wrote:Thanks again Frisbee, it works well.

I'm looking as well to run a php script, and there I'm facing difficulties.

It works using a wget as follow for example :

Code: Select all

*/1 * * * * wget http://localhost/cron/test_cron.php
You need to put quotes around the URL, ie:

Code: Select all

*/1 * * * * wget "http://localhost/cron/test_cron.php"
Then it'll 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
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

Post by fribse »

jm45 wrote:Hi,
Once backup are well done with the shell script,
what kind of commands should we have to prepare in case of restore needed ?
Good point, I'll add a paragraph to the script about restore...
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
hornetbzz
Getting the hang of things
Posts: 97
Joined: Tue Apr 07, 2009 8:10 am

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

Post by hornetbzz »

Thx Frisbee,


Actually it works (note: it works also w/o quotes in my case) using :

Code: Select all

*/1 * * * * wget "http://localhost/cron/test_cron.php"

My question is :

is there any more elegant solution, calling a php script using the PHP-CLI, so also avoiding a local rewriting of the crontab file ?


Thx
The Force to be with you !!
--------------------------------------
TS509 Pro (2.1.5 Build 0408T), Apache/2.2.6 (Unix) DAV/2 mod_ssl/2.2.6 OpenSSL/0.9.8e , PHP 5.2.9, 5 x 1.5 To (RAID5), 1 nux Firewall, 1 Netgear GS724T, 3 other machines (Debian)
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 databases

Post by fribse »

Not as I can see it, if you need something done on a timely matter, cron is the way.
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 »

feedback about the tries:
1)

0 databases backed up

==> no backup done:
done < tmp/list
should be: done < /tmp/list (missing a /)

2) then after the done < /tmp/list change

[~] # mysqlbackup.sh
Removing backups older than 7 days
Compressing old backup
/bin/tar: Removing leading `/' from member names
/share/Qbackup/mysql/
Backing up MySQL database mysql
/usr/bin/mysqlbackup.sh: line 22: /opt/bin/mysqldump: No such file or directory
Backing up MySQL database test
/usr/bin/mysqlbackup.sh: line 22: /opt/bin/mysqldump: No such file or directory
Backing up MySQL database forum
/usr/bin/mysqlbackup.sh: line 22: /opt/bin/mysqldump: No such file or directory
3 databases backed up

backup file.sql are there now but O byte and nothing in it !

3) i am a bit lost with such a message " No such file or directory"
there is nothing in my /opt/bin folder but /opt/nasconfig_fs.img.tgz
and
[~] # find / -name mysqldump
/mnt/HDA_ROOT/mysql/bin/mysqldump

so i have something to change yet...
and now after the last changes:
[~] # mysqlbackup.sh
Removing backups older than 7 days
Compressing old backup
/bin/tar: Removing leading `/' from member names
/share/Qbackup/mysql/
/share/Qbackup/mysql/mysql.sql
/share/Qbackup/mysql/test.sql
/share/Qbackup/mysql/forum.sql
Backing up MySQL database mysql
Backing up MySQL database test
Backing up MySQL database forum
3 databases backed up
[~] #

then backup are well done :)
TS-109 II firmware version: 3.1.0 Build 0708T (1TB disk)
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 databases

Post by fribse »

Well, apparently the mysqldump is placed elsewhere on the ts-109, I've only tested it on my ts-639.
If it's empty anyways, you could solve it by doing a

Code: Select all

ln -s /mnt/HDA_ROOT/mysql /opt
Good find, 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
hornetbzz
Getting the hang of things
Posts: 97
Joined: Tue Apr 07, 2009 8:10 am

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

Post by hornetbzz »

Hi Frisbee,

So now I understood now that you can use "cron" to run scripts, by 2 different ways, both working :

- 1st way as you recommended: to use "wget" in the crontab file, that's the easy way, but not enough clean for my needs,
for example : */1 * * * * wget "http://localhost/cron/test_cron.php"

- 2nd way as I was looking for in my previous posts above: to install a php client (with ipkg), being able to run php script in command line, and use this command line in the crontab. In summary, what I have done to get it working :

SSH to the NAS
ipkg update
ipkg install php
php -v (to check the ipkg php client is working)
php /yourpathto/yourscript.php (also to check your script is working when running it through the php CLI, before using it in the crontab)
edit crontab (in my RAID5 case: /mnt/HDA_ROOT/.config/crontab) :
Replace
by
*/1 * * * * php /my_full_path/test_cron.php (if you are not root connected, you might have to specify the full path to the php cli

Here we are !
The Force to be with you !!
--------------------------------------
TS509 Pro (2.1.5 Build 0408T), Apache/2.2.6 (Unix) DAV/2 mod_ssl/2.2.6 OpenSSL/0.9.8e , PHP 5.2.9, 5 x 1.5 To (RAID5), 1 nux Firewall, 1 Netgear GS724T, 3 other machines (Debian)
Post Reply

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