Backup Your WordPress Database with WP-CLI

In this article you will learn how to backup your WordPress database using the wp-cli tool. Knowing how to backup your database is a critical skill to have when running a WordPress site. All your posts, pages, and more live in your database; keeping backups is critical.

You should always take a backup before any major changes to your site, just in case. It’s much quicker to take a backup now and do a restore if you need to, than to find a useful backup when you need it.

Pre-flight Check:

  • These instructions were created with a cPanel-based server in mind.
  • Command line access via SSH will be necessary to follow along.
  • The server must have WP-CLI installed, for installation directions see this tutorial.

To follow along with this tutorial you will need to have WP-CLI installed. If you have not yet installed wp-cli on your server feel free to read our tutorial here on that subject.

  1. Login to the server via SSH as the cPanel user that owns the domain, or the root user.
    ssh wordpress@198.51.100.142

    In the example we are using ssh to connect to the server. You can follow the rest of the steps even if you connect via alternative means(TTY, a Windows SSH Gui, etc).

    If you login as root you will need to use `su` to take on the user which owns the WordPress site before you continue.

  2. Now logged in (as the cPanel user) change to the WordPress root folder:
    cd ~/public_html

    We’re using a cPanel server so we know WordPress should be installed in the `public_html` folder. If you are not on a cPanel server it will be in a different location.
  3. Once in the WordPress root folder (where you can find wp-config.php) you can use the wp-cli tool. To backup your database run one of the following commands:
    • Backup the database to the current folder:
      wp db exportThis will use all the defaults for exporting the database as a backup. This means it will be in the current folder and will have the same name as the database.
    • Backup the database to a specific file:
      wp db export ../my_wordpress_db.sqlUsing this command will backup the database to a file called “my_wordpress_db.sql” and will put it in the folder above the current one (or `../`). This is done as a security measure to ensure no one can download your database.

Once you’ve run the export command you’ll see confirmation output like the following:

Success: Exported to ‘../my_wordpress_db.sql’.

After you see that confirmation your WordPress database is officially backed up. Now you can continue making changes or updates and will have a way to restore if anything goes wrong.

How To Enable Server Backups in WHM/cPanel

The most important thing you can do to protect your server against data loss is to take regular backups. Properly configured backups are a critical aspect to the maintenance of any website, and can mean the difference between a quick recovery and rebuilding a site from scratch.

If a key file were to accidentally be deleted, a site database were to become irreparably corrupted, or your site were to become infected with malware, would you be able to restore your data and get your site back up within a few minutes? If you can’t answer “yes”, then it’s time to review your backup strategy.

Liquid Web customers have access to a variety of backup methods including Guardian and Storm® backups, but this article focuses exclusively on cPanel backups. Because cPanel backups are not managed by Liquid Web, you will want to ensure that you have both enabled backups as described in this article and formulated an appropriate backup retention policy. It’s critical that you not attempt to store so many backups that your server runs out of disk space. In fact, you ideally want to reserve at least 10 percent to 20 percent of your total disk space: cPanel needs to (temporarily) use disk space to create backups and update itself and other software packages, and your operating system and database both rely on having disk space available to perform core functions.

Pre-Flight Check

  • This article covers backup configuration in WebHost Manager using cPanel’s “new” backup system. Legacy backups still are supported in cPanel for backward compatibility, but the current system is preferred as it is designed to be more efficient and reliable.
  • While these instructions specifically apply to WHM version 54.20; the same steps should apply to any recent cPanel version.

Step #1: Configure Global Settings

Log in to WHM using your server’s root credentials, and select Backup Configuration under the Backup section in WHM’s main menu. You can begin typing “backup” into the search menu to narrow the list of menu options.

WHM backup

In the Global Settings section. select the radio button next to Enable to turn on the backup system.

Note: The Enable button only turns on the backup system; you still need to instruct cPanel to back up accounts, and which accounts to back up, which we cover later in this article.

Now choose the type of backups you want cPanel to create:

  • Compressed backups use the least amount of disk space, potentially giving you more restore points than could get with the other methods. Compressed backups take longer to complete and the process of restoring an individual file requires the extra step of first extracting the file, but in many cases it represents an ideal option. Note that the size of a compressed cPanel backup will range from about 25% to 75% of the actual account size, depending on a number of factors, such as the types of files owned by the account. Until backups have run and you can see the actual size of each archive, it’s generally safe to estimate that a compressed backup will take up about half the space of the account itself. Once backups have run, you can use the actual figures to determine how many compressed backups to retain.
  • Uncompressed backups can be completed faster, and the process of restoring individual files is quicker because they don’t first need to be extracted from the archive. However, they take up the same amount of space as the account itself and, as such, allow for fewer restore points. If you choose to use uncompressed backups, you must ensure that your retention policy does not allow you to run out of disk space.
  • Incremental backups allow you to retain only one daily, weekly, and monthly backup, giving you fewer restore points than either of the other options. The backups are uncompressed, so each will take up the same amount of disk space as the account itself. Individual files can be quickly restored, but changes to your files are synced each time backups run. That means that a corrupted, compromised, or changed file you wish to restore could already have been synced to your backup if the change isn’t immediately noticed.

Step #2: Choose Scheduling and Retention Options

The Scheduling and Retention section allows you to enable or disable daily, weekly, and monthly backups, and specify the number of each type of backup you wish to store. For daily and weekly backups, you can select the days of the week on which backups will be taken and, for monthly backups, you can select whether you want them to be taken at the beginning of the month, in the middle of the month, or both.

Scheduling and retention

Note: If you select both the first of the month and the 15th of the month for monthly backups to run, each will count as one monthly backup. When choosing both backup date options, you will need to retain two monthly backups for each month you wish to retain.

Step #3: Select Account Backup Options

The Files section allows you to select which accounts to back up and choose more detailed options for each:

FilesBackupAccounts

  • We enabled the backup system itself in Global Settings above, and now we’ll instruct cPanel to back up user accounts by checking the box next to Backup Accounts.
  • Click the Select Users to open a new browser window (or tab, depending on your browser settings) where you can select the specific cPanel accounts to back up. Simply click the ON button in the Backups column for each domain you want to back up, and ensure that the buttons in the Legacy are turned off if you’re not using the legacy backup system. Note that you can access this page directly at any time by clicking Backup User Selection in WHM’s main menu.

    BackupUserSelection

    Choose whether you want to back up accounts that are suspended, as well as access logs and bandwidth data for the accounts, and whether you wish to back up system files as well as individual accounts (which is necessary only for server restoration; individual account can be restored regardless of your choice here).

Step #4: Select Database Backup Options

In the Databases section, select whether you want to back up databases per account, the entire MySQL directory, or both. In most cases, you’ll want to choose Per Account and Entire MySQL Directory.

BackupDatabases

Step #5: Select Backup Location

The Configure Backup Directory section allows you to choose whether you want to back up locally or externally.

BackupDirectory

  • The Default Backup Directory is pre-configured, and in most cases should not be changed.
  • If you elect to Retain backups in the default backup directory, local backups will be retained even if you configure the server to automatically transfer them to an external destination.
  • The Mount Backup Drive as Needed option should, in most cases, remain disabled, as an external drive set up by Liquid Web will be configured to mount at boot.

Step #5: Select Remote Transport Options

The Additional Destinations section allows you to set up a method for transferring your backups off-server via SFTP or another method. To do so, select a Destination Type, click the Create new destination button and provide the requested credentials on the form.

AdditionalDest

Step #6: Review and Save Backup Settings

Once you’re satisfied with your backup configuration, click the Save Configuration button to apply your settings.
 

Proactive Server Maintenance Checklist, Prior to Reboot

Take a Backup, or Be Sure Backups are Running

We have tutorials on How to Create a Storm Server Backup, and How to Restore a Storm Server Backup.

Check Running Processes

Liquid Web’s Heroic Support team closely monitors the load on your servers with Sonar® proactive monitoring and service restoration. That said, it’s always wise to glance at what processes are running on your server to assure your services are running as expected. Check-out our tutorial on Monitoring Server Processes with Top for Linux.

Verify Services Will Start at Boot

Be sure to verify that all of your necessary services are configured to start when the server boots up. For CentOS users, you can follow this tutorial: chkconfig Command Examples for Red Hat and CentOS.

Save, Save, and Save Some More

Always check whether or not there are any unsaved changes to configuration files, etc. prior to a reboot. Save early and safe often!
Continue reading “Proactive Server Maintenance Checklist, Prior to Reboot”

How to Back Up MySQL Databases From The Command Line

While automated backups are important, sometimes you just want to take a quick and dirty snapshot before making a change to your data. When changing files in Linux, you can simply cp a file to another name, and cp it back if your change does not workout. With MySQL, it is not quite that simple, but it is by no means difficult.

Continue reading “How to Back Up MySQL Databases From The Command Line”

Using rsync to Transfer and Synchronize Local and Remote Systems

Rsync is a powerful tool that facilitates the transfer and synchronization of data between both local and remote systems.

File Synchronization 101: The Basics
I. Using rsync to Transfer and Synchronize Local and Remote Systems
II. How to Setup SSH Keys for Use with File Synchronization

 

Continue reading “Using rsync to Transfer and Synchronize Local and Remote Systems”