Page 1 of 25

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

Posted: Thu Jun 25, 2009 10:45 pm
by fribse
SCRIPT UPDATED to v3.8

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

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

Posted: Fri Jun 26, 2009 5:59 pm
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!

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

Posted: Fri Jun 26, 2009 8:08 pm
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.

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

Posted: Fri Jun 26, 2009 10:36 pm
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.

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

Posted: Tue Jun 30, 2009 3:50 am
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

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

Posted: Tue Jun 30, 2009 12:10 pm
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

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

Posted: Tue Jun 30, 2009 2:43 pm
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 ?

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

Posted: Tue Jun 30, 2009 3:00 pm
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.

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

Posted: Tue Jun 30, 2009 3:01 pm
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.

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

Posted: Tue Jun 30, 2009 3:03 pm
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...

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

Posted: Tue Jun 30, 2009 7:21 pm
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

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

Posted: Tue Jun 30, 2009 9:20 pm
by fribse
Not as I can see it, if you need something done on a timely matter, cron is the way.

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

Posted: Tue Jun 30, 2009 10:12 pm
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 :)

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

Posted: Tue Jun 30, 2009 11:40 pm
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!

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

Posted: Thu Jul 02, 2009 7:23 am
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
*/1 * * * * wget "http://localhost/cron/test_cron.php"

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 !