Automate MySQL Database Backups

by jeremyjones on March 12, 2011

  • Share
  • CevherShare
  • Share

One of the most important tasks for a MySQL administrator is the ability to backup and restore all your databases on a regular basis. This will help to prevent data loss in the event of a hardware failure or data corruption.

One great way to do this is to use a script to perform the backup on a regular basis. Ideally, you will want the script to automatically find all the databases on your server and then perform the backups. One great tool for performing the backups is the AutoMySQLBackup script. This is freely available for download over the internet from sourceforge.net.

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:

USERNAME=dbuser                Amend to the credentials for your database
PASSWORD=password
DBNAMES=”DB1 DB2 DB3”    Change to DBNAMES=”all”
BACKUPDIR=” “                      Set this to your desired backup location

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 744 to allow the script to execute.

The next step is to configure cron to run the script automatically. On the cron control panel enter the absolute path to the script and the desired backup schedule. It might be a good idea to configure the task to run on a regular basis while you are troubleshooting it then 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 is has not reported any errors. Also, 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 use it to restore the .sql file into an empty database.

Other scripts that work in a similar manner are available on the internet. 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 losing everything.

Leave a Comment

Previous post:

Next post: