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
nano backup.sh
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 backup.sh
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/backup.sh
Now you should have your automatic database backup setup to run every day at 0100. If you encounter any problems, please let me know.