DevChunks

Automating MySQL Database Backups

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.

Share:

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the