Sanitize a Magento 2 Database for Staging and Local Dev

Developers need realistic data. A staging store with five test products does not show the slow category page, the configurable product with three hundred children or the checkout bug that only happens with a specific shipping rule. So production databases get copied to staging and to laptops, and with them go customer names, addresses, phone numbers, order history and every API credential stored in configuration.

That copy is a data protection problem under GDPR and similar laws, and it is also an operational risk. A staging store with production settings can send real emails to customers, push test orders to the ERP or capture payments. Sanitizing the database fixes both. This article shows what to change and how to automate it.

Where the work happens

Sanitize before the data leaves a controlled environment. The safest flow is:

1. Dump production on the production network.
2. Import it into a temporary database on a server you control, not a developer laptop.
3. Run the sanitize script there.
4. Dump the sanitized database. That file is what goes to staging and to developers.

Running the script after the raw dump has already been copied to five laptops defeats the purpose. The dump and import commands themselves are covered in the backup and restore guide.

Strip or anonymize

There are two approaches, and most projects use both.

Strip means exporting the table structure without the rows. It fits tables nobody needs for development: sessions, logs, reports, visitor data, password reset requests. n98-magerun2 supports this directly with db:dump --strip, which accepts table names and predefined groups. Run n98-magerun2 db:dump --help to see the groups your version defines.

n98-magerun2 db:dump --compression=gzip \
    --strip="customer_visitor customer_log persistent_session password_reset_request_event oauth_token" \
    staging.sql.gz

Anonymize means keeping the rows but replacing personal data with fake values. It fits customers and orders, because developers need them to reproduce bugs in the account area, order grid, invoices and integrations.

Anonymizing customers

Use deterministic values based on the entity ID. They are unique, stable across refreshes and easy to recognize as fake.

UPDATE customer_entity SET
    email = CONCAT('customer', entity_id, '@example.com'),
    firstname = 'Test',
    lastname = CONCAT('Customer', entity_id),
    middlename = NULL,
    dob = NULL,
    taxvat = NULL,
    password_hash = NULL,
    rp_token = NULL,
    rp_token_created_at = NULL;

UPDATE customer_address_entity SET
    firstname = 'Test',
    lastname = CONCAT('Customer', parent_id),
    company = NULL,
    street = CONCAT(entity_id, ' Test Street'),
    telephone = '0000000000',
    fax = NULL,
    vat_id = NULL;

UPDATE newsletter_subscriber SET
    subscriber_email = CONCAT('subscriber', subscriber_id, '@example.com');

Clearing the password hash means nobody can log in as these customers with a password. Developers who need to see a customer account can use the Login as Customer feature from the admin, which ships with Magento 2.4, or reset a single test account.

Keep city, region, postcode and country if shipping rules and tax calculation depend on them. Those fields alone rarely identify a person, and changing them breaks the reason you copied real data in the first place.

Anonymizing orders and quotes

Orders carry their own copy of customer data, separate from customer_entity. Changing the customer does not change past orders.

UPDATE sales_order SET
    customer_email = CONCAT('customer', entity_id, '@example.com'),
    customer_firstname = 'Test',
    customer_lastname = CONCAT('Order', entity_id),
    customer_middlename = NULL,
    customer_dob = NULL,
    customer_taxvat = NULL,
    remote_ip = NULL,
    x_forwarded_for = NULL;

UPDATE sales_order_address SET
    firstname = 'Test',
    lastname = CONCAT('Order', parent_id),
    email = CONCAT('customer', parent_id, '@example.com'),
    company = NULL,
    street = CONCAT(entity_id, ' Test Street'),
    telephone = '0000000000',
    vat_id = NULL;

UPDATE sales_order_grid SET
    customer_email = CONCAT('customer', entity_id, '@example.com'),
    customer_name = CONCAT('Test Order', entity_id),
    billing_name = CONCAT('Test Order', entity_id),
    shipping_name = CONCAT('Test Order', entity_id),
    billing_address = NULL,
    shipping_address = NULL;

UPDATE quote SET
    customer_email = CONCAT('quote', entity_id, '@example.com'),
    customer_firstname = 'Test',
    customer_lastname = CONCAT('Quote', entity_id),
    remote_ip = NULL;

UPDATE quote_address SET
    firstname = 'Test',
    lastname = 'Quote',
    email = CONCAT('quote', quote_id, '@example.com'),
    street = CONCAT(address_id, ' Test Street'),
    telephone = '0000000000';

The invoice, shipment and credit memo grids also hold names and addresses. Apply the same kind of update to sales_invoice_grid, sales_shipment_grid and sales_creditmemo_grid, or truncate them and rebuild with the grid async indexing if you use it.

Also check sales_order_payment.additional_information, which payment modules use to store gateway responses, and any table added by your extensions. Reviews (the nickname in review_detail), wishlists shared by email, gift messages and custom checkout fields can all hold personal data. Search your schema for columns named email, phone, name, address or ip.

SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND (COLUMN_NAME LIKE '%email%'
    OR COLUMN_NAME LIKE '%phone%'
    OR COLUMN_NAME LIKE '%telephone%'
    OR COLUMN_NAME LIKE '%street%'
    OR COLUMN_NAME LIKE '%remote_ip%')
ORDER BY TABLE_NAME;

After the updates, reindex the customer grid with bin/magento indexer:reindex customer_grid so the admin customer list shows the new values.

Neutralizing configuration

This step prevents real side effects. A staging store should never send an email to a real customer or talk to a production payment account.

UPDATE core_config_data SET value = 'https://staging.example.com/'
WHERE path IN ('web/unsecure/base_url', 'web/secure/base_url');

DELETE FROM core_config_data WHERE path IN ('web/cookie/cookie_domain');

TRUNCATE TABLE oauth_token;

Then set values through the CLI on the staging server, which also writes them to the right scope:

bin/magento config:set system/smtp/disable 1
bin/magento config:set design/search_engine_robots/default_robots "NOINDEX,NOFOLLOW"
bin/magento config:set google/analytics/active 0

For multi website stores, check the base URLs on each website scope as well. Payment methods, shipping carriers, ERP connectors and marketplace integrations each have their own configuration paths. List them per project and switch each one to sandbox mode or disable it. Pay special attention to integrations that run from cron, because they start working as soon as cron is enabled. If you send orders to an ERP, staging must point to the ERP test system or nowhere. The patterns are in the order export guide.

Admin users and access

The admin_user table holds names and email addresses of staff, and password hashes that someone could try to crack offline. On staging, keep only the accounts of the people who work on that environment. Delete the rest, or deactivate them and replace their email addresses with fake ones.

UPDATE admin_user SET
    email = CONCAT('admin', user_id, '@example.com'),
    is_active = 0
WHERE username NOT IN ('dev.anna', 'dev.mark');

Remove integrations that point to production systems, or at least revoke their tokens, which the TRUNCATE TABLE oauth_token above already does. Protect the staging site itself with HTTP basic authentication or an IP restriction at the web server, so search engines and strangers cannot reach it even if the robots setting is lost on the next refresh.

What to keep untouched

Sanitizing too much makes the copy useless. Leave the catalog alone: products, categories, attributes, prices, stock and images are business data, not personal data, and they are the reason performance problems show up on staging. Keep order totals, items, statuses, payment method codes and shipping methods, so reports, invoices and integrations behave as in production. Keep cart price rules and coupon codes unless they were issued to named people. The goal is to remove what identifies a person or gives access to a system, and nothing else.

The encryption key question

Production secrets in core_config_data are encrypted with the key in production's env.php. If staging uses the same key, anyone with staging access can decrypt production payment and API credentials. Give staging its own encryption key, and overwrite the encrypted fields with sandbox credentials entered through the admin or config:set. Values encrypted with the old key will simply fail to decrypt on staging, which is what you want.

Automate it

A sanitize process that depends on someone remembering steps will be skipped. Put the SQL in a versioned file in the repository, wrap the dump, import, sanitize and export in one script, and run it on a schedule or from CI. Add a final check that fails the script if any customer email outside example.com remains:

SELECT COUNT(*) FROM customer_entity WHERE email NOT LIKE '%@example.com';

Developers then pull the latest sanitized dump into their local environment. My setup for that is in the Docker and Warden guide.

FAQ

Is it legal to copy a production Magento database to staging?

Copying personal data to more systems and people raises GDPR and similar obligations. Anonymizing customer and order data before the copy leaves production removes most of that risk.

How do I stop staging from sending emails to customers?

Set system/smtp/disable to 1 with bin/magento config:set and anonymize email addresses. Do both, because a module may send mail through its own transport.

Should staging use the same encryption key as production?

No. With the same key, staging can decrypt production secrets. Use a new key and enter sandbox credentials on staging.

Can n98-magerun2 anonymize data?

Its db:dump command can strip table data, which removes rows but keeps structure. For anonymizing rows you keep, run your own SQL or use a dedicated anonymization tool.

I am available for freelance Magento and Adobe Commerce work, including staging and development workflows. If you need help with this, get in touch.