How to Setup Automated Database Backups

Published

Before I type the rest, I will admit that I'm far from an expert on Linux or bash. I did a lot of googling to arrive at the script and process that I have/use, but it's been tested and it works on my server running Debian 7.

This process is quite simple, but many people forget or just don't bother to do it. As a solution to this I've created a simple bash script that will backup your database once per-day along with deleting any backups older than 14 days. This release assumes that you have gzip installed, your server is running on a distro of Linux, and that you know enough to be able to create a bash script file. The script is as follows.

    
        #!/bin/bash

        #Get the date in the format of YYYY_MM_DD:
        DATE=$(date +'%Y_%m_%d')

        #Backup the WoW DB
        # Replace MYSQL_USERNAME with the username you use to log into MySQL.
        # Replace MYSQL_PASSWORD with the password to go with the username.
        # Replace auth, characters, and world with whatever databases need to be
        # backed up. These three are for Trinity.
        # The rest is self explanatory. You can remove these comments when you're
        # finished.
        mysqldump --user=MYSQL_USERNAME --password=MYSQL_PASSWORD --databases auth characters world | gzip -9 > /path/to/folder/where/backups/are/stored/DB_Backup_$DATE.sql.gz

        #Delete all backups older than 14 days in the WoW folder.
        find /path/to/folder/where/backups/are/stored -mtime +14 -delete
    

After creating the script file, you'll need to add a cronjob entry for it to run once per-day and set the permissions of the script file. Go to the location of the script file and enter the command chmod x+u scriptFile.sh. Next, enter the command crontab -e, then scroll to the bottom of the file that opens and type @daily /path/to/the/bash/script/backup_databases.sh. Now save the file and, whichever databases you specify, should automatically keep backups for up to fourteen days.


To restore your database from a backup use the following commands.

    
        #Unzip the backup.
        gunzip file.sql.gz

        #Import the backup.
        mysqlimport --user=MYSQL_USERNAME --password=MYSQL_PASSWORD root --databases auth characters world DB_Backup_2014_08_30.sql
    

Replace auth, characters, and world with the databases being imported and replace DB_Backup_2014_08_31.sql with the name of the .sql file to import. If this doesn't work, comment below.