We have a single deployed (MySQL) database that contains both user tables and product tables (currently in the same DB).
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.
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.
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?