1

I have 2 DBs - DB1 (back-office) and DB2 (web application). The data needs to be synced between the 2. I'm happy with the idea of full master-master replication and may have to implement it, but really I just want some tables replicated from DB1 (back-office) to DB2 (web application) and other tables replicated from DB2 to DB1. (Some data is too sensitive to be replicated out to DB2 hence I'd use the --replicate-wild-do-table method).

DB1 is updated by internal systems and then DB2 would receive the data to be displayed by a web application, the data considered "read-only". The web application can collect data via web forms and would put it in its local database DB, and I want that data replicated to DB1 back office server.

SO, can MySQL be a master for some tables and a slave for others? If not, could I do it with Postgres?

Many Thanks

2
  • If is too sensitive to be replicated, then you have no backup? What is the goal?
    – Rick James
    Oct 22, 2020 at 0:06
  • @RickJames - Hi, I meant I didn't want some of the data in the backend replicated to the database queried by the web application to reduce the possibilities of a breach. (Yes, the DB's are connected so it's not foolproof.) I'll definitely back the databases up or created local replicas but this was hopefully a way to provide data syncing at the DB level. Oct 22, 2020 at 13:26

1 Answer 1

0

Yes you can. MySQL 5.7 and later support filters for replication which allow you to exclude arbitrary tables: https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html

Caveat: This is a slave-side option, which means that sensitive data will still be in the relay logs on the client side.

1
  • So I'd need to set up Master-Master as blank databases (and they are both acting as clients), set filters on both servers (e.g. on the Web DB, ignore incoming financial and webform data tables, and on the Backoffice, ignore pretty much everything incoming except webform data tables. This would result in only webform data replicating to the back office and everything except financial would be sent to the Web DB. Does that make sense? Oct 22, 2020 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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