List:General Discussion« Previous MessageNext Message »
From:Dan Burke Date:September 19 2005 7:25pm
Subject:Database replication between oracle->mysql (was RE: database link)
View as plain text  
This is really cool!  Can something be done with this to use a
materialized view log on a table in Oracle and sync with a table in
mysql?


-----Original Message-----
From: Mark Leith [mailto:mark@stripped] 
Sent: Friday, September 16, 2005 9:54 AM
To: mysql@stripped
Subject: RE: database link

You may want to think about doing this the opposite way around also, and
look at pushing the data from Oracle in to MySQL. Oracle has something
called "heterogeneous services", which allows you to define ODBC
datasources
as valid entries in the tnsnames.ora file. Then you could simply create
a
job in Oracle that executes a procedure to do the entire process
(truncate /
load), no external scripting necessary..

Here's a quick example of what to do:

First set up an ODBC data source for your MySQL database, using MyODBC.

Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file
put
the following options:

HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN
*/
HS_FDS_TRACE_LEVEL = OFF

Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
following:

(SID_DESC =
  (PROGRAM              = hsodbc)
  (ORACLE_HOME          = oracle/product/92) /* Your ORACLE_HOME */
  (SID_NAME             = MySQL5) /* Your DSN */
)

Add the following to your tnsnames.ora file:

MYSQL5 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=MYSQL5))
    (HS=OK)
  )

Reload your Oracle listener (lsnrctl reload), and then connect to the
Oracle
database. To set the database link up:

CREATE DATABASE LINK mysql5 CONNECT TO "user" identified by "password"
using
'mysql5';

User and password should be a valid user within MySQL, that can connect
from
the Oracle host. You should be set to go from there. Here's a quick
example
of this working, to a MySQL 5 database using the new "sakila" sample
database that Mike Hillyer recently released
(http://www.openwin.org/mike/download/sakila.zip):

SQL> select count(*) from film@mysql5;

  COUNT(*)
----------
      1000

SQL> desc film@mysql5;
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 film_id                                            NUMBER(10)
 category_id                               NOT NULL NUMBER(10)
 title                                     NOT NULL VARCHAR2(27)
 description                                        LONG
 rental_duration                           NOT NULL NUMBER(3)
 length                                             NUMBER(10)
 rating                                             CHAR(5)

SQL> insert into film@mysql5 values (1000000,1,'test','test',1,1,'PG');

1 row created.

---change prompts---

mysql> use sakila
Database changed
mysql> select max(film_id) from film;
+--------------+
| max(film_id) |
+--------------+
|      1000000 |
+--------------+
1 row in set (0.01 sec)

HTH

Mark Leith

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date:
15/09/2005
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1



_______________________________________________________
This message has been scanned for viruses 
by TechTeam's email gateway.



_________________________________________________
This e-mail transmission is strictly confidential 
and intended solely for the person or organization 
to whom it is addressed. It may contain privileged 
and confidential information and if you are not the 
intended recipient, you must not copy, distribute or 
take any action in reliance on it. If you have 
received this e-mail in error, please notify the 
sender as soon as possible and delete the e-mail 
message and any attachment(s).

This message has been scanned for viruses 
by TechTeam's email gateway.


Thread
Database replication between oracle->mysql (was RE: database link)Dan Burke19 Sep