Contents
To access a MySQL Database via unixODBC you need a driver. You get it free from http://dev.mysql.com/.
Connector/ODBC Installation
Prerequisites
unixODBC should be installed first, see Unix ODBC Installation
Install from Repository
For Mariadb in RedHat Linux 9 and Oracle Linux 9 there are Packages in the standard appstream repositiories.
dnf install mariadb-connector-odbc
If you want to use the Mysql-Connector, you have to install the repository first:
# install the repository dnf install https://dev.mysql.com/get/mysql84-community-release-el9-2.noarch.rpm # install the connector dnf install mysql-connector-odbc
Configure unixODBC
Next step is to integrate the MySQL Connector/ODBC into unixODBC.
Configure dsn in odbc.ini
The file odbc.ini in usually located /etc/odbc.ini, /etc/unixODBC/odbc.inior /usr/local/etc/odbc.ini.
If you have compiled unixODBC from source as described in the previous article, you will use /usr/local/etc/odbc.ini.
For each connection a datasource name dsn is configured in odbc.ini
; mysqltestdsn is the datasource name (not case sensitive)
[mysqltestdsn]
; driver: unixODBC will use the specified driver
; for mariadb-odbc:
; driver = /usr/lib64/libmaodbc.so
driver = /usr/lib64/libmyodbc9w.so
; server: hostname (or ip address) of your MySQL Server
server = mysqlserver
; port: database port number on the specified host (usually 3306)
port = 3306
; database: name of database
database = sakila
; charset: character set of MySQL database, usually latin1 or utf8
charset = utf8
Test the configured dsn
The configured dsn can be tested with isql:
/usr/local/bin/isql (if your compiled unixODBC from source) or /usr/bin/isql (unixODBC installed as package)
$ echo "select version();" | isql -v mysqltestdsn username password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> +-----------------+
| version() |
+-----------------+
| 10.11.15-MariaDB|
+-----------------+
SQLRowCount returns 1
1 rows fetched
SQL>
$ isql mysqltestdsn username password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT schema_name, default_character_set_name FROM information_schema.SCHEMATA;
SQL> +-----------------------------------------------------------------+---------------------------------+
| schema_name | default_character_set_name |
+-----------------------------------------------------------------+---------------------------------+
| information_schema | utf8mb3 |
| sakila | utf8mb3 |
+-----------------------------------------------------------------+---------------------------------+
SQLRowCount returns 2
2 rows fetched
Here you see three schema_names which can be accessed by the MySQL User.
But: with ODBC the user only can access the default database (sakila in the example).
Troubleshooting
Some typical errormessages
| isql errormessage | Reason |
|---|---|
| [ISQL]ERROR: Could not SQLConnect | use isql -v to get a more detailed message |
| [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified | Wrong datasource name or file odbc.ini not found |
| [S1000][unixODBC][MySQL][ODBC 5.3(w) Driver]Access denied for user 'username'@'%' to database 'sakila' | Wrong database name in odbc.ini or user has no access-rights |
| [S1000][unixODBC][MySQL][ODBC 5.3(w) Driver]Access denied for user 'username'@'oraserver.local' (using password: YES) | Wrong username or wrong password |
Next Step
If the isql Test works, your are ready to configure the Oracle Gateway to ODBC and the Database Link in your Oracle Database: Configure Oracle Gateway for ODBC with MySQL Connector