How to Create Automatic WordPress Database Backup? (With Plugins and WP-CLI)

Create automatic WordPress database backups now. Otherwise, you will suffer in the long run!

WordPress Database backups are always important. Automatic WordPress database backups can save you from worse situations. Nothing worse than waking up one morning to find out you no longer access to your database.

Database security is very critical as we mentioned in our WordPress Security Guide. Often hackers attack websites and change vital information on your database. Without a database backup, you cannot recover your site to the previous working state. There are many WordPress database backup restore plugins which can help you to be safe.

Automatic WordPress database backups can help you to be in peace of mind knowing that you always have a plan B. That is why automatic WordPress database backups are essential for every website including eCommerce, multivendor, classified or multilingual.

What is a Database Backup?

Simply put, a database contains all of your website’s information (the text, image info, videos – everything).

A database stores this information in database tables. So if your database is compromised anyhow, you will be left with a mess. A database backup is a backup or replica of your database in a separate place. Automatic database backups help you to automate the WordPress database backup process.

Easiest Way to Move a WordPress Site from Localhost to Server [Step by Step]

That is why we covered a separate tutorial on WordPress security guideline. We mentioned about backing up your database frequently.

There are many plugins which can automatically backup your WordPress database on a daily or weekly basis. You can even back up the databases on separate cloud service like Amazon AWS, Google Drive, Dropbox using WordPress plugins.

This tutorial will explore the best options to backup WordPress databases using plugins and CLI mode.

How to Enable Automatic WordPress Database Backup Plugins?

You can enable automatic WordPress database backups by using free plugins. There are many free plugins in the WordPress repository that give you the ability to create automatic WordPress database backups. You can schedule WordPress database backups daily, weekly, monthly or even yearly.

The best thing about these plugins is that some of the plugins support external upload of WordPress databases. For example, you can schedule to take backups of your database daily and save them in your personal cloud storages. You can use these plugins to enable WordPress backup to Dropbox too. Many plugins support automatic WordPress backup to other servicesautomatic wordpress database backup such as Google Drive, OneDrive or AWS.

Best Automatic WordPress Database Backups Plugins

1. WP DB Manager – WordPress Database Backup Plugin

WP DB Manager – WP DB Manager is one of those great free plugins that just works. It is certainly one of the best WordPress database backup plugins. WP DB Manager can backup your database and email it to you weekly, daily or on monthly basis. You can set up and forget as the plugin will start creating automatic WordPress database backups. You can also save those database backups directly to Cloud Storage services like Dropbox, AWS, Google Drive.

WP-DBManager

2. UpdraftPlus WordPress Backup Plugin

Udraftplus is a very popular WordPress plugin. The plugin allows one to backup the whole website and to restore the backup at a later date. Udraftplus can also be used to move a site from localhost to live server.

Udraftplus has great reviews and has more than 2Million downloads. It makes the whole process so easy that you can take backups and store into the cloud and restore them with a single click!

UpdraftPlus WordPress Backup Plugin

3. Backup & Staging – BlogVault Backups

Backup & Staging is one of the new plugins in the wild. However, the plugin has already got some fanfare due to its unique services other than backups. Backup and Staging plugin provides the option of malware scanning and one-click malware removal functionality which is pretty neat.

BlogVault WordPress Backup Plugin – Migration, Staging, and Backups

The plugin has been deployed to more 500,000 sites and counting,

Comes with Malware Scanning and smart incremental backups

One of the unique features of BlogVault Backups is that it takes smart incremental database and file backups. Also, the backups are stored on cloud servers, so you never lose your backups. One less thing to worry about, right? It also has support for FTP, SFTP or FTPS for migrating/restoring feature.

There are some few other plugins which we should mention. You can always check them out if you are not satisfied with the current ones.

Honorable Mentions

1. BackWpup

https://wordpress.org/plugins/backwpup/

2. Backup Database

Backup Database

3. Keep Backup Daily

Keep Backup Daily

How to take WordPress Database Backups from CLI?

Since using CLI is a bit complicated for new users, we suggest you to use plugins. However, if you really want to take WordPress database backups as a professional, follow the steps.

Step 1

• Install WP DB Backup.
  WP DB Backup is a command line tool for backing up your WordPress database to a local folder, remote location using SFTP (SCP) and Amazon S3. WP DB Backup is a shell script that uses the combination of WP-CLI commands to backup your WordPress database. The database backups can be saved onto your local Linux server or to remote locations such as Amazon S3.
• The backup files generated from WP DB Backup will be gzipped automatically for your convenience.

Step 2

• Login to your shell of your Linux distro.
• Type wget -O - https://raw.github.com/tareq1988/wp-db-backup/master/setup.sh | bash to install WP DB Backup.

Step 3

To take a backup, the commands are really straightforward.

If you want to take a database backup and save it your own local server/VPS, type the following command

wp-db-backup --path=/var/www/example.com/htdocs --backup-dir=/home/user/dir

You can use a whole lot of options using the wp-db-backup keyword.

Options

  -h 	--help
  [--ssh=<path>]
	Path to your remote directory if --type=scp. e.g. --ssh=user@host:/path/to/dir

  [--type=<type>]
	Type of backup. Default: dir. Possible values include 'dir', 'scp', 's3'.

  [--backup-dir=<dir>]
	Path to your backup directory if --type=dir.

  [--path=<path>]
	Path to the WordPress installation. Required if the current directory is not a WordPress installation.
  [--s3path=<path>]
	Path to your s3 directory if --type=s3. e.g. --s3path=path/to/dir. s3:// will be prepended automatically.

  [--filename=<name>]
	The file name for the backup file. The date name will be automatically appended.
	.e.g <filename>-<year-month-date>.sql.gz. Default is 'database'

Types of Backup WP DB Backup Supports:

1. Remote Backup using SFTP. --type=scp
2. Local Backup. --type=dir, this is the default one.
3. Remote Backup to Amazon S3. --type=s3

Local Backup

Type: If you want to back up your database to the same machine, this is the default backup type and you don’t need to pass the additional parameter--type=dir.

Directory: By default, the backup will be stored in Backups folder in your home directory (~/Backups). But you can override that with the --backup-dir=/path/to/your/backup, this has to be the absolute path.

wp-db-backup --path=/var/www/example.com/htdocs --backup-dir=/home/user/dir

Remote Backup – SFTP

WP DB Backup uses SCP to push the backup file to a remote location.

wp-db-backup --path=/var/www/example.com/htdocs --type=scp --ssh=user@host:/path/to/directory

Remote Backup – Amazon S3

s3cmd – A very popular Amazon S3 client is being used for this type of backup. The setup script should automatically be installed for you if you’re using Debian based Linux Distro. Otherwise, you have to install it manually.

After installation, please configure your S3 client using s3cmd --configure and make sure you can upload files to your S3 bucket. [Please take a look at S3 policies to securely configure your S3 bucket]

s3path: Let’s say your S3 bucket name is my-backup and the backup directory is sitename, the s3cmd compatible way of putting a file is: s3cmd put filename.zip s3://my-backup/sitename/. So your backup command will be:

wp-db-backup --path=/var/www/example.com/htdocs --type=s3 --s3path=my-backup/sitename/

You don’t need to put the full s3://my-backup/sitename/ path, s3:// will be automatically prepended for you.

Cron Job

Cronjob can automate this process of backups. So basically using WP DB Backup and Cron Job, you can take database dackups automatically without using WordPress database backup plugins.

Example cronjob for 3 types of backup.

Daily Backup using WP DB Backup and Cronjob

0 0 * * *	/usr/local/bin/wp-db-backup --path=/var/www/example.com/htdocs --backup-dir=/home/user/dir > /dev/null 2>&1
0 0 * * *	/usr/local/bin/wp-db-backup --path=/var/www/example.com/htdocs --type=scp --ssh=user@host:/path/to/directory > /dev/null 2>&1
0 0 * * *	/usr/local/bin/wp-db-backup --path=/var/www/example.com/htdocs --type=s3 --s3path=my-backup/sitename/ > /dev/null 2>&1

Weekly Backup using WP DB Backup and Cronjob

0 0 * * 0	/usr/local/bin/wp-db-backup --path=/var/www/example.com/htdocs --backup-dir=/home/user/dir > /dev/null 2>&1
0 0 * * 0	/usr/local/bin/wp-db-backup --path=/var/www/example.com/htdocs --type=scp --ssh=user@host:/path/to/directory > /dev/null 2>&1
0 0 * * 0	/usr/local/bin/wp-db-backup --path=/var/www/example.com/htdocs --type=s3 --s3path=my-backup/sitename/ > /dev/null 2>&1

Source: Github

Wrapping Up

WordPress database backups are very important to make sure your site is safe.Check our ultimate WordPress Security Guide for more WordPress related security tips.

31+ WordPress Security Tips – Ultimate WordPress Security Guide (2022)

It is also very much essential to maintain a fast loading website with better optimization for great SEO optimization. You also need to make sure your website has all the best WordPress plugins, running on the latest version of WordPress.

Follow us on Facebook and Twitter for more beginner-friendly WordPress tutorials and reviews