Backup MySQL Database from Cron Automatically

The mere mention of the word backup makes many website owners panic. "Backup! Um, yeah well I meant to do backups, but I never get around to it". Trust me, when a database fails you need a recent backup. And databases do fail!

There are many scripts available online that can run backups for you automatically. The one problem with most of them is that they can time out if the database is too big.

The alternative is to run a bash script on cron. A what on what you might ask! Don't panic!

If you have access to CPanel or Plesk, then you should have access to Cron Jobs. Navigate to that. Select "standard". We are going to add the Linux commands in the "Command to run" field.

The MySQL Dump Code for Cron

Here is the code that I use:

You need to replace anything that is in caps with the relevant text. So you need the database name, database username, database password and the server username.

Explaining the Code

The first part of the command is using the Unix date function.

We are formating it to show the Year, Month, Day, Hour, Minute and Second. This is then added to the file name so that each file name is unique.

Then we run the mysqldump command to create the backup itself.

You might need to change the path to mysqldump as this depends on your server setup. If in doubt, ask your hosting provider.

Finally we gzip the file to compress it.

You need to set the time that this cron job will run. If you want to run it once a day, then it is a wise idea to run it overnight. You can set the hour to midnight, for example.

Make sure you check that it is running by going to the root folder and checking for new backup files. You can FTP these to your local computer on a regular basis for added piece of mind.

Backup folder

If you want the backups to be in one folder, then in your FTP client create a folder called "dbbackups". This should be in the root directory so that it is not accessible from the web. Then add the folder to your path. So the command would become:

Then you can relax, knowing that a backup of your database is being created on auto pilot.

Edit: The following was added on 23 Jan 2009.

Alternative Code

The following is the code I use on another server with a different hosting provider.