MySQL Mirror Database Question.

[tarsier]

I am using a MySql database to mirror another non-MySql database. The reason for doing this is the only access into this database is over an ODBC connection and is very slow. I do a data-copy 4 times a day to check for changes in the old database. There are about 5 tables that I copy over, with a total of about 100,000 records. This is what I do:

 

- Copy the whole table out of the Master database over ODBC into a temp MySql table with the same form at the final mirror copy.

- Do an SQL compare to look for any new records.

- Do an SQL compare to look for modified records.

- Do an SQL compare to look for deleted records.

 

I did not want to update the table by just dropping the old table and moving the copy over because of the chance of users doing a search at that point and getting bad data. I also could not use a REPLACE because some of the tables that I am moving over do not have primary keys.

 

My questions is if there is a better way to do this? Would love to get some feedback on this. Thanks

- Dave