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.

kojiro
  • 1,048
  • 12
  • 22

5 Answers5

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)
FlorinelChis
  • 2,900
  • 2
  • 26
  • 41
  • 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 Feb 24 '13 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! – Tim Bezhashvyly Feb 24 '13 at 17:06
  • @Tim the hard part is to put all of the above in a script... running it afterwards is the simple solution. – FlorinelChis Feb 24 '13 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.

Ben Lessani
  • 17,630
  • 4
  • 44
  • 68
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 -- xyphoid@example.com,coworker@example.com"
xyphoid
  • 985
  • 6
  • 11
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-')
Timothy Frew
  • 318
  • 2
  • 9
SPRBRN
  • 1,307
  • 6
  • 19
  • 33
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.

Tim Bezhashvyly
  • 11,575
  • 6
  • 43
  • 73
  • 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 Feb 24 '13 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. – Fiasco Labs Feb 24 '13 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. – Fiasco Labs Feb 24 '13 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 Feb 24 '13 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 Feb 24 '13 at 16:57
  • @kojiro: Why won't you use a test user/order within your test system? Also while answering I was thinking about cloning the production DB into a localhost where you are in full control of SMTP. – Tim Bezhashvyly Feb 24 '13 at 17:04
  • @kojiro - I guess your confusion comes from the fact you need to understand. Customer accounts key on the email address. If you want to have more than one test account so you don't annoy your customers and want to see the emailed results, you do as I mentioned. You don't use current live customers for testing, even if you are using dummy credit card numbers given you by your card processor. Go with Sonassi's answer, they've been doing this stuff for a long time. – Fiasco Labs Feb 24 '13 at 18:09