Mysql – Best method to synchronize two tables across SQL servers

MySQLodbc

I have one large table in a MySQL table that when updated, I want to synchronize with a duplicate of the table on a Microsoft SQL server. Here is the process that happens when a new row is inserted into the MySQL table, and how it is sent to the MS SQL server table:

  1. When a new row is inserted into the MySQL table, there's a trigger that inserts the same row into a temp table on the MySQL server
  2. A job on the MS SQL server periodically checks the temp table, and from there takes any rows and inserts them into the MS SQL using a MERGE statement.
  3. Temp table is cleared out of any processed rows.

I have recently found out that very randomly some rows have just not been transferred over to the MS SQL server. What I am wondering is, is there a better way to do this, with reliability being the first priority, and speed being less of a priority, what other methods are out there? Thanks for any help, feel free to ask for clarification if I did not explain it well.

Best Answer

I suspect you grab the list of rows to move, then an insert occurs, then you delete the rows. Instead...

Ping-pong a pair of tables. You are always inserting into one table, always copying from the other. Then do an atomic RENAME TABLE to switch.

See my high speed ingestion blog for something very similar. It explains the flip-flop technique.

Related Question