Automating MySQL Backups with Cron

First, we're going to set up the command or script that will back up the database(s). There's a few things you're going to need to configure before you start backing up, so let's get to it...

There are a few different ways you can do these backups, so I've provided a method to use both PHP (requires php-cli) and a regular Linux shell command with MySQL to do the same thing.

Using the command line

You can use the following command to back up your MySQL database.:

mysqldump -uusername -ppassword dbname | gzip > /home/username/folder/mysql_backup.gz

You'll have to set the username, password, dbname, and path to save in. Also note, the -u and -p preceding the username and password must be there, or it will not work correctly.

To run it automatically, save your configured command from above into a file named anything you want, with as a shell script (.sh). We'll talk about automating them in just a minute, but for a little more flexibility, I prefer...

Using a PHP Script

You can also back up your databases using a PHP script. Setting it up takes a little longer, but it is more configurable if you aren't as proficient with shell scripting. You can download the PHP Class (Based on MySQLDump - written by Daniele Vigan, GPL) To configure the PHP script, you'll have to do the following:

Set your database settings) All of this information is available in your hosting providers Control Panel, or can be managed easily using phpMyAdmin or a similar MySQL management program. If you're not sure about the host, you can leave it as localhost most of the time.

/* Database details */
$dbname = "databasename";
$dbhost = "localhost";
$dbuser = "user";
$dbpw = "pass";

Compress backed up files) If you want to compress the backups that you make, you can set this to true to gzip the files before saving them.

/* Compress as gzip? */
$compression = false;

Dump as hex) Choose if you want to dump the database as hex.

/* Dump in hex? */
$hex = false;

Filename to back up to) Set the filename that you want to save the backup as. You don't have to put an extension on the end of it. You can also select if you want to add a date and timestamp in the filename. I would recommend this, as it makes keeping them together easy, while still being able to sort find them fairly quickly.

/* Filename (No extension) */
$filename = "filename";

/* add date/timestamp to filename? */
$filedate = true;

Keep in mind, (mentioned by resmo in the comments) that if your database is bigger than PHP's default memory limit of 8MB, you'll have to change your php.ini, or add ini_set ( "memory_limit", "20M") to your script to change the limit. Once you've set all of those details, I would highly suggest that you test it out a few times, to make sure that it is dumping all of the information correctly and to your preferences, and then we can proceed with automating your MySQL backups.

Automating with cPanel, Plesk, or similar

Now, if you're using a host that provides you with a program like cPanel or Plesk, you'll have an area in your control panel for setting up crontabs. Here's an example of how cPanel handles crontabs:



If you're going to run the command version of the backup, you can paste your configured command into the command field, choose when you want it to run, and click go. If you're running the PHP version, you must first copy your files into a directory, and then enter php /home/username/folder/script.php for the command. Remember, the PHP version will only work with php-cli installed on the server.

Automating without a cPanel (crontabs)

If your hosting provider does not provide you with a Control Panel, and you have shell access, you can set crontabs using the command line. (using crontabs)

Should you choose to go the PHP route, I would advise that you make sure nobody but that machine can access that folder, so that nobody can download your backups. (covered briefly here).

Now you can be certain that your MySQL databases are backed up properly – as often as you'd like. How do you keep your databases backed up? How often have do you usually back up your databases? Hopefully you'll never have to go through the pain of losing a whole database as I have in the past. Be safe, be smart, keep regular backups in multiple locations.