Sync live and staging databases while retaining sales data

[Finestshops]

We do something like this for store upgrades and re-platforming from other systems to Magento. In such cases, we just migrate orders and customers from live store to the development store and switch development store live. 

Try to remove orders and customers from the development store, export orders and customers from the live store into csv  file, import those records into your development store. If your version of Magento does not have import/export feature, try a service by https://www.shopping-cart-migration.com by they can get expensive very fast if you have a lot of records.

You can try to import the missing products (if any) the same way but it can be a bit more complex.

Just make sure you do a full database backup before any import or migration in case something will go wrong.

[mousepad]

Hi Anton,

Thanks for the reply. Your solution is what I am looking for but how exactly do you export customers and orders and reimport them. There is an option for import/export of customers but not orders. I have purchased multiple order import/export extensions but none of them work. They all have so many problems that they are useable. I'm going to try shopping cart migration but it's not a long term solution either. 

Maybe I need to ask this question a different way :

What are ALL the tables in the Magento 2 schema are used for customers and orders ?  Could i export these from 1 database and import into another.

Also do the products in orders match on SKU or ID ? The id of my products may be different, so I need it to match on SKU.

[herve_tribouilloy1]

I can hear you guys promoting the Magento dev principles that consists in using Module install/update data scripts and that is great to see you are advocating something we should do as a developer for the Magento platform.

However, I find myself in a simialr situation and the people making the changes are 1: on a separate environment than production, 2: perform cms changes, catalog rules, config for shipping, and son on. Also, some of these changes need to apply for specific stores. Finally, there is no compromise to have these changes to be made by a dev. 

Could somebody add to the table some insights that take this type of considerations into account?

[mjoraid]

This question is not solely Magento related, this questions applies to any live website with a staging environment. 

Like seriously, how do developers do this?

So far from the comments the ways to do this are:

1- write down each change and make it a command/query for it, so when ready to push to live, close live temporarily and apply the scripts/queries. 

2- If the changes are too much, then the Staging will become the Live. So once staging is ready, temp close live, sync edits from live to staging (now staging has the latest info/transactions) then make staging live and turn it on. 

So far doesn't seem like a solution. A lot of variables. 

[5cb494b150a1d]

"when our developers install the finished customizations from a development to a live store, they upload new files and run MySQL queries to implement the changes in the live database. we never had any problems with this approach and, as I know, all developers do this."

This is actually not how things should be done. You don't deploy via single SQL queries or copying stuff from a test DB to a live environment.

There are some things you have to implement first. 

1. Organize your code via a versioning system like github, bitbucket etc.

2. Organize your custom theme and all your changes in separate modules

3. Use composer to add those modules to your deployment.

All changes you make on your dev or staging environment should only be file based. So your test orders or cms blocks or whatever you create on your test environments are not reflected on live. You only work on the codebase (modules, theme etc.)

Deploying a new live version is done via git branches. For example develop branch is merged into a master branch when your work on the module is finished and tested.

With those new master branches (use versioning for your modules) it's easy to create a composer installation with all your modules, including magento itself. 

When you are not using a CI/CD deployment you will have a downtime. In this case you have to set the store into maintenance mode, disable all cronjobs and wait a bit until all running cornjobs or db accesses are actually done.

Then you pull the latest changes from the updated master repository and run the magento setup (bin/magento setup:update etc.) You can read the docs what exactly you have to do.

setup:update updates your live database schema to the latest version. So if you have a new module or modules with another version, those changes will be made in your live db. So there is no need to copy anything from your test or stage db to the live. 

You only update the codebase.