Mysql – Single MySQL DB contains both push and pull data. Recommended path to replication

database-designMySQLreplication

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?

Best Answer

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.

Related Question