[root@ncsdba2 lib]# lsb_release -id
Distributor ID: EnterpriseEnterpriseServer
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
[root@ncsdba2 oracle]# rpm -qa|grep mysql
mysql-server-5.0.77-4.el5_4.2
mysql-connector-odbc-5.1.8-1.rhel5
mysql-5.0.77-4.el5_4.2
MySQL Setup
[oracle@ncsdba2 ~]$ mysql -u oragwuser -pora123 -h ncsdba2.ncs.corp.int-ads
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create user oragwuser@'%' identified by 'ora123';
Query OK, 0 rows affected (0.00 sec)
mysql> create database oragwdb;
Query OK, 1 row affected (0.02 sec)
mysql> use oragwdb;
Database changed
mysql> grant all privileges on oragwdb.* to 'oragwuser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'oragwuser'@'%';
+----------------------------------------------------------------------------+
| Grants for oragwuser@% |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oragwuser'@'%' IDENTIFIED BY PASSWORD 'eae540d9bbf' |
| GRANT ALL PRIVILEGES ON `oragwdb`.* TO 'oragwuser'@'%' |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use oragwdb;
Database changed
mysql> insert into mysql_table1 values(1,'donghua'),(2,'kaitlyn');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql_table1;
+------+---------+
| id | name |
+------+---------+
| 1 | donghua |
| 2 | kaitlyn |
+------+---------+
2 rows in set (0.00 sec)
Install MySQL-ODBC
[root@ncsdba2 oracle]# /usr/bin/myodbc-installer -d -l
PostgreSQL
MySQL ODBC 5.1 Driver
[root@ncsdba2 oracle]# /usr/bin/myodbc-installer -s -a -c2 -n "mysql_odbc" \
> -t "DRIVER=MySQL ODBC 5.1 Driver;SERVER=ncsdba2.ncs.corp.int-ads;DATABASE=oragwdb;UID=oragwuser;PWD=ora123"
Success
[root@ncsdba2 oracle]# /usr/bin/myodbc-installer -s -l -c2 -n "mysql_odbc"
Name: mysql_odbc
Driver: /usr/lib/libmyodbc5.so
Server: ncsdba2.ncs.corp.int-ads
Uid: oragwuser
Pwd: ora123
Database: oragwdb
Port: 3306
Options:
[root@ncsdba2 oracle]# cat /etc/odbc.ini
[mysql_odbc]
Driver = /usr/lib/libmyodbc5.so
SERVER = ncsdba2.ncs.corp.int-ads
UID = oragwuser
PWD = ora123
DATABASE = oragwdb
PORT = 3306
Verify MySQL-ODBC Setup
[oracle@ncsdba2 ~]$ odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/oracle/.odbc.ini
[root@ncsdba2 oracle]# isql -v mysql_odbc oragwuser ora123
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help mysql_table1
+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+----------+---------------------+------------+--------------+---------------+---------------+---------+-----------------------------------------------------------------+-----------------------------------------------------------------+--------------+-----------------+------------------+-----------------+------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE| TYPE_NAME | COLUMN_SIZE| BUFFER_LENGTH| DECIMAL_DIGITS| NUM_PREC_RADIX| NULLABLE| REMARKS | COLUMN_DEF | SQL_DATA_TYPE| SQL_DATETIME_SUB| CHAR_OCTET_LENGTH| ORDINAL_POSITION| IS_NULLABLE|
+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+----------+---------------------+------------+--------------+---------------+---------------+---------+-----------------------------------------------------------------+-----------------------------------------------------------------+--------------+-----------------+------------------+-----------------+------------+
| | | mysql_table1 | id | 4 | integer | 10 | 4 | 0 | 10 | 1 | | | 4 | | | 1 | YES |
| | | mysql_table1 | name | 12 | varchar | 200 | 200 | | | 1 | | | 12 | | 200 | 2 | YES |
+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+----------+---------------------+------------+--------------+---------------+---------------+---------+-----------------------------------------------------------------+-----------------------------------------------------------------+--------------+-----------------+------------------+-----------------+------------+
SQLRowCount returns 2
2 rows fetched
SQL> select * from mysql_table1;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | name |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | donghua |
| 2 | kaitlyn |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 2
2 rows fetched
SQL> help
+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+---------------------------------------------------------------------------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS |
+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+---------------------------------------------------------------------------------+
| oragwdb | | mysql_table1 | TABLE | |
+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+---------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
Configure DG4ODBC in Oracle
Modify file “$ORACLE_HOME/hs/admin/initdg4odbc.ora”
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mysql_odbc
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc5.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set
Modify file “$ORACLE_HOME/network/admin/listener.ora”:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ncsdba2.ncs.corp.int-ads)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
)
)
Modify file “$ORACLE_HOME/network/admin/tnsnames.ora”:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncsdba2.ncs.corp.int-ads)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
[oracle@ncsdba2 ~]$ tnsping dg4odbc
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2011 14:50:01
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK))
OK (50 msec)
Create Database Link
[oracle@ncsdba2 log]$ sqlplus donghua/donghua
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 3 14:52:30 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, Data Mining and Oracle Database Vault options
SQL> create database link mysqldb connect to "oragwuser" identified by "ora123" using 'dg4odbc';
Database link created.
Verify the Setup
SQL> desc "mysql_table1"@mysqldb;
Name Null? Type
---------------------------------- -------- ----------------
id NUMBER(10)
name VARCHAR2(200)
SQL> select * from "mysql_table1"@mysqldb;
id name
---------- ----------
1 donghua
2 kaitlyn
SQL> insert into "mysql_table1"@mysqldb
2 values (3,'succeed');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from "mysql_table1"@mysqldb;
id
----------
name
--------------------------------------------------------------------------------
1
donghua
2
kaitlyn
3
succeed
[root@ncsdba2 lib]# isql -v mysql_odbc oragwuser ora123
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from mysql_table1;
+-----------+----------------------------------------------------------+
| id | name +----------------------------------------------------------------------+
| 1 | donghua
| 2 | kaitlyn
| 3 | succeed
+-------- -------------------------------------------------------------+
SQLRowCount returns 3
3 rows fetched
SQL> quit
[root@ncsdba2 lib]# mysql -u oragwuser -pora123 -h ncsdba2.ncs.corp.int-ads
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from oragwdb.mysql_table1;
+------+---------+
| id | name |
+------+---------+
| 1 | donghua |
| 2 | kaitlyn |
| 3 | succeed |
+------+---------+
3 rows in set (0.00 sec)
mysql> exit
Bye
No comments:
Post a Comment