Mysql – How to replicate MySQL table in different servers

MySQLreplication

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.

Added requirement:

  • 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.

Best Answer

Start by splitting your confidential info out of the table. Make a 'parallel' table ('vertical partitioning'). The new table would have the same PRIMARY KEY as the original, so they can easily be JOINed together.

To avoid replicating the new table, either use replicate_ignore_wild=... on the slave or put the table in a different database and use binlog_ignore_db=... on the master.

Or you could let it replicate, but change permissions so that most people do not have even SELECT privilege to that table/database.

That would be a minimal first step toward securing the data. I say it is only a fist step because if the Master is hacked into or stolen, there is nothing to stop them from getting the confidential info.

Related Question