• 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 :

    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.


    Leave a reply