Synchronizing databases

Thread view:

Ike - 26 Sep 2004 19:54 GMT

I have a MySql database I connect through using mysql-connector-...jar. I
need to co-exist with another MySql database in order to get this one job
wherein the fields of the other database can map to fields in my database.

For example, they have a customers table as does mine. There may be some
differences in the fields and such, but, in broad strokes, the tables are
redundant.

I cannot, however, rewrite this application to use their format - both
tables need to coexist. So I am wondering if there is not an accepted means
to monitor changes in one database/table and forward those changes to the
other database/table on an ongoing and real-time basis.

Does such a procedure exist? Thanks, Ike

Reply to this Message

David Harper - 26 Sep 2004 20:27 GMT

> I have a MySql database I connect through using mysql-connector-...jar. I
> need to co-exist with another MySql database in order to get this one job
[quoted text clipped - 10 lines]
>
> Does such a procedure exist? Thanks, Ike

If the table structures were *identical*, you could use replication.
Read the manual chapter here:

http://dev.mysql.com/doc/mysql/en/Replication.html

However, in your case, the two tables are apparently not identical, so I
cannot recommend replication, because it has the potential to completely
screw the database which is the "backup copy". You'll have to update
both tables in your client code.

David Harper
Cambridge, England

Reply to this Message

Mykola Rabchevskiy - 28 Sep 2004 03:15 GMT

I uses simple java program which read batch file and perform mix of
SQL statements targeted to 2 different databases. Batch script contains
in addition to usual SQL statements a special database switch statement.
Data synchronization between tables witch different structure requires
may be done in a few steps using mySQL commands over JDBC:

- select data into temp table;
- save this temp table into text file;
- insert data from text file into other database.

Of course, it is possible to use system scheduling tool to perform
batch processing automatically on regular basis.

Java program is very simple; I can send sources.

Regards

Mykola Rabchevskiy

Reply to this Message

Synchronizing databases

Free Magazines