15

The closer a test is to production, the better it can emulate production behavior. I would like to copy the database backups from production into our test environments, but what do I need to change so that test works, and to keep from interfering with production or accidentally emailing real customers (besides setting web/%secure/base_url with the test url)?

Another way to think about this question would be to consider how to generate something like Magento Sample Data from my own production data.

5 Answers 5

8

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)
3
  • For dev, ignoring the content for some tables is OK. For QA/Staging you'll want all those tables populated to reflect production as close as possible.
    – beeplogic
    Commented Feb 24, 2013 at 17:00
  • @FlorinelChris: I thought the question was about simplifying db migration and not making it that complex :) Bu in any ways, good answer! Commented Feb 24, 2013 at 17:06
  • @Tim the hard part is to put all of the above in a script... running it afterwards is the simple solution. Commented Feb 24, 2013 at 18:36
2

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.

1

One option for the email issue is to configure your development site to redirect ALL emails to you. Adds a bit of piece of mind.

How you do that depends on your environment - for us adding this to the vhost does the job:

php_admin_value sendmail_path "/usr/sbin/sendmail -i -- [email protected],[email protected]"
0

Nothing. Changing secure and unsecure URLs is quite enough.

You may also like to omit log_* tables data just to make your dump lighter.

7
  • 1
    But suppose I tell my test system I shipped an order – wouldn't it send an email to the real customer about it?
    – kojiro
    Commented Feb 24, 2013 at 16:21
  • The only other items that need changing are third party module registration information if you are running keys that are per domain instead of only being activation keys. I also include email addresses so test order transactional goes to a test account instead of the order desk. When you first start, secure and unsecure baseUrls are all that are necessary. If you plan on doing this often, package it all into a sql file and import it in after your magento db import. Commented Feb 24, 2013 at 16:23
  • @kojiro - Not if you set up some test accounts. Talk to your company email administrator, have them set up a couple email aliases. Commented Feb 24, 2013 at 16:25
  • @FiascoLabs I'm confused. In this scenario I've just imported a production Magento database with real customers in it. If I process a real order with a real customer, what good are email aliases going to do?
    – kojiro
    Commented Feb 24, 2013 at 16:32
  • @kojiro you're correct, after importing a data set from production then you need to scrub/update customer/sensitive information. This is not a good answer as it does not address the situation correctly.
    – beeplogic
    Commented Feb 24, 2013 at 16:57
0

Try this, it will scramble user emails to help with your issue in accidently emailing live customers from the test environment

UPDATE customer_entity SET email = REPLACE(email, '@', '-test@abcxyz123-')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.