How to Schedule Backup WordPress Database

Do a schedule backups of your WordPress MySQL database is the most important thing to do when running your own WordPress website, it is required to restore your blog, it needs to urgently migrate to another host or restore your database from a crashed server.

From official WordPress guidline – The WordPress Codex, you will find some excellent ways to back up database using phpMyAdmin, MySQL commands, and MySQL administrator. But are you willing to backup your site daily, weekly, monthly and everyday of the year by yourself? There are too many methods, plugins, tools which help you done this job by automatically back up your MySQL database for you and send it across to a safe storage space such that the latest backup is there whenever you need it, wherever you need to restore database from across the world.

There are several ways to backup MySQL data. In this article I will show you how to backup your databases using different methods, we will also learn how to achieve an automatic backup solution to make the process easier, including the backup of your database to a file, another server, and even a compressed gzip file and send it to your email.

Update:

• 2019.09.09: Remove unavailable items.

Why WordPress regular backup is very important

Your WordPress is self hosted and you have to care it yourself. There are many reasons to loss your WordPress database or any other data;

• A plugin may corrupt your database and sometime it may not be recoverable
• If your blog is hacked than you must need a backup to restore your website
• Sometime your hosting provider encounter a fatal server error and all your data may be loosed
• Or any other unknown error may occur anytime

Automatic backup MySQL database with CPanel

CPanel is most popular hosting control panel on Linux server (most popular too). If your hosting service support CPanel, you can create a cron job in your server and schedule it to backup your database everyday.
1. Login to your CPanel then looking for a Cron Job icon.

2. Setting the time and frequency to run backup command

3. Under the command field, copy and paste the following command:
mysqldump --opt -Q -u dbusername --password=dbpassword dbname | gzip > /path-to-store-the-backup-file/db_backup.sql.gz

• Replace dbusername with the database user
• Replace dbpassword with the database user password.  In some server, you might need to put a pair of single quote 'dbpassword' around the dbpassword for it to work.
• Replace dbname with the database that you are backing up
• Replace the path-to-store-the-backup-file to the file path in your server where you want to save the backup

Tips: I recommend you choose the storage location of backup above the /public_html/ to prevent unwanted eyes.
Now, you can test this cronjob first to make sure it works.

Use WordPress plugins to automatic backup your website

WordPress has a ton of plugins to help you backup your website include MySQL database and files. Obviously there are many more available and a whole host of cloud backup services, but I have kept this post as backup plugins that are tried, tested and come highly recommended by colleagues that are actually using the plugins.

1. Super Backup & Clone – Migrate for WordPress

This plugin allows you to back up all your WordPress, as well as non-WP files immediately, to your own cloud, or on the same server, for safe keeping and easy restoration and much, much more!
With the Super Smooth Dashboard you have a clear overview of all your existing backups, where they’re stored, how much space they occupy, as well as a view of when the latest backup occurred.

• The Dashboard also helps you keep an eye on your available resources so you can take appropriate measures ahead of time!
• The Migrate panel allows you to easily migrate a different backup onto your current working server. This allows you to import a pre-existing backup, either from your own hard drive, or an external URL. With extensive settings we’ve ensured that you have full control over what is migrated, so that you can rest assured that the migration will not break your current installation, before you even hit the button!
• Set up a schedule once and you don’t even need to worry about it anymore, because it all happens automagically – exactly according to your schedule!

WP SuperBackup allows you to have a single backup instance, or as many as you’d like, all stored on the same server, or the cloud service of your choice.
This plugin supports: Same Server, Google Drive, Amazon S3, Rackspace, (S)FTP, Copy.com, OneDrive, Dropbox.

2. BackWPup

The backup files can be used to save your whole installation including /wp-content/ and push them to an external Backup Service, if you don’t want to save the backups on the same server. With the single backup .zip file you are able to restore an installation.
Positives:

• Database Backup (needs mysqli)
• WordPress XML Export
• Generate a file with installed plugins
• Optimize Database
• Check and repair Database
• File backup
• Backups in zip, tar, tar.gz, tar.bz2 format (needs gz, bz2, ZipArchive)
• Store backup to directory
• Store backup to FTP server (needs ftp)
• Store backup to S3 services (needs curl)
• Store backup to Microsoft Azure (Blob) (needs PHP 5.3.2, curl)
• Store backup to RackSpaceCloud (needs PHP 5.3.2, curl)
• Store backup to Dropbox (needs curl)
• Store backup to SugarSync (needs curl)
• Send logs and backups by email
• Multi-site support only as network admin

If you don’t have a backup schedule in place or you only have a partial backup I recommend you download and install BackWPup. I’ve ran this plugin for a long time ago until I found another way to sync all my website to local computer and online storage services. I guess it’s best backup plugin for WordPress, the free version is enough for personal using.

3. VaultPress

VaultPress is a subscription-based protection, security and backup service for WordPress blogs and sites. Built on the same Automattic grid that serves over 32 million WordPress.com blogs and 330 million monthly visitors, VaultPress secures your site.
VaultPress is now part of Jetpack, so you’ll need to install Jetpack on your site, connect your website to WordPress.com, and select (at least) the personal plan to get started. On the Personal Plan ($3.50 /mo or $39 yearly) your complete website, including content and database will be backed up once a day. Backups are stored on the VaultPress servers which means you are not reliant on backups that your web host should provide. With VaultPress you’re protected against hackers, malware, accidental damage, and host outages.
As NARGA.NET’s reader, you will receive a 20% discount on any Jetpack / Vaultpress plan.

To receive the offer, you will need to visit Jetpack (using the link below) and purchase a plan:

• Visit the plans page on Jetpack
• At the checkout, quote coupon code: DISCOUNT20

VaultPress is a perfect backup product.

4. Backup

Backup is a plugin that provides backup capabilities for WordPress. Backups are zip archives of created locally and uploaded to a folder of your choosing on Google Drive.

You are in total control of what gets backed up.
Features

• Schedule automatic backups.
• Back up the database.
• Back up files and directories.
• Fine grained control over what gets backed up.
• Store backups locally and/or on Google Drive.
• Interrupted uploads to Google Drive automatically resume.
• Get email notifications when something goes wrong.
• The settings page interface uses standard WordPress elements to fit right in.
• Extensive contextual help included.
• Advanced options are provided to control the inner workings of the plugin.

5. WP-DBManager

Allows you to optimize database, repair database, backup database, restore database, delete backup database , drop/empty tables and run selected queries. Supports automatic scheduling of backing up, optimizing and repairing of database.
This plugin is not as simple as some. Most people will head for the email daily back up option and that is not as straightforward as you would like it to be. It takes persistence to stick with this plugin but it is worth it.

Backup Your Database into an XML File Using PHP

I found this article from David Walsh’s blog, with a PHP script you can export MySQL database automatic to XML format. XML isn’t the easiest format to restore but it’s easy to read. Combinned with CronJob, it’s can schedule daily or as you want.

Backup MySQL with SSH

Some web hosting services supported SSH login like Digital Ocean. This is easy way to backup large MySQL database that doesn’t require PhpMyAdmin or PHP script.
Here are the steps.

• Enable SSH login inside your hosting control panel or customer panel.
• Using utility such as PuTTY or similar, log into your server via SSH. I’m using Terminal.
• Change to the directory where you want to save your backup files — e.g., /public_html/dbbackup, /sqlbackup…
• Using mysqldump command to export the database into a file on the server. For example:

  mysqldump --add-drop-table -u db_username -p db_name > mybackup.sql

• Download mybackup.sql to your local computer or tranfer to online storage services

About the command:

• Omit the –add-drop-table argument if you’ll want to merge this backup with an existing database upon restore
• Replace db_username with the name of your database user
• Replace db_name with the name of your database
• Replace mybackup.sql with the name of your backup file

Automatic Schedule Backup WordPress Database to Email

After choosed best solution to schedule backup WordPress database, do you want it sends database files to specified email? All the WordPress plugins which I mentioned above has this feature, if you want to do it by hand, here for you:

CronJob command

Using this command:

mysqldump -e --user=username --password=password dbname | gzip | uuencode dbbackup_e.gz | mail [email protected]

Some questions to ask

• Do you back-up your WordPress site(s)? If so, how often?
• Have you ever lost your WordPress site?
• Does your web host provide consistent, automatic and easily accessible back-ups? If so, what web host are you using?

Conclusion

So keep daily database backups and you will never have a sleepless night worrying if your site is wiped out the next day! If I have not mentioned a plugin or procedure that works well for you, would you be kind enough to share in the comments? Are you using any of the WordPress Backup plugins that are mentioned above on your blog?