Backing up a WordPress database properly

Something is about to change. A major plugin update, a PHP version bump, a move to a new host, or a bit of SQL you are not sure about. The advice is always the same: back up first. What nobody tells you is what counts as one.

A backup is not a file. It is a file you have proved you can put back. Most of the backups I find on inherited sites are neither: a plugin that stopped running eight months ago, a .sql file in the web root that turns out to be half a database, or a host’s nightly snapshot that restores the whole server and nothing smaller.

By the end of this you will have a database export taken by hand, stored somewhere sensible, and proved by restoring it into a spare database rather than hoping. I cover three routes and you need only one: a point-and-click route through your hosting panel, and two command line routes for people who have that option. Route A needs no code at all.

Before you start

You need one of three things. A hosting panel with phpMyAdmin or Adminer, which are web pages that let you look inside the database and export it, with nothing to install. Or SSH access, a text-only command line on the server reached with Terminal on a Mac or PuTTY on Windows. Or just admin access to WordPress, plus a plugin. SSH is fastest and most reliable, so if your host offers it and you have never used it, this is a good reason to start. If it does not, Route A does the whole job.

You also need the database details, which are in wp-config.php in the site root, the top-level folder that also holds wp-admin and wp-content and is usually called public_html. Open it through your hosting panel’s File Manager. You are only reading, not changing anything:

define( 'DB_NAME', 'example_wp' );
define( 'DB_USER', 'example_wpuser' );
define( 'DB_PASSWORD', '...' );
define( 'DB_HOST', 'localhost' );   // sometimes host:port, or a socket path
$table_prefix = 'wp_';              // not always wp_, check before assuming

Nothing here changes your site, with one exception. Importing a .sql file means loading a saved export back in, and doing that over a live database wipes out whatever is in the tables it covers. That is the step that destroys data if you get the target wrong. Never test a restore against the database your site is currently using. Later I show you how to make an empty spare database to test against instead, which costs nothing and removes the risk.

Step 1: Know what is in the database, and what is not

This is the part people get wrong, and it is the difference between a recoverable site and a bad afternoon. A WordPress site is two halves: files on the server, and a database holding everything you typed. They are backed up in different ways.

The database holds your content and settings: posts, pages, custom post types and their fields, categories and tags, users and their hashed passwords (scrambled versions that cannot be read back), comments, menus, widgets, theme options, plugin settings, WooCommerce orders and customers, form entries. If it was typed into WordPress, it is in there.

The database does not hold your files. Not uploads, so every image, PDF and video in wp-content/uploads sits outside it and only the path is stored. Not themes or plugins, only a record of which are active. Not wp-config.php, .htaccess or anything in mu-plugins.

So a database backup is not a site backup. It is the half that is hardest to recreate and the likeliest to be wrecked by a bad update or a bad query, which is why it is worth taking on its own before a risky change. A full backup needs both halves. The files half is just copying folders, which your File Manager does by making a zip, or this does in one line over SSH:

# The files half. Run this from the folder above the site root.
tar -czf content-$(date +%F).tar.gz example.co.uk/wp-content example.co.uk/wp-config.php

Database plus wp-content plus wp-config.php, and you can rebuild the site anywhere.

Route A: phpMyAdmin, when there is no SSH

This is the route most people should take, and it is ticking boxes rather than typing. Exporting only reads the database, so nothing here can damage your site.

phpMyAdmin is in most UK hosting panels, under Databases. Log in, click your database name in the left sidebar (check it against the DB_NAME you read earlier, because shared accounts often hold several and the wrong one gives you a backup of another site), then open the Export tab.

Choose Custom, not Quick. Quick uses defaults that are wrong for a backup you may need to restore over the top of an existing site. Then set:

• Output: “Save output to a file”, Compression gzipped. A .sql file compresses very well, and a smaller file is easier to get back in through phpMyAdmin’s upload limit later.
• Character set of the file: utf-8, and Format: SQL.
• Dump table: structure and data. Structure only gives empty tables; data only gives rows with nothing to put them in.
• Object creation options: tick “Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement”. This is the important one and it is off by default. Without it, importing into a database that still holds the old tables fails with “Table already exists” and leaves you half restored.
• Leave “Add CREATE DATABASE / USE statement” unticked, or the file insists on a database name, which will be the wrong one the day you restore to a new host.

Click Go and you get a .sql.gz download. That is your backup: a plain text file of instructions for rebuilding the database, squashed down with gzip compression.

Then check it, because phpMyAdmin fails quietly. The export runs through PHP, and PHP is only allowed to run for so long before the server cuts it off. On a large site it can stop partway and still hand you a file that looks fine. Unzip it, open it in a text editor and scroll to the bottom. A complete export ends with tidy comment lines. A truncated one stops mid-sentence, usually partway through an INSERT, the command that puts a row of data back. Comparing sizes is quicker still: the Structure tab shows the database size, and a download far smaller than that is truncated, which is your cue to use a route below.

Route B: mysqldump over SSH, the one I actually use

If you have SSH, this is the right answer. mysqldump comes with MySQL and does one job: it writes the whole database out to a file. It is faster, it does not run through PHP so nothing cuts it off partway, and it does not care how big the database is. With no SSH, skip this section; Route A gives you the same file.

# --password with no value prompts, so it never lands in your shell history.
mysqldump \
  --host=localhost \
  --user=example_wpuser \
  --password \
  --single-transaction \
  --quick \
  --default-character-set=utf8mb4 \
  --no-tablespaces \
  --routines --events \
  example_wp | gzip > ~/backups/example_wp-$(date +%Y%m%d-%H%M%S).sql.gz

The flags are the options after each --, and they are not decoration. Each one prevents a specific failure:

• --single-transaction takes one consistent snapshot at a single moment, so an order arriving mid-export cannot leave you with half of one, and it does that without locking anything. mysqldump’s defaults instead include --lock-tables, which blocks writes for the whole export. On a live shop that is checkout failing while you back up. It works on InnoDB, the modern way MySQL stores tables and the default for many years, which your site almost certainly uses. Only InnoDB tables are dumped consistently this way, so any MyISAM table left behind by an old plugin can still change mid-export, which is worth checking with SHOW TABLE STATUS on a site you have inherited.
• --quick streams rows out instead of buffering a whole table in memory. It matters the moment a table gets large.
• --default-character-set=utf8mb4 is your emoji and accented-character insurance. A character set is the agreed list of which letters and symbols exist, and utf8mb4 is the one covering everything. Without it the two ends can fall back to an older, narrower set called latin1 and mangle text on the way out.
• --no-tablespaces avoids “Access denied; you need (at least one of) the PROCESS privilege” on MySQL 8, a permission almost no shared hosting user has.
• --routines --events includes stored procedures and scheduled events, bits of logic saved inside the database itself. Most WordPress sites have none. If --events gives you “Access denied; you need (at least one of) the EVENT privilege(s)”, your database user does not hold that privilege, and dropping the flag is the fix.

Two more for when an import complains. --set-gtid-purged=OFF keeps some MySQL 8 bookkeeping out of the file, and --column-statistics=0 fixes “Unknown table ‘COLUMN_STATISTICS’ in information_schema”, which appears when a MySQL 8 client exports from MariaDB (a close cousin of MySQL that many hosts run instead).

Never write the password inline as -pSecret. It lands in your shell history, the list of commands the server keeps, and is visible to other users on that server. Use --password alone, which makes it ask, or a ~/.my.cnf with permissions set to 600 so only you can read it.

Route C: wp db export, if WP-CLI is available

WP-CLI is WordPress’s official command line tool, and some hosts install it for you. Run wp --info over SSH to see. It wraps mysqldump and reads the credentials from wp-config.php, which removes the most common typo.

wp db export ~/backups/db-$(date +%Y%m%d-%H%M%S).sql

# Straight to a compressed file, with no large .sql on disk in between.
wp db export - | gzip > ~/backups/db-$(date +%F).sql.gz

# Skip tables you genuinely do not need. Action Scheduler logs get enormous.
wp db export --exclude_tables=wp_actionscheduler_logs

# Restore.
wp db import ~/backups/db-20250414-101500.sql

Anything mysqldump understands passes straight through, so --single-transaction --quick work as above. mysqldump enables --opt by default, which already includes DROP TABLE statements, so a wp db export file replaces tables on import rather than colliding with them.

Run wp db size --tables --format=table first. It tells you which table is responsible for the size of your database, and the answer is usually a log table a plugin never cleans up. More on the commands I use daily in WP-CLI commands worth knowing.

Why a backup you have never restored is not a backup

The file is not the backup. The restore is the backup. Until you have put one back somewhere and looked at it, what you have is a file you hope is complete, and that is as true of a mysqldump as it is of anything automated.

Unattended backups are the ones worth checking hardest, because they fail quietly. A job running through PHP can hit the time limit and write a half-finished archive that still appears in the list as a success. Anything scheduled through WP-Cron stops firing on a site nobody visits overnight, because WP-Cron only runs when someone loads a page, so the newest copy can be months older than it looks. Remote storage tokens expire and the notice goes to an inbox nobody reads. Archives left in wp-content/uploads get taken out by the same hack that took out the site. None of that shows on a dashboard, which is why the check in the next section matters more than which tool produced the file.

Host backups deserve the same question. Ask how far retention goes, whether they can restore one site rather than the whole server, and whether they can restore the database without rolling the files back with it. Knowing the answers before you need them is a fair part of what a maintenance plan is for.

How to check it worked

Two levels. Do the first every time, and the second at least once for any site that matters. The quick check looks at the file itself, not the confirmation message. These commands need SSH; without it, do the eyeball check from Route A instead.

gzip -t backup.sql.gz && echo "archive is intact"   # gzip integrity check
zcat backup.sql.gz | tail -3                        # should end cleanly, not mid-INSERT
zcat backup.sql.gz | grep -c "^CREATE TABLE"        # roughly your table count

A complete mysqldump ends with a -- Dump completed on comment. The real check is restoring it somewhere else, meaning a scratch database: a brand new empty one created purely for this test, which your site knows nothing about. Creating it is safe and you delete it afterwards. In a hosting panel you make one under MySQL Databases and import through phpMyAdmin. Over SSH:

# A scratch database. Nothing here touches the live one.
mysql -u example_wpuser -p -e "CREATE DATABASE restore_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

zcat backup.sql.gz | mysql -u example_wpuser -p restore_test

mysql -u example_wpuser -p restore_test -e "
  SELECT COUNT(*) AS posts FROM wp_posts;
  SELECT option_value FROM wp_options WHERE option_name = 'siteurl';"

If the post count looks like your site and siteurl is your domain, the data really is in there. On anything commercial, go further: point a staging copy at restore_test, log in, and open a page built with a page builder and an order or form entry.

Do not shortcut this by importing into the live database “just to see”. The DROP TABLE statements you ticked earlier run first, deleting each table before rebuilding it. If the file is truncated, the deletions succeed and the rebuilding does not, and you have destroyed the live site with the backup you were testing.

Where to keep them, and how often

Keep them off the server and out of the web root. A backup on the same disk as the site is a second copy of the same risk, so download it or push it somewhere the site’s own credentials cannot reach. A .sql file left in public_html is downloadable by anyone who guesses the name, and it holds every password hash, email address and customer record on the site: a personal data breach under UK GDPR, caused by a file you created to be safe.

How often depends on how much work you are willing to redo. A brochure site that changes monthly is fine weekly; a shop taking orders wants daily at least, because every hour of gap is real orders you cannot recover. Whatever the schedule, take a manual export immediately before any update, migration or bit of SQL. And keep more than one: a single rolling backup that overwrites itself is one bad night away from being a perfect copy of a broken database.

The in-admin route: ABCode Database Exporter

All of the above assumes a command line or a tolerable phpMyAdmin. Plenty of sites have neither: a budget plan with no SSH, a phpMyAdmin that is slow or buried three menus deep, and you only want a .sql file before you press Update.

That gap is why I built ABCode Database Exporter, a free plugin on wordpress.org. You press a button in the admin and the file is in your hands, so you can run the restore test above on it that same afternoon, which is worth more than a schedule nobody has ever checked.

• Exports the whole database, or only the tables you tick, to a file you download from the admin.
• phpMyAdmin-compatible output, so it imports back through phpMyAdmin, mysql or WP-CLI without editing anything.
• A progress bar shows which table is being written, so a stall is visible rather than something you find in the file afterwards.
• Single tables can come out as .sql or .csv, with optional gzip.

The limits matter more than the features. It exports the database only: not wp-content, not your uploads, and it is no replacement for a full-site backup plugin, so read Step 1 again, because a database export on its own will not rebuild your site. It is manual by design rather than an unattended nightly routine, and on a very large database with SSH available, mysqldump streaming straight to disk will always be quicker. What it covers is the case most sites are in: the export you want right now, before you press Update, from a site where the terminal was never an option.

When it does not work

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql

Common questions

The habit that makes this worth it

The mechanics take ten minutes to learn. phpMyAdmin with DROP TABLE ticked and gzip on, mysqldump with --single-transaction --quick --default-character-set=utf8mb4, or wp db export. Any of the three produces a real file. What separates people who recover from people who lose a week is the two habits either side of it: taking a fresh export immediately before anything risky, and having restored one at least once, so you know the file is good and you know the commands under pressure. A restore practised on a quiet Tuesday is a very different thing from one you are attempting for the first time with a client on the phone.

If you have inherited a site and cannot tell whether its backups are real, that is worth an hour of someone’s time before you need the answer. I do that as part of ongoing maintenance, and as a one-off when a site is already in trouble and needs fixing. Either way, get in touch and I will tell you plainly what you have got.