Simple Script to Backup Smaller MYSQL Databases remotely

Here is a simple script you can use to add as a job that you can use to backup smaller remote MYSQL DB’s.  This script will take a mysql dump, date it, then add it to an archive (with the date), then the uncompressed file is deleted.  It is perfect to use as a daily cronjob. The instructions are specifically for Ubuntu, but it can be easily modified for Unix or even a Windows batch file.




First create a folder in your home directory called backup, then switch to it and create a script file:

sudo mkdir ~/backup
cd ~/backup
sudo nano SCRIPTNAME.sh

Now paste the following into it substituting the words in CAPS with your settings :

#!/bin/bash
cd ~/backup
mysqldump -h REMOTE_DATABASE -uDBUSER -pDBPASSWORD --opt DBNAME > ~/backup/FILENAME.$(date +%F).dat.sql
tar -cf FILENAME.$(date +%F).tar FILENAME.$(date +%F).dat.sql
gzip FILENAME.$(date +%F).tar
rm -rf FILENAME.$(date +%F).dat.sql

Once you exit and save you now need to make the script executable:

sudo chmod +x SCRIPTNAME.sh

Finally, to execute this script daily at 12:30am as a cronjob add the following to the crontab:

30 0 * * *      ~/backup/SCRIPTNAME.sh

You may need to allow your backup server to communicate remotely with your MYSQL DB. For very large databases you probably do not want to do this as it will take an uncompressed mysqldump of the remote DB server.

For large databases, you should run the job locally to make the compressed backup file, then either add an SCP or FTP command to transfer the database to the remote backup server.