Refreshing the staging database from production

Latest data, but not live data

A staging environment that mirrors production in structure but not in content is useless: the codes under test behave differently against old rows, and a migration or a plugin change is only ever tested against stale dates. Refreshing staging from production is the repeatable job of pulling a fresh dump, applying it to the staging database, and making it safe to work on. The awkward part is the scrubbing step, because production data left in staging is a privacy and security leak when staging is on the open internet.

The three-step refresh

1. Export from production, with structure and data: mysqldump --single-transaction --quick (keep the --single-transaction, so the download is consistent) or the host's own export.
2. Apply to staging, replacing whatever is there: drop the staging tables (or the whole staging DB) and import the dump.
3. Scrub and adjust for the staging context, then test.

The restore procedure, the general backup order, and the database search-replace article all cover the transfer halves; this article is the part the standard copies forget: the scrubbing and the repeatability.

The scrub is the whole point

Before anything hits staging:

• Anonymise personal data. Customer emails, phone numbers, addresses and passwords (the password hashes can stay, but never copy real customer PII into a non-production environment). Replace emails with a deterministic transform (e.g. concat(id, '@test.example')) so joins still work.
• Strip payment artifacts. Tokenised cards, gateway keys, and stored billing references must not exist anywhere outside the payment tree. Leave only the table structure.
• Remove the real media. The uploads folder is a mirror of the media that the database references; either copy the folder or map it, but do not copy sensitive customer documents (e.g. ID scans) if the media library holds them.

Two things most refresh scripts miss: ids must stay stable, so the test environment can reproduce the same user in the UI and the API, and transform everything deterministic, so a refresh on Monday goes back to the same users.

Handle the environmental differences

After the scrub, the import usually needs three adjustments:

The two-environment split (a staging DB that connects to a live IDP, or a staging email that sends real mail) is the classic half-decoupled failure; the audit checklist in the staging-to-live article frames this as "the mixed items to fix before launch".

Script it or it snowballs

A refresh that is manual is a refresh that happens at the worst moment (mid-deploy, under pressure). Script the whole flow:

# 1. export
ssh prod 'mysqldump --single-transaction -u app app > prod.dump'
# 2. scrub sensitive columns
sed -E 's/(email =).*/\1 "test-1@example"/' prod.dump > staging.dump
# 3. import to staging
ssh staging 'mysql app < staging.dump'
# 4. fix URLs and env
ssh staging 'wp search-replace https://example.com https://staging.example.com'
ssh staging 'wp config set SMTP_HOST mailtrap.local'

Mind the trap: a raw sed across a SQL dump can mangle binary fields (a serialized array with a length that no longer matches becomes a corrupted row). The safe pattern per project is a database tool or WP-CLI's search-replace with --precise, the same guard that the WordPress restore steps walk through. Keep the script in the repo, run it with one command, and have it prune itself (delete dumps older than N days on staging, alert on a dump that hits a new size).

The staging environment caveat

Staging databases are a second customer-data surface. If the staging URL is on the public internet, any unchanged customer name or email that survives the scrub appears in the browser and search engine caches. That is why the scrub is the deliverable, not the refresh. When the site later goes from staging to live, the whole promotion order applies again over the scrub result.

Verify

After the refresh, log in to staging with a test identity, hit pages that load the scrubbed rows, confirm outbound email goes to the sink, and confirm the API calls use test credentials, not production ones. That is a short walk that catches the two most common leaks (real email and real tokens) before any client sees them.