Managing a WordPress website often involves working with its database. Backups and restores are critical to maintaining the health and security of your site. While tools like phpMyAdmin or plugins like UpdraftPlus can handle these tasks, they often involve manual steps or additional overhead. Enter WP-CLI—the command-line interface for WordPress—which provides a powerful, efficient, and automated way to handle database backups and restores.

This guide dives deep into using WP-CLI for automating database backups and restores, complete with practical examples.

Why Automate Database Backups and Restores?

Automation saves time and reduces human error. Here are key benefits:

• Reliability: Ensures regular backups without relying on manual intervention.
• Efficiency: Fast execution compared to manual plugins or GUI-based tools.
• Customization: Tailor backup and restore processes to suit specific needs.
• Cost-Effective: Eliminates the need for paid plugins or services.

Getting Started with WP-CLI

Before proceeding, ensure WP-CLI is installed and configured on your server. You can check this by running:

wp --info

If WP-CLI is installed, this command will display the version and environment details. If not, follow the official WP-CLI installation guide to set it up.

Backing Up the Database with WP-CLI

WP-CLI’s wp db export command is your go-to tool for exporting the WordPress database. Here’s how you can use it.

Basic Database Backup

Run the following command in your WordPress installation directory:

wp db export backup.sql

This command creates a file named backup.sql in the current directory containing your database content.

Customizing the Backup

You can customize the output filename and directory:

wp db export /path/to/backups/backup-
$(date +%F-%H-%M).sql

• /path/to/backups/: Specifies the directory to save the backup file.
• $(date +%F-%H-%M): Appends the current date and time to the filename for versioning.

Excluding Tables

To exclude specific tables:

wp db export backup.sql –exclude_tables=wp_comments,wp_commentmeta

Automating Backups with Cron Jobs

You can automate database backups by scheduling the WP-CLI command using cron jobs.

Setting Up a Cron Job

1. Open the crontab editor:crontab -e
2. Add a cron job to run the backup daily at 2 AM:0 2 * * * wp db export /path/to/backups/backup-$(date +\%F-\%H-\%M).sql
3. Save and exit the editor.

This ensures your database is backed up daily, automatically.

Restoring the Database with WP-CLI

Restoring a database backup is just as straightforward with WP-CLI. Use the wp db import command.

Basic Restore

To restore from a backup file:

wp db import backup.sql

Ensure the backup file is in the current directory or specify the full path to the file.

Clearing Existing Data

Before importing, you may want to clear the existing database to prevent conflicts:

wp db reset --yes

The --yes flag confirms the action without prompting, making it suitable for automation.

Automating Restores

Restores can also be automated, for example, as part of a staging site setup:

#!/bin/bash
wp db reset --yes
needed:port /path/to/backups/backup-latest.sql

Make the script executable:

chmod +x restore.sh

Execute the script whenever needed:

./restore.sh

Practical Example: Full Backup and Restore Workflow

Here’s a complete example combining backup and restore operations for a WordPress staging site:

#!/bin/bash
BACKUP_DIR="/path/to/backups"
FILENAME="backup-$(date +%F-%H-%M).sql"
wp db export $BACKUP_DIR/$FILENAME

Automated Restore Script

Create a script named restore.sh:

!/bin/bash

BACKUP_DIR=”/path/to/backups”
FILENAME=”backup-$(date +%F-%H-%M).sql”
wp db export $BACKUP_DIR/$FILENAME

Automated Restore Script

Create a script named restore.sh:

Best Practices for WP-CLI Automation

1. Secure Backup Storage: Store backups in a secure, remote location, such as AWS S3 or Google Cloud Storage.
2. Test Restores Regularly: Regularly test your restore process to ensure backups are functional.
3. Monitor Automations: Use monitoring tools to ensure your scripts and cron jobs run as expected.
4. Use Logging: Log output for debugging and tracking: