I am searching for some solution for the following problem:
- I have two MySQL Database servers in different locations.
- I need copy a specific table from Server A into the same table of the Server B (duplicate table).
- This is a live table and it receives 3-6 insert/updates operations per minute (on average)
- I need that the information in both tables is synchronized in real time, each change in the table of Server A should be made in the table of Server B.
- The table of Server B will be used as only read table for a dashboard tool, so the goal is that dashboard tools does not query the original table and original server for security reasons.
- As described above, the goal is to have a replica of the table of Server A into Server B, so to publish it and this could be read for a dashboard tool.
- This table has confidential information so some columns of the table must not be replicated, for instance, columns for client names, national ids, phones,…
My first idea is do this through triggers but these do not work with different servers.
Replica master-slave could mark the way, but I don't know if it's possible to alter schemas in the destiny table.
Can this be done with native tools? If there is some commercial solution, buying it is not a problem.