[HOWTO] Make automatic backup of ALL MySQL databases

[fribse]

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

FEATURES

* Each database will be backed up to a seperate file
* Every day the backup from the day before will be tar'ed and compressed to a file, before a new backup is made. This way, the latest backup is easily available, and you can go as far back as you have allowed.
* 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, currently set to 15).
*When you run the script you should see a bit information on what it's doing.
*The System log will reflect the script status.

Requirements

* A share called Qbackup (set in the script via variable, can be changed to any other share)
* A directory under the above share called mysql (it will be created automatically if it's not there)
* A directory under the above share called oldmysql (it will be created automatically if it's not there)
* A MySQL user that has global SELECT rights, and has 'localhost' access, for security, don't use root, but create a new user, here called backup.
* The mysqldump command

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 rights. The easiest way to set this up, is via phpMyAdmin
Place the users password in the config file /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
The script is now of a size that makes it easier to download, so it has been attached to this post.

mysqlbackup.zip

EDIT: Please note that the FIND statement refers to MD0_DATA, this is for systems with RAID set up (not Q-RAID, but real RAID). If you do not have RAID set up, the path is called HDA_DATA.
EDIT 2: Script fixed, mistypings
EDIT 3: Script fixed, mistypings (missing /)
EDIT 4: Cleaned up code, introduced variables for setup, used AdNovea's hint with the AWK command
EDIT 5: Cleaned up code, got rid of the last temporary file
EDIT 6: Error handling, and password moved to seperate file
EDIT 7: More error handling, the script finds the path and commands automagically. The archive name has been altered to have the year first!!!
EDIT 8: Restore procedure is corrected
EDIT 9: Better error handling, more compact script
EDIT 1.9: More error handling, corrected for TS-219 without RAID
EDIT 2.0: Whoops forgot some error checking on the folder creation
EDIT 2.1: Password file moved to /etc/config so it won't be removed at a reboot
EDIT 2.2: System log is now being updated
EDIT 2.3: Changed the way the databases are listed, this is more correct, and works on firmwares 3.+
EDIT 2.4: Minor bug fix, notification command was misspelled
EDIT 2.5: Internal testing version, not released
EDIT 2.6: Config moved to seperate file, logging level can now be set to levels between 0 (nothing) and 2 (verbose) bug fix so that create database is now included in the backup, license now included
EDIT 2.7: Mysql stored procedures are now backed up (if you use such things), thankyou for the tip on this!
EDIT 2.8: Error checking on stored procedure command added, as some has very old mysqls running even on new versions of the OS

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 )

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.

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.
You can either edit the crontab manually, or you use the excellent QPKG for editing crontab via webinterface.
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:

There are two situations for restore.

1) You need to restore a database from the day before
2) You need to restore a database from previously.

Restore from day before
Do a simple import of the database.

1) Open up phpMyAdmin
2) Change the database to the one you wish to restore (it's on the backupshare, under mysql).
3) Select Import
4) Select the file you wish to import to the database
5) Press Execute
DONE!

Restore from earlier backups

Connect to the box with putty.

cd /share/BACKUPSHARE/oldmysql
/bin/tar -xvf BACKUPNAME

The rest of the guide is more or less like above:
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

Last step is a bit of cleanup:

Code: Select all

cd /share/Qbackup/oldmysql

DANGER! MAKE SURE THAT YOU ARE IN THE CORRECT SUBDIR!
With this step you delete the folder you just uncompressed.

Code: Select all

rm -R BACKUPNAME

DONE!

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

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

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

[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

[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

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

[fribse]

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.

[fribse]

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.

[fribse]

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...

[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

[fribse]

Not as I can see it, if you need something done on a timely matter, cron is the way.

[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

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

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