Today we will show you how to automatically backup your database daily. This is perfect in case you ever need to roll-back your database to a previous state.

Step 1: Creating Backup Directories

Now we will create the backup folders where we will store our database backups.

cd /var
mkdir backups
cd backups
mkdir auth | mkdir characters | mkdir world

This will navigate you to the var directory and create the following folders ‘backups’, ‘backups->auth’, ‘backups->characters’, ‘backups->world’.

Step 2: Creating Backup Script

Let’s create a script to backup the auth, character, and world databases and save it with the current year, month, and day appended to the file name.

Let’s create a new shell script inside the new backup folder.

cd /var/backups

Now we’ll type the information below into our new script:

mysqldump  auth | gzip > /var/backups/auth/auth.$(date +”+%Y-%m-%d”).sql.gz
mysqldump characters | gzip > /var/backups/characters/characters.$(date +”+%Y-%m-%d”).sql.gz
mysqldump  world | gzip > /var/backups/world/world.$(date +”+%Y-%m-%d”).sql.gz

We will now need to give the shell script executable permissions by typing:

chmod +x

Step 3: Setting Up Cron Job

We will now be setting up a corn job to run daily at 0100 in the morning. Let’s start by opening up crontab

crontab -e 

Then we will need to add this line at the very bottom of the crontab.

0 1 * * *  sh /var/backups/

Now you should have your automatic database backup setup to run every day at 0100. If you encounter any problems, please let me know.