Backing Up a Remote MYSQL Database with a Linux based QNAP 109 Network Attached Storage
Introduction
Using a QNAP linux NAS you can backup MYSQL databases on remote servers. This article shows you how to write a script to do this. This script can then be run at set intervals using the cron scheduler to ensure your mysql data server is backed up safely.
This guide assumes your server is running the SSHD and allows you to run remote commands. For example a VPS server is the perfect example of this.
Step 1
Setup your qnap to be able to run secure remote commands on your remote server. This article describes how to do this Password Login remotely with QNAP
Step 2
Create a shell script on your disk file system. I called mine databasebackup.sh and I save it under /share/HDA_DATA/dave/
#!/bin/sh
#This variable grabs the date. This is can be appended to your backup file name so you can keep archives of your data.
BACKUPDATE=`date +%Y-%m-%d`
#These are variables which you set depending on your system.
#The path to where you will create the tar backup on the remote server
BACKUPDIR=/home/dave
#The name of the database you wish to backup
DBNAME=myimportantdatabase
#This is the name database which has backup rights to the remote database. Hopefully you aren't using root!
DBUSER=myimportantdatabaseuser
#This is the password of the database user.
DBPASSWORD='Passw0rd'
#This is the path of the database backup. The database dump.
MySQLFILE=$BACKUPDIR/$BACKUPDATE.$DBNAME.sql.gz
#Run Backup Commands
#Run a secure remote command for mysqldump
ssh dave@www.myserver.co.uk "mysqldump -u$DBUSER -p$DBPASSWORD $DBNAME | gzip > $MySQLFILE"
#Copy the database dump backup to the qnaps local files system
scp dave@www.myserver.co.uk:$MySQLFILE /share/HDA_DATA/dave/backups/db/
#Delete the backup file from the remote server - you don't need it anymore now you have copied it to your QNAP.
ssh dave@www.myserver.co.uk "rm -r -f $MySQLFILE"