+1 (276) 265-7197
StackPioneer

A Staging Copy of Production That Won't Leak Customer Data

Every upgrade we do starts the same way: restore last night's backup somewhere else and rehearse the change against it. On about half the applications we pick up, that somewhere else does not exist. There is production, there is a developer laptop with a database from 2021, and there is a shared "test" box that drifted out of sync three years ago and still sends real email to real customers.

That is why upgrades get rehearsed in production. The fix is not a platform project. It is a restore script, an anonymization script, and a cron entry. A couple of days of work, and every future upgrade, index change, and PHP bump gets a dress rehearsal.

Start from the backup, not from production

Build staging by restoring the nightly backup — not by dumping the live primary. Two reasons, and the second is the better one:

  1. A dump of a busy primary holds metadata locks and burns I/O you need for customers.
  2. Restoring the backup is a backup-restore drill. If the restore fails, you have found out on a Tuesday afternoon instead of during an outage.

So the refresh job is: pull last night's artifact from wherever it lives, restore it into the staging MySQL instance, sanitize, then sync the code and the uploads directory.

# on the staging host
aws s3 cp s3://acme-backups/mysql/$(date +%F).sql.gz /var/tmp/
gunzip -c /var/tmp/$(date +%F).sql.gz | mysql --force staging_db
mysql staging_db < /opt/staging/sanitize.sql

Time the restore and write the number down. "Our 180 GB database restores in 2 hours 40 minutes" is a fact you want on paper before you need it, and it is the same number that sets your recovery time objective.

Sanitize with SQL you can read

Resist the urge to buy an anonymization product first. For most legacy schemas, the personal data lives in five to fifteen columns across three or four tables, and a single sanitize.sql that any engineer can read in two minutes beats a tool nobody can audit.

The pattern that holds up: overwrite in place, keep the shape of the data, keep it deterministic per row so the app still behaves.

UPDATE users SET
  email      = CONCAT('user', id, '@staging.invalid'),
  first_name = CONCAT('First', id),
  last_name  = CONCAT('Last', id),
  phone      = CONCAT('555010', LPAD(id, 4, '0')),
  password   = '$2y$10$ZxK...fixedhashforpassword',
  remember_token = NULL,
  api_token  = NULL;

UPDATE addresses SET
  line1 = CONCAT(id, ' Example Street'),
  line2 = NULL,
  postcode = '00000';

TRUNCATE TABLE password_resets;
TRUNCATE TABLE sessions;
TRUNCATE TABLE personal_access_tokens;
UPDATE payment_methods SET card_last4 = '0000', gateway_token = NULL;
DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 30 DAY;

Four things that are easy to miss and cause real incidents:

  • The .invalid TLD is reserved (RFC 2606) and can never resolve. Use it so that a stray mail attempt bounces instead of reaching someone.
  • One known password hash for every user. Set it to a bcrypt hash of a password your team shares. Testers can log in as any customer, and nobody keeps a copy of real credentials.
  • Kill the tokens. API keys, remember-me tokens, OAuth refresh tokens and webhook secrets in the database are credentials; a staging box is not where they belong.
  • Free-text columns leak. Support ticket bodies, order notes, and comments tables routinely contain phone numbers and card digits typed in by humans. Blank them or replace them with lorem text; do not assume a notes column is harmless.

Then verify the sanitizer rather than trusting it. A short check that fails loudly is enough:

SELECT COUNT(*) FROM users WHERE email NOT LIKE '%@staging.invalid';
-- must be 0, or the refresh aborts

Run that as the last step of the refresh job and have a non-zero result exit non-zero. Schemas change; a new billing_contacts table will appear one day and your sanitizer will not know about it. The check is what tells you.

Make the environment incapable of touching the outside world

Anonymized data is half of it. The other half is that staging must not be able to email customers, charge cards, or push to the warehouse — even if someone runs the wrong job by hand.

  • Mail: point PHP at a catch-all. MailHog or Mailpit on localhost, sendmail_path or the app's SMTP host set to it. Every message is captured and viewable; none leave the box. Do not rely on an application config flag — if the host can reach port 25 or your ESP's API, someone will eventually get through it.
  • Payments and third-party APIs: sandbox keys only, and keep the production keys out of the staging environment file entirely. If the vendor has no sandbox, block the hostname at the firewall or /etc/hosts it to 127.0.0.1 and let the calls fail visibly.
  • Cron and queue workers: default to disabled. Turn on the specific job you are testing. A legacy app's nightly invoice run will happily invoice sanitized customers through a live gateway if you let it.
  • Search engines and humans: HTTP basic auth on the whole vhost, plus X-Robots-Tag: noindex. A staging copy indexed by Google is a duplicate-content problem and an information disclosure at the same time.
  • Make it obvious: a loud banner injected by a staging-only config, and a different favicon. People mistake one browser tab for another; it costs nothing to make that impossible.

Match the parts of production that change behaviour

Staging does not need production's hardware, and paying for an identical server is usually waste. It does need production's versions: same PHP minor and extension list, same MySQL version and SQL mode, same Apache MPM and PHP-FPM layout, same OS. A rehearsal on PHP 8.2 tells you nothing useful about a production box running 7.4.

Two differences worth being deliberate about:

  • Data volume. If you shrink the dataset to save disk, you lose the ability to time migrations and spot queries that only get slow at scale. Keep the full dataset if you can afford the disk; if you cannot, keep the largest tables whole and trim history tables, and know that your migration timings are now optimistic.
  • sql_mode and character set. These silently change what the application accepts. Copy them exactly: SELECT @@sql_mode, @@character_set_server; on production, pin the same values in the staging my.cnf.

Put it on a schedule

A staging environment refreshed by hand becomes stale in a month, and a stale staging environment is worse than none because people trust it. Run the whole thing — fetch, restore, sanitize, verify, sync uploads — weekly from cron, log the output, and alert on failure.

0 3 * * 0 /opt/staging/refresh.sh >> /var/log/staging-refresh.log 2>&1

The payoff compounds. Every PHP upgrade gets rehearsed against real data shapes. Every schema change gets timed before it runs for customers. Every restore gets tested 52 times a year instead of never. And when something breaks in production at 2 a.m., there is a current copy of the system sitting right there to reproduce it on, with none of the customer data that would make a debugging session a notifiable event.

If you are about to upgrade PHP or MySQL and the honest answer is that there is nowhere to rehearse it, build this first. It is the cheapest part of the project and it is the part that makes the rest reversible.