• MySQL知识库 :: oracle
  • How can I connect Oracle to MySQL?

  • Discussion

    It is possible to connect Oracle to MySQL using a feature within Oracle known as Heterogeneous Services. This allows you to use database links within Oracle to connect to non-Oracle databases, such as MySQL, via ODBC.

    First install the MyODBC driver on the server which is running Oracle. Configure an ODBC DSN (Data Source Name) for the MySQL server to which you would like to connect. See the external resources links in the margin for the MyODBC driver and for information on how to configure ODBC properly. Once this is complete, create a file in $ORACLE_HOME/hs/admin called initMySQL.ora. In this file, add the following options:

    /* Replace "MySQL" below with your DSN */
    
    HS_FDS_CONNECT_INFO = MySQL #ODBC DSN
    HS_FDS_TRACE_LEVEL = OFF

    Edit the server's 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 = MySQL) /* Your DSN */
    )

    Add the following to the tnsnames.ora file:

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

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

    CREATE DATABASE LINK mysql 
    CONNECT TO "my_user" IDENTIFIED BY "my_password" 
    USING 'mysql';

    The user name and password should be valid within MySQL so that it can connect from the Oracle host. Below is a simple example of this in action, connecting to a MySQL database using the new sakila sample database:

    SQL> COUNT(*) FROM film@mysql;
    
    COUNT(*)
    ----------
    1000
    
    SQL> DESC film@mysql;
    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@mysql 
    VALUES (1000000,1,'test','test',1,1,'PG');
    
    1 row created.
    
    ---change prompts---
    
    mysql> USE sakila
    
    mysql> SELECT max(film_id) FROM film;
    
    +--------------+
    | max(film_id) |
    +--------------+
    | 1000000 |
    +--------------+

    You will need to create your own tables in MySQL before running inserts across the database link.