Good day all, I am sure a lot of people over time has asked this but yet I am unable to find any reference on the net for what we need.

We have a client running both Oracle and MySQL databases, each for its own purpose.

There is a requirement to have specific data updated from oracle to mysql every 30 minutes.

There is a current custom process in place but this takes a long time and also quite a bit of resources.


I am trying to find if there is a better way of doing this with as little effort and as little processes involved as possible.

The mysql databases will be moved to new hardware soon in an effort to improve the performance from a range of areas , and we would in the same process also try to resolve the issue with the data export/import between oracle and mysql.

Any ideas will be appreciated.

Regards
Machiel

Hi Marchal,

have you got timings on each phase of the process? For instance, is is the data extraction that is taking time or the data insert into MySQL? This will give a clearer picture as to what to start looking for.

If you are inserting into MySQL, do you have indexes on the tables? What about foreign key constraints do these exist too? Each of these will contribute to the overall performance hit.

If the tables contain massive amounts of data you could look at implementing partitions on MySQL to simplify the data management.

HI, thank you for the response. The problem we have with this process is not in terms of the actual dump or insert. (Maybe i didn't phrase my problem correctly for which I apologize).

Currently, the process takes about 4 different steps before the data is in the the mysql production database:

1. Create dump file on oracle
2. translate using custom scripts into mysql format
3. load onto staging mysql database running on a VM
4. If successful then load into production VM.


We are looking at removing the VM and and try to make the process as short and less complicated as possible. The process is currently being managed by a 3rd party, however should the 3rd party not be involved in future no one will know what to do and all the extra steps just complicates it.

So we are looking for the best and easiest way to re-implement this, automated and with as little steps as possible.

Regards
Machiel