How to Back Up the WordPress Database (mysqldump, WP-CLI, UpdraftPlus)

This article is a localized edition of the Japanese original. Read the Japanese original

Everything in WordPress — posts, pages, comments, settings and user accounts — lives in the database (MySQL or MariaDB). Images, themes and plugins are files under wp-content, so they need to be backed up separately from the database.

This article walks through three ways to back up the database, plus how to automate the job, where to store the copies, and how to test a restore.

Before you take a backup

Check the connection details

The database name, user and host are defined in wp-config.php.

grep -E "DB_NAME|DB_USER|DB_HOST|table_prefix" /var/www/html/wp-config.php

Replace /var/www/html with the directory where WordPress is installed. Open the file to look up the password (DB_PASSWORD) only when you need it, so it doesn't end up on screen or in logs.

Create a directory for the backups

Keep backups outside the public web directory (the document root). If they sit inside it, someone could guess the URL and download them.

The VPS commands in this article assume a root shell (switch with sudo -i). That lets the backup directory be readable by root only; WP-CLI alone runs as the user that owns the WordPress files.

mkdir -p /var/backups/wordpress
chmod 700 /var/backups/wordpress

Method 1: Back up with mysqldump

Put the password in an option file

If you pass the password on the command line, as in --password=..., it stays in your shell history and shows up in the process list. MySQL clients can read connection details from the [mysqldump] group of an option file instead.

# /root/.wp-backup.cnf
[mysqldump]
user=wp_user
password=the value of DB_PASSWORD goes here
host=localhost

chmod 600 /root/.wp-backup.cnf

Take the dump

mysqldump --defaults-extra-file=/root/.wp-backup.cnf \
  --single-transaction \
  --no-tablespaces \
  wp_database | gzip > /var/backups/wordpress/db_$(date +%Y%m%d_%H%M%S).sql.gz

• --defaults-extra-file has to be the first option on the command line.
• --single-transaction writes out a consistent snapshot of InnoDB tables as of the start of the dump, without locking them. WordPress tables today are normally InnoDB.
• --no-tablespaces avoids the error "you need (at least one of) the PROCESS privilege(s)" that MySQL 8.0.21 and later raise when the user lacks the PROCESS privilege. Tablespace information isn't needed to restore WordPress.
• On MariaDB the official name of the command is mariadb-dump, and some systems don't have mysqldump at all. In that case, replace mysqldump with mariadb-dump (the options are the same).

Check the contents

ls -lh /var/backups/wordpress/
zcat /var/backups/wordpress/db_20261110_020000.sql.gz | head -20

Use the file name that was actually created. If the file starts with a -- MySQL dump or -- MariaDB dump line followed by CREATE TABLE statements, the export worked.

Method 2: Back up with WP-CLI’s wp db export

WP-CLI's wp db export runs mysqldump with the connection details from wp-config.php. The advantage is that you don't have to manage the password separately.

If you give - as the file name, the dump goes to standard output, so you can compress it on the fly. Run WP-CLI as the owner of the WordPress files (www-data for Apache on Ubuntu) and let the root shell write the output.

sudo -u www-data wp --path=/var/www/html db export - | gzip > /var/backups/wordpress/db_$(date +%Y%m%d_%H%M%S).sql.gz

If you give a file name (for example, wp db export backup.sql), the file is written to a location the running user can write to. If you omit the file name, a file named "database name-date-random string.sql" is created in the current directory.

• By default, wp db export calls mysqldump with --no-tablespaces (use --include-tablespaces if you don't want that).
• You can append mysqldump options such as --single-transaction as they are (for example, wp db export - --single-transaction).
• To export only certain tables use --tables=wp_posts,wp_postmeta; to skip tables use --exclude_tables=....
• Running as root requires --allow-root, but it can change file ownership, so running as the file owner as shown above is safer.

The WP-CLI basics are also covered in How to export the database with WP-CLI.

Method 3: Back up with UpdraftPlus (when SSH isn’t available)

On shared hosting without SSH, use a backup plugin. The free version of UpdraftPlus can back up the database and files, run on a schedule, restore, and save to remote storage (version 1.26.14 as of November 2026).

1. In the admin, go to Plugins (プラグイン) → Add New (新規追加), search for UpdraftPlus, install it and activate it.
2. Open Settings (設定) → UpdraftPlus Backups and switch to the Settings tab.
3. Under "Files backup schedule" and "Database backup schedule," choose an interval (for example, Daily) and set "and retain this many scheduled backups." The free version offers every 2, 4, 8 or 12 hours, daily, weekly, fortnightly and monthly.
4. Choose a remote storage destination. The free version supports Dropbox, Google Drive, Amazon S3 (including S3-compatible storage), FTP, email and others. Follow the on-screen instructions for the service you pick to connect it.
5. Click "Save Changes," then run one backup manually with "Backup Now" on the "Backup / Restore" tab and confirm that it appears under "Existing backups."

Incremental backups and database encryption are paid features.

Automate the backup (cron)

If you use mysqldump or WP-CLI, run a shell script from cron every day.

#!/bin/bash
# /usr/local/bin/wp-db-backup.sh
set -euo pipefail

WP_PATH="/var/www/html"
BACKUP_DIR="/var/backups/wordpress"
KEEP_DAYS=7
FILE="${BACKUP_DIR}/db_$(date +%Y%m%d_%H%M%S).sql.gz"

mkdir -p "$BACKUP_DIR"

# Export and compress the DB (set -e and pipefail stop the script on failure)
sudo -u www-data wp --path="$WP_PATH" db export - --single-transaction | gzip > "$FILE"

# Check that the compressed file isn't corrupted
gzip -t "$FILE"

# Delete files older than the retention period
find "$BACKUP_DIR" -name 'db_*.sql.gz' -mtime +"$KEEP_DAYS" -delete

echo "$(date '+%F %T') backup ok: $FILE"

chmod 700 /usr/local/bin/wp-db-backup.sh
crontab -e

Add the following line to root's crontab (runs daily at 2:00 and appends the result to a log).

0 2 * * * /usr/local/bin/wp-db-backup.sh >> /var/log/wp-db-backup.log 2>&1

Without set -o pipefail, the pipeline counts as successful whenever gzip succeeds, even if wp db export failed, and you end up keeping an empty backup.

Store backups off the server

Backups kept on the same server are lost along with it if the disk fails or the server is compromised. Keep at least one copy somewhere else. Here is an example using Amazon S3 (it assumes the AWS CLI is installed and configured with credentials for an IAM user that only has write permission).

# Add this to the end of the script above
aws s3 cp "$FILE" "s3://your-backup-bucket/wordpress/db/" --storage-class STANDARD_IA

For old files in S3, a bucket lifecycle rule such as "delete N days after creation" is better than deleting them from a script, because a bug in the script can't delete too much.

If you also archive and store wp-content (uploaded images, themes and plugins), you can restore the site even if you lose the whole server.

tar -czf /var/backups/wordpress/files_$(date +%Y%m%d).tar.gz \
  -C /var/www/html wp-content --exclude=wp-content/cache

Confirm that you can restore

A backup is only useful once you've confirmed you can actually restore it. Try a restore in a test environment (a separate database) rather than production.

# Check that the compressed file isn't corrupted
gzip -t /var/backups/wordpress/db_20261110_020000.sql.gz && echo OK

# Count the tables (about 12 for WordPress core alone; plugins add more)
zcat /var/backups/wordpress/db_20261110_020000.sql.gz | grep -c '^CREATE TABLE'

# Import into the test WordPress (wp db import reads from standard input with "-")
zcat /var/backups/wordpress/db_20261110_020000.sql.gz | sudo -u www-data wp --path=/var/www/test db import -

# Check that posts can be read
sudo -u www-data wp --path=/var/www/test post list --posts_per_page=5

If the test environment uses a different domain from production, replace the URLs with wp search-replace after the restore; otherwise requests to the admin get redirected to production.

Things to watch out for

• Restoring with wp db import or mysql overwrites the contents of the target database. Before restoring to production, take a separate backup of its current state.
• The option file and the script contain the DB password. Set their permissions to 600/700 and keep them out of Git and similar places.
• Some shared hosting providers have their own automatic backup feature. Check in advance how long backups are kept and how restores work (free or paid), and you can use it alongside a plugin.

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)