Steps to create a Database Link from Oracle to MySQL

-

 

teps 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
[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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
[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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
[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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
[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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[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.

Comments

Post a Comment

Popular posts from this blog

Project Management System ER Diagram

-
Image
  This article will discuss the step by step process on how to prepare the entity relationship diagram or ERD of the project entitled Project Management System. Project Management System is an information system that will manage the processes and transactions involved in monitoring and recording the updates of a project. The said project can be implemented in an offline settings but in most cases this is implemented in a live server or it simply means an online system. The first step in the development of the Project Management System is to prepare the ER diagram that will serve as the basis later on in the creation of the actual database. We will create and explain the process of making the entity relationship diagram of project management system. Let’s start from the symbols used in the ER Diagram. Entity is represented by the rectangle shape. The entity will be our database table of Project Management System later on. Attribute is represented by the oval shape. This will be the ...
Read more

Install and Configure OBIEE 12c on Linux VM

-
Image
Install and Configure OBIEE 12c on Linux VM · 1 Today we are going to be installing Oracle BI 12c on our Linux Virtual Machine. If you have not already created an Oracle Linux VM, I wrote a blog on how to do this  here . If you have not already installed Oracle Database 19c on your Linux VM, I wrote a blog on how to do this  here . This blog also assumes that you have access to the sample HR dataset that comes pre-installed with Oracle Database 19c. I covered how to access this in the Oracle 19c installation blog that I mentioned above. Before we begin, I just wanted to say good luck  and … Starting-up and opening the database If you have not already done so, log-in to your Linux instance’s GUI. Your screen should look like this. First, let’s start our listener and open-up our database. Open a new terminal window and execute the following command to start your listener, if it is not already started. If it has already been started, the terminal will let you know. lsnrctl s...
Read more

Web Based Hotel Room Management Information System

-
  Systematic management of records allow organization to know what records they have, and locate them easily, increase efficiency and effectiveness, make savings in administrations costs both in staff time and storage, support decision making, be accountable, achieve business objectives and targets, provide continuity in the event of a disaster, meet legislative and regulatory requirements, protect the interests of employees and clients. Records are valuable because of the information they contain and that information is only usable if it is correctly and legibly recorded in the first place, is then kept up to date, and is easily accessible when need Records in hotels are not commonly found in archives but when such records survive and find their way to an archives the information contained in the records are beneficial to a variety of researchers, including students, genealogists, and academics. Due to manual recording system, Hotels, Pension Houses, Apartments and the likes are e...
Read more