Automating MySQL Database Backups

Last Updated on: September 15, 2022

One of the most critical tasks for a MySQL administrator is reliably backing up and restoring databases.

This, of course, helps to prevent data loss in the event of a hardware failure, data corruption… or a little accident.

Most modern hosting/server solutions have some kind of database backup solution available. However, many people prefer to roll their own to save costs or just to have more control.

One great way to do this is to use a script to perform the backups automatically. Ideally, you will want the script to automatically find all the databases on your server.

A reliable tool for performing the backups is AutoMySQLBackup script – a maintained fork of a script originally on SourceForge.

Once you have the script, you will need to configure it before you upload it to your database server. Use a text editor to amend the following entries in the script file:

Add the credentials for your database
USERNAME=dbuser  
PASSWORD=password

Change to backup all available databases
DBNAMES=”all”

Set this to your desired backup location
BACKUPDIR=”“

(Be sure to check the GitHub repository linked above for the latest instructions)

Once the editing is complete, you can upload the script to the home directory for the website on your server. Change the file permissions with CHMOD to allow the script to execute.

The next step is to configure cron to run the script automatically. On your cron control panel or command line, enter the absolute path to the script and the desired backup schedule. It might be a good idea to configure the task to run regularly while you are testing it, then later set it to your backup schedule.

The job will send a detailed report to an email when it finishes. Look at the report and check that it has not reported any errors. Check your specified backup location for the backup files.

To make certain, perform a disaster recovery test by copying the backup to a different server and using it to restore the .sql file into an empty database.

Testing your backups is crucial – don’t just assume a big file means the backup was successful!

There are no doubt other scripts that work similarly. Find the one that you are happy with, and make sure you perform regular tests on your backups to ensure that you can restore your database and minimise the risk of data loss.

---

---