How to merge two databases for Magento

Question

Situation

To get this out first thing: I am not a Magento developer. Don't know much about it. I just consult when it comes to server and project management issues. I'll occasionally get my hands dirty with a rough patch for issues, but I don't know anything about the "Magento Way" of doing things.

We have a Magento site (1.9.3.4) we perform active development on. We have a production server and a development server.

Problem

Magento (like WordPress) is very database-dependent. We can version and transfer code without issue. We need an easy and reliable method for merging the databases, however.

On production we want to retain products, orders, reviews, site configurations (such as URL), etc; and on develop we want to retain CMS, plugin configurations (especially troublesome when we install a new plugin), etc.

Currently, I manually backup both databses, trim out the table I don't want from each, and combine them. I then push the resulting Frankensteinian database to production. This is as error prone as you can imagine.

There has to be a better way to do this.

Other Paths

I've toyed with the idea of a shared database, but that isn't ideal, as unapproved changes to products and pages would reflect on the production site.

I've also toyed with the idea of piping the database dumps into Git, but that still requires me to know which tables to keep from each respective database.

Update

Now that I think about it, would the better path here be to have a second "staging" store on the production environment rather than a separate development server? Caveats?

Answer 1

The correct environment in security and operation would be:

• Two different servers: avoids potential security problems that may exist in an developer environment and that can be harmful to the production environment.
• Two different databases in different server: for the same reason as before. How to work? First modifications are made in the development environment, if they are correct and everything works well, they are made in the production server.
• Files: use a repository system, like git, well configured, with .gitignore, and separating development and production in different branches.

Maybe this answer will not help you, but what are you proposing isn't safe nor is it a good practice to work in a project.

I hope to help you.

Answer 2

Interesting question although does confuse me a little bit, here is my take on it though, which is heavily based on opinion.

In reality Your product and Development database should match (mostly)

The way I currently work is, the database from product is pulled down to a staging/local environment without customer data, no orders, customer accounts, quotes etc.

I then do my development and if there is anything database wise that needs to change when going to production then these are done in an upgrade script, these are run automagically either on deployment using something like magerun or when logging into the admin.

For store configuration type things I normally have a Client_Core module and system config data upgrade scripts go inside there, then upon deployment these are run and the changes can be seen straight away.

As all the work i do has to go onto the staging environment first this basically means that after deploying to production the two environments match again, until the next release is built.

The only downside so far to this process is that if I were to create a release with 3/4 new features in it and deploy this to the staging environment and then the client decides they don't want one of those features, then we roll back the release so the code is rolled back and then we re-sync the database.

Basically what i'm saying is we almost never do any manual changes even to things like cms blocks/pages. The only caveat to this is that the client may make changes so before we do any deployments/database resync we make sure there is nothing on staging that the client wants to keep.

Hope this gives you some thoughts/insight to help you, as i said it's just my work process and opinions.