How To Backup Your WordPress Database Automatically
In a previous article, I'd shown you how to connect to your MySQL WordPress database remotely through the HeidiSQL program. Now if you need to take a backup, it's extremely easy. Just open your database in HeidiSQL, right-click the database you want and export as SQL. Problem solved!
However, this is purely a GUI interface. It needs a user to manually select the backup option. There's no command line interface that we can use to schedule a task. So in this tutorial, I'll show you how to schedule a backup of your database and download it onto your computer.
Why Not Just Use a Plugin?
Two reasons. First, a plugin will perform a backup and either store it on the local server or send it to a 3rd party online storage. If your local server crashes, you lose all your backups. In addition, you want to reduce the load on your WordPress installation as much as possible. This means minimizing cron jobs and reducing the number of plugins.
For these reasons, I prefer to schedule backups remotely and have them downloaded onto my local desktop.
Gathering the Requirements
To schedule a remote backup, we need the following pieces of information first:
- The full path to where you want the backup to be stored
- Your hostname or IP address
- Your database username
- Your database password
- Your database name
Step 1: Getting the Database Details and Enabling Remote Access
To connect to the database remotely, we need to follow the same initial steps as outlined in my earlier tutorial. Basically, open up your wp-config.php file and get the details. There are screenshots in the tutorial as well, so check it out!
The tutorial also includes instructions on how to enable remote access from cPanel.
Caveat: Due to PCI compliance requirements, you might need to use a different IP address for remote access. Read the instructions on the remote access screen in cPanel for further details.
Step 2: Downloading the mysqldump Utility
This is the standard tool for exporting MySQL databases from the command line. The mysqldump utility is part of the community server package from Oracle. Visit this page and scroll down to download the zip archive.
There's no need to get the debug binaries and test suite. The first one is sufficient. Once you've downloaded it, unzip the directories into a permanent location. I personally choose C:\ , but you can put it anywhere you want as long as you remember where it is. Once unzipped, navigate to the "bin" directory to find the "mysqldump.exe" file as shown here:
Copy the full path to the file so we can reference it. One way to do this is to use the "where" utility in the Windows command line with the /R parameter indicating the directory to look in. For example:
where /R c:\ mysqldump
Shows this:
I copy this address and keep it with me. Now we have all the ingredients to create the backup command.
Step 3: Creating the Backup Command
The template for the command is this:
[full path to mysqldump.exe] --column-statistics=0 --host [host or IP name] -u [username] -p[password] [database name] > [path to destination filename]
Note that there's no gap between "-p" and your password!
Plug in all the necessary pieces of information. When you have your final command, open a command line and execute it like this:
You'll get a warning about using your password on the command line, but it can't be helped. Can't schedule a task without it! Once the command completes, verify that it works. The backup file should have been created like this:
Step 4: Scheduling the Task
Now that we have a working command, we can schedule it to run using the built-in OS task scheduler. On Windows, we can access it like this:
Once open, go to Action->Create Basic Task, and a wizard dialogue will open to guide you through the process. Choose the frequency and the time of execution. In the "Action" dialogue, choose "Start a program", and then paste the entire command into the provided box.
You'll get a popup asking you to confirm the parameters. Click ok to continue and your task will be scheduled as planned.
Congratulations! You've successfully scheduled a remote backup of your WordPress MySQL database onto your local PC!
