Looking After a WordPress Database: Backups, Cleanup and Search-Replace Done Safely

Most of a WordPress site lives in about a dozen tables. Posts, pages and media records are in wp_posts, custom fields in wp_postmeta, settings in wp_options, users and their roles in wp_users and wp_usermeta. My old notes had a long section of raw SQL for all of it, from creating posts to bulk-inserting users.

Reading those notes again, the SQL for reading holds up fine. The SQL for writing is where the trouble is. Here's how I'd look after a database now. Checked against the WP-CLIWP-CLIThe command-line tool for WordPress. Anything you'd click through in wp-admin, from updates to new users, you can type or script instead.More: WP-CLI: The Commands I Keep Coming Back To docs in September 2026.

Back it up first, every time

wp db export --add-drop-table

With no filename it writes dbname-date-hash.sql in the current folder. --add-drop-table means the file can be imported straight over the top of a broken database, and --exclude_tables lets you skip a giant log table if a plugin keeps one. For a scheduled backup from Windows, with the files zipped alongside, I use a PowerShell script.

Warning

A backup you've never restored is a guess. Import one into a scratch database now and then, just to prove it works.

Cleanup that won't hurt

Job	Command
Clear expired transients	wp transient delete --expired
Empty the spam	wp comment delete $(wp comment list --status=spam --format=ids) --force
Delete old revisions	wp post delete $(wp post list --post_type=revision --format=ids) --force
Tidy the tables	wp db optimize
Check for damage	wp db check

Revisions are the big one on older sites. Every save keeps a full copy of the post. To stop them piling up again, cap them in wp-config.php:

wp config set WP_POST_REVISIONS 5 --raw

My old notes also had DELETE statements for orphaned metadata and auto-drafts. They'd work, but WP-CLI goes through WordPress's own functions, so caches and related records get handled too. I'd only drop to raw SQL for reporting, like "posts per category" or "which posts have a price field."

Why search-replace is special

When you move a site to a new domain, or from http to https, the old URL is baked into the database in thousands of places. The obvious fix is SQL:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://old.example.com', 'https://example.com');

My notes had exactly that, for wp_options, wp_posts and wp_postmeta. It's the classic way to break a WordPress site slowly.

The reason is serialized data. WordPress stores arrays, like widget settings and theme options, as text that records the length of every string:

s:23:"http://old.example.com/";

That 23 is the character count. Swap in https://example.com/ and the string is 20 characters, but the label still says 23. PHP can't read the value anymore, so WordPress quietly treats it as empty. Widgets vanish, the theme forgets its settings, and nothing tells you why.

wp search-replace unpacks serialized values, swaps the text, and packs them back up with the right lengths:

wp search-replace 'http://old.example.com' 'https://example.com' --dry-run
wp search-replace 'http://old.example.com' 'https://example.com' --skip-columns=guid --report-changed-only

Run the dry run first and read the counts. If a table you didn't expect shows thousands of changes, stop and look.

Tip

Skip the guid column. Post GUIDs are meant to be permanent IDs, and feed readers use them to tell which posts they've already seen. Change them and every subscriber gets your whole archive again.

A few more flags worth knowing:

• --all-tables includes tables without the WordPress prefix, which some plugins create.
• --precise forces the slower PHP pass on every row. Use it if a dry run misses something you know is there.
• --export=moved.sql writes the changed database to a file and leaves the live one alone. It's handy for building a copy for the new host.

Reading is fine

None of this means stay out of the database. A SELECT never hurt anyone, and questions like "which posts have no featured image" or "how many subscribers signed up this year" are quicker in SQL than anywhere else. wp db query runs it for you with the site's own credentials, so you don't need a separate login.

The rule I'd pin to the monitor: reads in SQL, writes through WordPress. If you need writes from a script on another machine, the REST API goes through WordPress too. And for the everyday commands around all of this, there's the WP-CLI short list.