SCRIPT UPDATED to v2.2 [NOTICE: PRESS THE LINK ABOVE TO FIND A NEWER UPDATED SCRIPT]
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 script, 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 rights to all the databases, 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 is in this script called backup.
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 a text file called backuppass that ONLY contains the password for the user (no newline or anything, just the password!), and place it in /etc/config

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

Unzip package before upload to NAS
Password moved to /etc/config/backuppass

(1.55 KiB) Downloaded 131 times

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

The script can be saved anywhere you like, eg. under /opt/bin (remember to mark it executable with chmod +x mysqlbackup.sh.

Go to the nas with putty.exe

Code: Select all

# cp /share/YOURSHARE/mysqlbackup.sh /YOURPREFERREDLOCATION
# chmod 777 /YOURPREFERREDLOCATION/mysqlbackup.sh


Now the command is implemented.

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
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/Qbackup/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!

Code: Select all

rm -R share

DONE!