Содержание
SCRIPT UPDATED to v3.1
Version 3 is here, as per request, it now handles daily, weekly and monthly archives.
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.
- 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 0 = sunday)
- 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 in the config)
- A directory under the above share called mysql (it will be created automatically if it does not exist)
- Directories under the above share 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
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:
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.
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
# 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
# cp /share/YOURSHARE/mysqlbackup/conf/mysqlbackup.conf /etc/config</div> # 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
# vi /etc/config/crontab
Insert a line by pressing i
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
# crontab /etc/config/crontab
See the new crontab by using
# 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/mysql(.daily/.weekly/monthly) choose the archive you wish to use
/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