efficient method to sync up two tables in differen

问题:

we have a source table which is updated from various external systems. i require the destination table (in different server) to be in sync with this source table. the destination table is not an exact replica of the source table, some data processing has to be done before the data is inserted/updated into destination table.

i have thought of the following logic

every 15min we run this java consumer code which fetches the records where the timestamp is created than that of previous update and stored in a CachedRowSet. and call a stored procedure with CachedRowSet as parameter, where the data processing is done and the data is inserted/updated into the destination table.

do you believe above mentioned is an efficient way as we are dealing over a million records every update ?

also when a record is deleted in the source table in would not be replicated in the above method ! can you suggest what to do in such scenario

回答1:

Something similar to technique used by database for save point and rollback. Whenever there is some change in the source table e.g. CRUD. keep the scripts of change as per format required to the target table. periodically you can push those changes to the target server. As your source table is updated by various external system, you'll need to have trigger on your source table for keeping script logs.

回答2:

You might want to check out mk-table-sync from Maatkit tools:

http://www.maatkit.org/doc/mk-table-sync.html

You'd need to be careful around your table differences.

回答3:

Here are some existing solutions:

https://www.symmetricds.org/

http://opensource.replicator.daffodilsw.com/