Testing against real data finds problems that test data never will. It also puts every customer name, address and order on a server that is protected less carefully - so the copy is made deliberately, not with a plain dump.

What to remove

  • Payment details and tokens - even the last four digits.
  • Password hashes, replaced with one known value so you can log in as anyone.
  • API keys and webhook secrets stored in settings tables.
  • Anything you would have to report if it leaked.

What to rewrite

Email addresses above all. A test that sends a hundred order confirmations to real customers is the single most common staging accident, and it is not recoverable.
UPDATE users SET
  email = CONCAT('user', id, '@example.invalid'),
  phone = NULL,
  password_hash = '$2y$10.test.hash';

example.invalid can never be delivered to - it is reserved for exactly this.

Do it in one pass

mysqldump --single-transaction live_db | \
  gzip > /tmp/live.sql.gz
zcat /tmp/live.sql.gz | mysql staging_db
mysql staging_db < scrub.sql   # the UPDATEs above
rm /tmp/live.sql.gz

Keep the scrub as a file in the repository, so the next person does the same thing and nobody has to remember the list.

Then the URLs

wp search-replace 'https://yourdomain.com' 'https://staging.yourdomain.com' --all-tables --precise

Skip what you do not need

mysqldump --single-transaction --ignore-table=live_db.sessions --ignore-table=live_db.audit_log live_db

Log and session tables are often most of the size and none of the value.

If the data is sensitive enough that scrubbing makes you nervous, do not copy it. Generate a small realistic set instead and keep it in the repository.