Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a single deployed (MySQL) database that contains both user tables and product tables (currently in the same DB).

  1. The product tables are "pushed to production" data -- they are generated in our internal development environment and need to make their way to the production database. We'd like to set up replication because our product DB is growing quite large (>40M items). Its no longer feasible to push snapshots of this data - we want incremental updates.

  2. The user tables are "pulled from production" data -- they generated in our production system and copies of this data need to make their way back into our development. Our devs want near real-time analytics and don't want to run these queries on the production environment.

  3. There are (currently) some join queries between these set of tables (e.g. lookup user's cart product). However its not particularly problematic separate these as separate queries to independent stores.

I'd like to set up replication of both sets of data. Pull the user data, push the product data.

What would be the best practice to set up this kind of bi-directional way replication?

Is it possible to setup replication on a table basis? Or should the data be split into separate databases, or even separate servers?

share|improve this question
    
I've got the same question. The reason for not auto-increment from development server to main server is that one wants to be sure the data is correct and works right. Agree that Master-Master is NOT a wise choice - the goal is keep it simple and no chaos. Main server has live data (customer info, etc.) and development server has product data and test site for testing before making it live. Ideally, we'd have a table in the development server mysql database with name of tables to move and the direction (push or pull). We'd like to use that table for updating when we want to move data. Keeping t –  oldnewbie2 Apr 1 at 23:41

1 Answer 1

You may set up a Master-Master-Replication in which both servers are slave of each other and one can write to both servers.

If you always make changes to just one subset of the tables on each of the servers, you do not run into problems concerning auto increment values (you might want to set auto_increment_increment and auto_increment_offset anyhow) or simultaneous writes to the same data on different servers.

But please note that a Master-Master-Replication can result in severe chaos, if one does something wrong.

Actually I do not understand why you do not fill the product tables in the production directly and not in the development database? You then could use one single master for writes and just keep a slave for backups and statistics / analytics, which want current data, but do not need the latest at all time. Makes configuration, maintenance and failure resistance a lot easier.
That is the solution I would prefer in this case.

share|improve this answer
    
There's a good amount of massaging that happens on the product data before it goes into production, and then we also want to test that data in a production -simulated environment, particularly if we have a process or schema change. –  user48956 Apr 10 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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