create data patch for database (synchronize databases)

Question

There is 2 databases: "temp" and "production". Each night production database should be "synchronized", so it will have exactly same data as in "temp". Database sizes are several GB and just copying all data is not an option. But changes are usually quite small: ~100 rows added, ~1000 rows updated and some removed. About 5-50Mb per day.

I was thinking maybe there is some tool (preferably free) that could go trough both databases and create patch, that could be applied to "production database". Or as option just "synchronize" both databases. And it should quite fast. In other words something like rsync for data in databases. If there is some solution for specific database (mysql, h2, db2, etc), it will be also fine.

PS: structure is guaranteed to be same, so this question is only about transferring data

Answer 1

Why not setup database replication from Temp Database to your Production database where your temp database will act as the Master and Production will act as a slave. Here is a link for setting up replication in MySql. MSSQL also supports database replication as well. Google should show up many tutorials.

Answer 2

Finally i found a way to do it in Kettle (PDI): http://wiki.pentaho.com/display/EAI/Synchronize+after+merge

Only one con: I need create such transformation for each table separately.