Convert a production database into test data

1) DB Dump

When you do the export, you can export only the structure for the following tables:

core_cache
core_cache_option
core_cache_tag
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
enterprise_logging_event
enterprise_logging_event_changes
index_event
index_process_event
report_event
report_viewed_product_index
dataflow_batch_export
dataflow_batch_import

Also core_url_rewrite can be imported only with the structure and run a Catalog URL Rewrites reindex after the import, unless you need all those records (for various tests).

You can also clean abandoned carts (hint: sales_flat_quote), you can also remove orders if you don't need them and just keep a limited number

2) Config Settings

  • web/(unsecure|secure)/base_url
  • contact email addresses
  • disable email (system/smtp/disable) so you don't send emails by mistake

3) Anonimize customer information

  • you can use Anonygento module for Magento
  • write your own script to obfuscate customer information/sales orders/etc

4) Module Settings

  • you can enable sandbox mode for payment/shipping modules and make the proper settings
  • check modules used for various integration (either disable or set them to sandbox mode)

We wrote a script to handle DB dumps for branching. Read this article.

The basic principal is that it reads the local.xml to fetch the DB credentials, then dumps the data on that basis. It splits the dump into two parts, the structure only and then the data. But the key is that it speeds up the conventional dump process by skipping out non-essential data, and most critically prevents any table locks during the dump that would otherwise block/hang your live site.

When you've got the MySQL dump, you can change the URL very easily just by using sed

sed -i 's/www.mydomain.com/staging.mydomain.com/g' ./var/db.sql

Then run a mysql import into your new DB.

So without the script, a very basic version would look like this.

mysqldump -hHostname -uUsername LiveDbname -p > db.sql
sed -i 's/www.mydomain.com/staging.mydomain.com/g' db.sql
mysql -hHostname -uUsername DevDbname -p < db.sql

There is no reason at all to have to delete the local.xml file, or re-run the installer if you change the URLs in the DB in this fashion.

The whole process of branching is well covered in our Magento GIT Guide. This is a good process for creating development branches, but does shrink the live DB by a significant margin. So tests won't be completely the same as on the live site.

So performing a vanilla DB dump, sed replace, DB import is sufficient for a staging site. And will mirror/match the live site as closely as possible.

In terms of prevent communications with customers - we've never found it a necessity, as we always create accounts deliberately for testing, never using real customer orders for testing.

Tags:

Database

Recent Posts