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.
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.
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.
I copy this address and keep it with me. Now we have all the ingredients to create the backup command.
Note that there's no gap between "-p" and your password!
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!