How to keep testing and production e-commerce db in sync when you have "sales" updates in production?

I've read the following thread in SO, Keeping testing and production server environments clean, in sync, and consistent, and I still have some doubts in what is the best way to do it.

"You should do modifications to your test environment and replicate it to your production server". Problem is I'm using Magento installa开发者_运维技巧tion: I have different modifications in test and production environment in different times (like sales in production server), and I don't know what is the best way to keep them in sync.

"Digging" in Magento's table structure is going to be a pain since there are 200+ tables.

What are your opinions about it? Are there tools that could help me to sync the databases in this situation? Is there a MySql configuration to do it?

Thanks in advance!

I know I'm late to the mix, but I just posted up a script that syncs production databases with staging.

http://markshust.com/2011/09/08/syncing-magento-instance-production-development

Generally, nothing should be important on staging as it could get overwritten at any time - it's supposed to be an exact clone of production for testing in an environment that just about exactly mimics production.

Development (or your local machine) is where you test code updates or changes before it is sent of to staging for deployment testing.

I'm running a Magento site with a production instance, a number of development instances, and a testing instance.

Magento is generally pretty clean about keeping metamodel and database configuration information in code. Each module comes with the database scripts that it needs to run to install itself, and Magento's quite good about running these automatically when the code for a module shows up. There are some configuration options that are modified in the administrator and kept in the datbase, but very few. This is different than some systems, e.g. Drupal, where there is a massive amount of metamodel information kept in the database and never reflected in code.

What I do is keep track of the code base in version control. For example, I'll install a new module in a development version. Once the code is working, I'll tag it in version control, push the tag out to the test instance, and copy the database from production to the test instance. That gives me an exact picture of what the production system will look like. (Some small changes are required when moving a Magento database from instance to instance. See this answer for details.)

On deployment, there are usually only very few configuration options that need to be set in the admin. I make note of them as I put them through on test, and this gives me a list of what needs to be done with the move to production.

If you must have automatic and instant config options set with the deployment, you can specify the default config options to be what you need in a config.xml that will be loaded after your module (i.e. that depends on your module.) I've never done it this way, but I don't see any reason why it wouldn't work.

Any changes to live data (changing prices across the board, or the like), I script on dev, test on a copy of the live database, and deploy on production.

Hope this helps.

Fast answer: Magento enterprise is supposed to take care of this by allowing you to run staging sites and then sync content such as prices and products via the admin interface. I have not have total success with it, but the idea is there.

When it comes to code, you may still be able to use the environments without much difficulty. For instance, changing the appearance of your site or adding new plugins may not require any modification of the database and should therefore be safe.

When adding new tables or modifying params, you'll need to keep track of any changes that are made and run a script mirroring those changes against the production site.

So that leaves catalog changes as the likely problem spot. I would advocate just making those changes to the live site (as they are standard data) and checking the configuration of the items before making them visible. You could also wipe out the specific tables for products (but not sales) and reload them from the test database, but this would take your site offline for a while whenever you sync.

If this misses your point, please post more detail about a problematic change that needs to be populated.

Thanks,

Joe

Use GIT, create remote branches, your production branch, release branch, keep it up to date, merge when it's needed. You can find more: http://git-scm.com/ and http://github.com/