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

Improve this question
2
  • If is too sensitive to be replicated, then you have no backup? What is the goal?
    – Rick James
    Commented 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.
    – Richard Turner
    Commented Oct 22, 2020 at 13:26

2 Answers 2

Reset to default
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.

Improve this answer
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?
    – Richard Turner
    Commented Oct 22, 2020 at 13:33
0

With PostgreSQL it should be very easy to do. Just create a publication on the master with the tables you want to be sent to other server. Create a subscription on the server you want to sync the tables from the master to

You will have two masters syncing the mentioned tables from both.

Improve this answer

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.