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.
- * 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
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
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
Backup:
mysqlbackup.sh History
Can now be found in the file 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
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
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
Code: Select all
10 1 * * * /opt/bin/mysqlbackup.sh
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
Code: Select all
# crontab -l
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