1

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server). The orders from the website are inserted into the Master Mysql database and which are replicated into the Slave Mysql database. These orders need to be pushed to the Oracle Db. I have arrived on 4 methods to do this.

  1. Use mysql UDF for http communication that would send the rows on a Insert Trigger on the slave to the Oracle webservices on Oracle server

  2. Use cron jobs(with a low interval may be 5 mins) with Php script that would get the new orders from mysql and send to the Oracle db via Oracle services/Php services on Oracle hosted server.

  3. Use sys_exec() udf to invoke php script that inserts into Oracle db

  4. Use memcached with MySql and let Php poll the memcached to retrieve data and send it to Oracle server, but unsure whether we could migrate existing Mysql version to new version Mysql 5.6

I already have the UDF's in place and tested them, they are good to go. but still in dilemma regarding data integrity and reliability in case of using UDF's with triggers.

Is there a better method for doing this. Or else which method shall I follow to do the same.

I am aware of the security threats of UDF's, you can't restrict it to any user

One more thing I am not allowed to introduce new changes to the existing website php code for the operation.

| improve this question | |
0

Try using heterogenous Connectivity to link the Oracle database to the mySQL database. It would be easier to directly query for what you want from Oracle, rather than trying to replicate the data to Oracle. I tried doing this on Redhat 5.6, but I had issues with the ODBC driver that was written for Redhat 5.6. I later tries it with Redhat 6.3 and it worked much better. The issue dealt with how the ODBC driver read unicode characters. It is pretty easy to setup, you can install HAS as a different Oracle Home, or use the same Oracle home. Linux has a built in ODBC test program that will help you to get it working.

http://docs.oracle.com/cd/B28359_01/server.111/b28277/toc.htm

| improve this answer | |
0

There are a number of commercial tools that allow MySQL to Oracle replication (Continuent, Oracle GoldenGate). I have not used any specifically, so I can not provide specific recommendations.

If you are going to write your own solution, I think the best way is to watch the binary log stream. I have an example of how to do this on my blog.

| improve this answer | |
  • Thanks, that seems really helpful. And yes after I get the statements I can use lets say php to execute equivalent statements for Oracle DB. Will it be appropriate? – coder Jun 23 '14 at 8:09
  • Yes, that would be the typical scenario. – Morgan Tocker Jun 23 '14 at 17:01
0

Thanks for answering..I have implemented it in a way as below:

  1. I had used Symmetric DS, I found it useless and very cumbersome to configure and use.
  2. And then, I used the mysql UDF for http requests
  3. Published a service using Oracle Embedded pl/sql gateway, esp. a procedure that accepts parameters, that lets Oracle db know the number of rows it has to pull from Mysql db.
  4. Oracle(Windows server) pulls data from mysql(Amazon server,Centos) using ODBC

  5. Finally the UDF was invoked on a statement based trigger on the slave replication in mysql

    This is all....

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.