Steps to create a Database Link from Oracle to MySQL

Jamsher Khan 18 mins2
()

Steps to create a Database Link from Oracle to MySQL

Nowadays many companies are using a mix of RDBMS system for example, some instances are running on Oracle and other instances are running on MySQL, and if we want to access data we need to do it using a heterogeneous connection. Heterogeneous connections allow us to query data from non-Oracle databases using SQL.

In this blog post we will see How to create a database link from Oracle to MySQL via ODBC.

 

Below are the high-level steps we will follow to set up dblink.
1. Setup MySql User in MySQL Cluster
2. Install MySQL ODBC Drivers in Oracle Server
3. Edit odbc.ini file & Test DSN’s connectivity in Oracle Server
4. Create initMYSQL.ora file in Oracle Server
5. Configure tnsname.ora & listener.ora file in Oracle Server
6. Create DB Link & Test Connectivity in Oracle Server

 

Below are setup details and the same will be used in this demonstration.

Sr. No.DatabaseVersionHostnameIPPort
1Oracle19.3test-machine01192.168.114.1771521
2MySQL8.0.22test-machine02192.168.114.1763306


Step 1. Setup MySql User: We will create user fdw_user and provides it with all privileges on database sample1 in MySQL.

[root@test-machine02 ~]# mysql -hlocalhost -uroot -S/u01/mysql-2/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.01 sec)

mysql>

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

mysql>  create user 'fdw_user'@'%' identified by 'Secret_123';
Query OK, 0 rows affected (0.01 sec)
mysql>

mysql> ALTER USER 'fdw_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Secret_123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on sample1.* to  'fdw_user'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'fdw_user'@'%';
+-------------------------------------------------------------------------+
| Grants for fdw_user@%                                                   |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `fdw_user`@`%`                                    |
| GRANT ALL PRIVILEGES ON `sample1`.* TO `fdw_user`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

[root@test-machine02 etc]# mysql -htest-machine02 -ufdw_user -P3306 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use sample1
Database changed
mysql> create table employee (id int, first_name varchar(20), last_name varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into employee values (1,'jobin','augustine'),(2,'avinash','vallarapu'),(3,'fernando','camargos');
Query OK, 3 rows affected (0.48 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.02 sec)

mysql> select user();
+-------------------------+
| user()                  |
+-------------------------+
| fdw_user@test-machine02 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| sample1    |
+------------+
1 row in set (0.00 sec)
mysql>


Step 2. Install MySQL ODBC Drivers: Use OS command yum install mysql-connector-odbc-8.0.26-1.el7.x86_64 to install MySql ODBC drivers. Please note below command will work only if you have MySQL repository configured. Follow the link: https://dbsguru.com/database-mysql-8-installation-using-yum-repository-method/ to configure MySQL repository.

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum install mysql-connector-odbc-8.0.26-1.el7.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package mysql-connector-odbc.x86_64 0:8.0.26-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================
 Package                                      Arch                           Version                                Repository                                          Size
=============================================================================================================================================================================
Installing:
 mysql-connector-odbc                         x86_64                         8.0.26-1.el7                           mysql-connectors-community                         4.1 M

Transaction Summary
=============================================================================================================================================================================
Install  1 Package

Total download size: 4.1 M
Installed size: 22 M
Is this ok [y/d/N]: y
Downloading packages:
mysql-connector-odbc-8.0.26-1.el7.x86_64.rpm                                                                                                          | 4.1 MB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-connector-odbc-8.0.26-1.el7.x86_64                                                                                                                  1/1
Success: Usage count is 1
Success: Usage count is 1
  Verifying  : mysql-connector-odbc-8.0.26-1.el7.x86_64                                                                                                                  1/1

Installed:
  mysql-connector-odbc.x86_64 0:8.0.26-1.el7

Complete!
[root@test-machine01 ~]#


Step 3. Edit odbc.ini file & test DSN’s connectivity: Create file odbc.ini under /etc directory. Add MySQL Cluster and User details. Please note we are using [MYSQL] as header for your block. Use command isql -v mysql to test DNS connectivity for MySQL Database.

[root@test-machine01 ~]#
[root@test-machine01 ~]# cd /etc/
[root@test-machine01 etc]#
[root@test-machine01 etc]# vi odbc.ini
[MYSQL]
Description = MYSQL
Driver = /usr/lib64/libmyodbc8a.so
Server = test-machine02
User = fdw_user
Password = Secret_123
Port = 3306
Database = sample1
:wq!
[root@test-machine01 etc]#
[root@test-machine01 etc]#
[root@test-machine01 etc]# isql -v mysql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
[root@test-machine01 etc]#


Step 4. Create initMYSQL.ora file: Create file initMYSQL.ora under directory $ORACLE_HOME/hs/admin and add the below parameters. Please note we are using MYSQL as our SID name and in parameter, HS_FDS_CONNECT_INFO = MYSQL is the same Header we using in the file: odbc.ini.

[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ cd /u01/app/oracle/product/19.3.0/db_1/hs/admin
[oracle@test-machine01 admin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/hs/admin
[oracle@test-machine01 admin]$ ls -ltr
total 20
-rw-r--r--. 1 oracle oinstall  489 Apr 17  2019 initdg4odbc.ora
-rw-r--r--. 1 oracle oinstall 1170 Apr 17  2019 extproc.ora
-rw-r-----. 1 oracle oinstall  407 Nov  2  2020 listener.ora.sample
-rw-r-----. 1 oracle oinstall  244 Nov  2  2020 tnsnames.ora.sample
-rw-r--r--. 1 oracle oinstall  459 Sep 29 16:40 initPG.ora
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi initMYSQL.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
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so
HS_FDS_FETCH_ROWS = 1
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=american_america.we8iso8859P1
HS_NLS_NCHAR=UCS2
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
:wq!
[oracle@test-machine01 admin]$


Step 5. Configure tnsnames.ora & listener.ora file: Configure tnsnames.ora & listener.ora same as below. Please note here SID = MYSQL is the SID name we created in Step 4. Once configuration is done perform tnsping mysql.

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ cd /u01/app/oracle/product/19.3.0/db_1/network/admin
[oracle@test-machine01 admin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/network/admin
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi tnsnames.ora
MYSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MYSQL)
    )
    (HS = OK)
  )
:wq!
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 12:01:46

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
The command completed successfully
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (PROGRAM = dg4odbc)
      (SID_NAME = MYSQL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
     )
   )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
:wq!
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 12:03:05

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-SEP-2021 12:03:05
Uptime                    0 days 0 hr. 0 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "MYSQL" has 1 instance(s).
  Instance "MYSQL", status UNKNOWN, has 1 handler(s) for this service...
Service "PG" has 1 instance(s).
  Instance "PG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$ tnsping mysql

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 30-SEP-2021 12:05:30

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))) (CONNECT_DATA = (SID = MYSQL)) (HS = OK))
OK (0 msec)
[oracle@test-machine01 admin]$


Note: Instead of stop/start listener you can also use reload listener after modification in file listener.ora.

Step 6. Create DB Link & Test Connectivity: Once all the above steps are done successfully. We are ready to create a dblink, connect to Oracle Database and, use Create database link command to create a database link. Please note we need to use “ “ around username as MySQL is case sensitive. Once DBLink is created try to access MySQL table: employee.

[oracle@test-machine01 ~]$ sqlplus sys as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 30 12:07:08 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> CREATE DATABASE LINK MYSQL_LINK  CONNECT TO "fdw_user"  IDENTIFIED BY Secret_123 USING 'MYSQL';

Database link created.

SQL> select sysdate from dual@MYSQL_LINK;

SYSDATE
---------
30-SEP-21

SQL> set lines 300
SQL> col first_name for a40
SQL> col last_name for a40
SQL>  select * from "employee"@MYSQL_LINK;

        id first_name                               last_name
---------- ---------------------------------------- ----------------------------------------
         1 jobin                                    augustine
         2 avinash                                  vallarapu
         3 fernando                                 camargos

SQL>


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, , , ,

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

2 thoughts on “Steps to create a Database Link from Oracle to MySQL”

Comments are closed.

Related Articles