In Part 1, I detailed how to set up an automatic backup for WordPress using a cron job calling mysqldump. This post has a slight twist on that method.

I recently had to set up a backup for a blog where this technique wouldn’t work.  The cron job would work and mysqldump worked, but whenever mysqldump was called from a cron, it resulted in an error: blocked because of many connection errors.

Normally, my first course would be to fix the underlying error, but in this case the blocking issue was only a problem for the cron backup and did not affect website performance.  Rather than figure out how to resolve the blocking issue, I knew that I could run the mysqldump from a PHP script and call that script from cron rather than trying to call the mysqldump from cron directly.

Here’s the PHP script I used:

    $version = 'live_12';
    $dbbackuppath = 'xxx';
    $dbuser = 'xxx';
    $dbpass = 'xxx';
    $dbhost = 'xxx';
    $dbname = 'xxx';

    $target_name = microtime();
    $target_name = str_replace(' ', '', $target_name);
    $target_name = str_replace('.', '', $target_name);
    $target_name = 'dbbackup_' . $version . '_' . $target_name . '.sql';
    $target_path = $dbbackuppath . $target_name;

    exec("mysqldump --user=$dbuser --password=$dbpass --host=$dbhost $dbname > $target_path", $out);

    echo("\n\ndone: $target_name");

For the real production code, all those variables at the top defining the file system path where the log will be stored and the database connection parameters are in an application config file.  I’ve included them inline here for completeness.

Basically this code creates a filename for the backup file which incorporates the application version number (to make it distinct from other backups saving to the same location) and the date/time the backup was run.  It then executes mysqldump using PHP’s exec command.

I’m not a big fan of using exec from script, but in this case it’s running in a non-public script and I’m using IP address filtering in htaccess to insure that this script can only be run by the server running the cron job.  It’s also saving the backup files in a location that is outside the web root path so these backup files cannot be retrieved from the server via HTTP.  Depending on what your site holds, it might be a very bad idea to make these backups publicly available even if they’re hidden.

With that script in place and after testing it to make sure it creates a backup file properly, you can automate the process using a cron job just like we did in Part 1.  The only difference is that in this case instead of running mysqldump, we use wget to hit the database backup PHP script.  To use wget, you just provide the URL of the script you want to load so your cron command looks like:

wget http://mydomain.com/dumpdata.php

While not quite as simple as using cron to call mysqldump directly, this method is easier to set up since you can test your dump script separately from cron.

Is there a better way?  Probably.  Give me feedback in the comments if you’ve got a more elegant way to handle automating backups of your site database.

Everyone has heard that backups are important, but are you sure you’re backing up the data you really need?

If you write a blog or run a website of any kind, there’s probably a database being used to generate the pages that users see.  If you’re not backing that data up regularly, everything you’ve created on the site could be lost.  A few simple steps can give you the security and peace of mind that all the content you’ve created is save and can be restored if needed.

The first step is logging into your hosting account.  If you have a blog set up on a host like GoDaddy, DreamHost or Bluehost, you might not have logged into this account since you did your initial setup, but you should be able to go to the hosting website and get to your account settings.

Many hosts will refer to this as the cPanel.  Others will just refer to it as “My Account” or “My Hosting Account”.  Browse through your hosting account website to find the screen that gives you access to your databases. Screen 1 shows the option as it appears on the control panel at BlueHost and similar services and the option is called “phpMyAdmin” which gives you administrative access to your databases.

Screen 1

Logging into your MySQL database with phpMyAdmin, supply the username and password you use to access the database on Screen 2.  This is NOT your main account username and password.  This is the username and password used by WordPress to access the database.

Screen 2

When you’ve logged in, you’ll see a general information screen shown on Screen 3.  On the left it will have two items, the general schema database which is called “information_schema” in this case and your main database.  On the right there are tabs for performing different actions on the selected database.

While you’re on this screen, make a note of the URL your browser is displaying (underlined in red on Screen 3).  You’ll use this later if you want to automate the backup process.

Screen 3

Select your database on the left (circled in red on Screen 3, but yours will not be named “my_database”) and you’ll see the tables within your database listed on the left as shown on Screen 4.  The right side will also show the tables in the Structure tab.

Screen 4

Click the Export tab at the top, circled in Screen 5.  Initially, the radio button at the top labeled “Quick” will be selected and the page will be very short.  If you select the “Custom” option below “Quick”, the options on the page will expand as shown on Screen 5.  You can leave all of these in the default state, no changes are necessary, but if you want to limit what data is saved or customize the format, you have the options available.

When you click the Go button at the bottom of Screen 5, you’ll see a Save-As dialog allowing you to save the export file on your own computer.

Screen 5

After you’ve saved the file, you can log out of your MySQL database using the logout button at the top of the left panel or just close the browser window.

Automating the Backup

If you want to automate this process and save backups on schedule, go back to the control panel screen we used in the first step, find the option for “Cron Jobs”.  Cron is a time-based scheduler that lets you set up tasks that will be executed at specific times in the future.

Screen 6

At the top of the cron-job setup screen, there is an block to set up an email address which will be notified each time the task executes.  This email address, underlined in red on Screen 7, will probably be set to your main account’s email.  If it’s not set or you want to change it, use the “Update Email” input and button below it to set it to the email address you want notified.  You may be inclined to leave this blank because you just want this taks to run and not keep telling you about it, but in the beginning you need to get the notifications to make sure the task you create runs successfully (and receive helpful error messages if it doesn’t).

Screen 7

The next step is to set how often the task should run.  You need to make a decision based on how often your site is updated whether the backups should run weekly, daily or even hourly.  If you update every day and there’s a lot of activity on the site, you should choose daily.  You might be safe with weekly if your site is less active.  The “Common Settings” dropdown box circled on Screen 8 provides a shortcut for filling in the boxes below it.  For example, if you select “Once a day” as shown on Screen 8, the minute and hour boxes below it are filled in with zeros, indicating that it will run at midnight (0:00) and the day, month, and weekday are filled in with asterisks indicating that the task will run every day, every month and every weekday.  You can change the numbers in the hour and minute boxes if you want the task to run at a different time every day.  You can also change the other inputs to customize exactly when your task repeats.

Screen 8

The last step is to fill in the Command at the bottom of Screen 8.  This is the task that will be executed every time your schedule job runs.  This could be almost any task that will run from a Linux command line.  For our needs in backing up a database, we want a command like:

mysqldump --opt -Q -h myserver.myhost.com --user=my_db_username 
--password='my_db_password' my_database_name | gzip -v9 - > 
/home/myaccount/public_html/archive/_db_backup-
`/bin/date '+\%Y-\%m-\%d_\%H-\%M'`.sql.gz

Let’s go through that step by step:

mysqldump – this is the command-line program that comes with the MySQL database which will create an export file with all the table schema and data just like we did manually in the first few steps of this article (Screens 1-5).

–opt – this is an arguement for mysqldump that specifies certain outputs.  It’s shorthand for a collection of individual arguments and sets the most common outputs:  add-drop-table, add-locks, create-options, disable-keys, extended-insert, lock-tables, quick, set-charset.

-Q – this sets the quote-names parameter that will cause table and field names to be surrounded by quote marks in the output file.  This may not be necessary for your database, but it’s necessary in some situations and doesn’t hurt to leave it in.

-h – indicates that the next value will be the hostname of the server where the database is located

myserver.myhost.com – this is the server name of the server hosting your database.  We made a note of it earlier when looking at Screen 3.

my_db_username and my_db_password – this is your database username and password, the same ones you used on Screen 2.

my_database_name – this is the name of your database, the name that’s circled in red on Screen 3.

/home/myaccount/public_html/archive/ – this is the path to a directory where you want the files to be saved.  You may need to check your site’s FTP settings to find the root path (the /home/myaccount part).  You’ll probably want to create a new directory like “archive” or “db_backup” to store your export files in.  You’ll need to make this directory writable using your FTP tool.

The command up to this point, mysqldump –opt -Q -h myserver.myhost.com –user=my_db_username –password=’my_db_password’ my_database_name, creates the MySQL export file.  This is followed by the pipe character, |, that will send the output of the export to the next part of the command line that will save the file.

gzip -v9 will compress the output file into a GZ file, sort of like a zip.

-> is like the pipe command, it will send the output of the gzip command to the filename specified next.

The remainder of the command line specifies the file where the export will be saved.

/home/myaccount/public_html/archive/ – this is the path to a directory where you want the files to be saved.  You may need to check your site’s FTP settings to find the root path (the /home/myaccount part).  You’ll probably want to create a new directory like “archive” or “db_backup” to store your export files in.  You’ll need to make this directory writable using your FTP tool.

The next part is the filename you want to use within that directory:  _db_backup-`/bin/date ‘+\%Y-\%m-\%d_\%H-\%M’`.sql.gz

The file name will start with “_db_backup-” followed by the date formatted in YYYYMMDD_HHMM format followed by the .sql.gz extension.  Using the date command will provide a unique filename for each file that’s created by the scheduled task.

Fill in the command line with all these parameters set with the values for your database and click the Add New Cron Job button shown on Screen 8.

You might want to set the time for a few minutes from the current time (accounting for the timezone change between you and the server) so the cron job will run soon and you can check the output.  You’ll get an email indicating the success or failure of the task.  Then you can come back and edit the time to have it run sometime in the middle of the night or whenever traffic is lowest.

If everything has been set correctly, you’ll start to get backup files in the directory you created.  Now you just have to remember to download them and clear out that directory every so often so it doesn’t fill up and use all the space in your hosting account.

Many hosts will have a slightly different setup.  If your screens don’t look like the ones I’ve shown above, post a comment with info about your host and I’ll try to add details that will help you.