How to synchronize data between two tables in different databases (MYSQL) running in different servers

Question

I have 2 different mysql database (OLD and NEW) running in different servers. OLD Database has around 10 tables out of which i want to synchronize 3 tables database NEW. So if any addition/deletion/updation happens on those 3 tables then those entries should be updated on database NEW. I have created the NEW database using the mysql dump. But I want to add a cron job which could run every 1 hour to sync the 3 tables.

I want to write a script which would keep track of last synced data using timestamps and then sync the data from OLD to NEW. I do have connectivity between both the server but I'm unable to figure out the way to achieve this.

Any suggestions or links would be appreciated.

Answer 1

First of all, why don't any of you guys answer this dude's question? Sometimes we have to do this due to security restrictions / compliance / legacy systems.

There's some options I'll write here with pseudo code. Not sure how real-time your database is so this won't work in all cases.

Requirements

For this to work the databases will have to be in the same server instance. If not, you will need to setup a federate storage engine to access the remote data. As another person stated, MySQL replication could still be useful in at least getting the data to the same server making the synchronization faster without the need to setup federated storage. Reference: https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html

Sync Timing

You can synchronize on an event schedule

MySQL will allow you to create events on a specific schedule to perform your work (assuming you don't have any external job scheduling tools).

Hopefully you have a modified date of some sort, you can query once a day or tighter intervals on all the fields where modified_at >= DATE_SUB(NOW( ),INTERVAL ? HOUR)

If you are able to add a column, you could create one called synced_at which would be a little more resistant to server clock differences. Then you can simply query where synced_at IS NULL or synced_at <= modified_at

Alternatively, use triggers

MySQL supports BEFORE and AFTER triggers of INSERT / UPDATE / DELETE etc... you can use these to trigger your logic. Keep in mind you will take a little performance penalty for each transaction and this can easily overwhelm very active production servers.

There really isn't a huge difference between BEFORE and AFTER except that if you use the BEFORE style triggers you can throw a sqlstate to prevent the insert into the source table if it is important for both tables to be highly synchronized.

Sync Logic

The INSERT / UPDATE / DELETE pattern

This is pseudo code but...

# new and updated records
INSERT ... ON DUPLICATE KEY UPDATE ...
SELECT FROM source_table
JOIN target_table.id
WHERE target_table.id IS NULL or modified_at > DATE_SUB(NOW(), INTERVAL ..)

# deleted records

TRIGGER AFTER INSERT / UPDATE / DELETE

Same as above only you are just manipulating one record at a time and you are mirroring the trigger statement. For example: an INSERT TRIGGER on the source table should just query INSERT on the target table.

The nuclear DROP / CREATE / INSERT

Simple but not recommended for anything other than maybe a reporting database. Drop the entire table and rebuild it from the other records.