Exporting a WordPress database from a live server is straightforward — phpMyAdmin does it in a few clicks and hands you a .sql file.
What’s less obvious is that the file you get is tied to the server it came from, and you can’t just import it somewhere else and expect it to work.
Here’s what needed changing before the dump was usable locally, and why.
The URLs
WordPress stores the site’s URL in the database and references it throughout — in content, in settings, in serialised option values.
A database exported from calebwhitefield.co.uk has that domain baked in everywhere.
Importing it locally and visiting caleb-local.local would result in a site that tries to load assets, links, and resources from the live server.
The fix is a search-and-replace: swap every occurrence of the live URL for the local one.
Simple enough in principle, but in practice the URL appears in several different formats within the same file:
- Plain:
https://calebwhitefield.co.uk - HTTP variant:
http://calebwhitefield.co.uk - Escaped for JSON:
https:\/\/calebwhitefield.co.uk - Double-escaped (as MySQL stores it in string literals):
https:\\/\\/calebwhitefield.co.uk - URL-encoded:
https%3A%2F%2Fcalebwhitefield.co.uk
The double-escaped form is the one that catches people out.
When WordPress stores JSON in the database, it escapes the forward slashes.
MySQL then escapes the backslashes again when writing them to the export file.
The result looks different in the raw file from what it looks like in the database, and a simple find-and-replace will miss it.
All five formats needed replacing.
Miss one and you’ll have a site like I did, with mysterious broken references and no obvious error to diagnose.
The table prefix
WordPress uses a prefix on all its database table names — wp_commentmeta, wp_posts, and so on.
The prefix can be changed for any number of reasons, and this site’s live database was using a custom one (quietly applied automatically by the hosting provider) rather than the default wp_.
LocalWP uses wp_ by default, so the dump needed a prefix rename: 153 occurrences across table names, meta keys, and option names.
Straightforward, but also worth being precise about — not every occurrence of the old prefix in the file is necessarily a table name.
Host-specific tables
The live site runs on SiteGround, which installs its own database tables for caching and security logging.
These tables don’t exist in a standard WordPress installation and serve no purpose locally.
Removing them keeps the dump clean and avoids import errors on a fresh install.
Documenting what changed
A SQL dump is just a text file, and like any text file it can have comments.
I added a header block at the top documenting where the dump came from, what changes were made, and what it’s intended for.
That way, anyone (including future me) who picks up the file knows exactly what they’re looking at without having to reverse-engineer it.
The result
A .sql file that can be imported into any standard local WordPress installation and produce a working copy of the site.
No live server references, no host-specific cruft, no mystery.
Part of a series — back to the overview
Leave a Reply