0

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.

  • what if you get the queries using triggers and then apply those queries to the other server,would that help..have a look at stackoverflow.com/questions/12563706/… – Nawaz Sohail Apr 8 '16 at 8:41
  • 1
    Was normal MySQL replication not working? You can always you replicate-do-table or replicate-do-table-wildcard to filter for specific table(s) only. If master-slave is not an option could you please elaborate on the problem? – Károly Nagy Apr 8 '16 at 8:45
  • @KárolyNagy I am investigating about master-slave replication, which could solve my problem. My doubts are about the mysqldump action which could lock the original database (which is running now...), at least at beginning. Is it true? I must avoid lock the original database. If it is necessary only lock original database when I will do initial configuration could be ok... Thanks. – mzurita Apr 8 '16 at 10:03
  • You would need a single table, right? How big is that table? If you can afford not having writes for the time of the dump that would solve the problem. You don't need to dump everything, only the required table. – Károly Nagy Apr 8 '16 at 10:50
  • Table has near of 5 millions of row. A first dump could be heavy (could be made at night). Later 3-6 rows are added per minute on average... This have to accomplish updated question... – mzurita Apr 8 '16 at 10:56
1

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.

  • If it were possible to do it without "touch" the original table, it would be important. Make a View (selecting desired columns) in original database and to do a reply of this, in the destiny database (server) would be perfect, but I have doubts about if this is possible. What you comment about vertical partitioning, it isn't clear for me how apply it here. (Although I can advance it won't have problems about primary key, since, the primary column would be in the destiny table and it would keep its integrity) – mzurita Apr 11 '16 at 7:26
0

Finally, I had to change the approach of the solution.

In the original server, I have created several triggers over the "private data" table when it changes (in inserts/deletes/updates), which store "insert/update/delete" custom queries in other table.

Through a demon (developed in .net), which is launched each 5 minutes, it opens a connection with original server, it retrieves the queries custom strings, and, it opens a connection with destiny server, where it executes the queries... Maybe it's not the best solution, but it's a solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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