Connect to MySQL from Linux or Mac with ODBC

Introduction

This article explains how to connect to MySQL using unixODBC or iODBC and MySQL Connector/ODBC. It includes installation and configuration instructions for Ubuntu, CentOS and the Mac.

It covers the following topics:

  1. How to connect to MySQL (with code samples).
  2. How to setup ODBC:
    • Install unixODBC:

      This is needed because to manage ODBC on Linux and Mac. The unixODBC install also include utilities isql and iusql.

    • Install Connector/ODBC:

      This is the official ODBC driver manager and driver from the MySQL website. You can configure ODBC using only odbc.ini, which contains the ODBC DSN details. You can optionally using odbcinst.ini as well but it is not needed.

    • Configure ODBC using the odbc.ini file:

      We recommend using system DSNs (available to all users), which use the /usr/odbc.ini file. User DSNs can be setup in the $HOME/.odbc.ini file.

Connect to MySQL [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The code samples will work with all operating systems.

Follow these steps to connect:

  1. Create a new channel to try out the code:
    • Create a channel like this:
      • Name: DB MySQL Connection (or similar)
      • Source: From Translator
      • Destination: To Channel
    • Alternatively use an existing channel:

      Your channel will need a From/To Translator or a Filter component.

  2. Connect to a database using ODBC:
    • Paste this code into the Translator:

      If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote MySQL database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

      function main()
            local conn = db.connect{   
            api=db.MY_SQL, 
            name='your_server_name',
            user='your_login', 
            password='secret',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The name is the same as the server entry in the odbc.ini file.
  3. Adapt the code to your requirements.

Setup ODBC [top]

Note: Iguana may not always support the very latest version of MySQL ODBC, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of MySQL are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

CentOS:

Note: The procedure and screenshots in this section are from CentOS 7 — and may differ somewhat if you are using a different version of CentOS , or another Red Hat based Linux.

  1. Install the MySQL Yum Repository:
    1. Download the MySQL Yum Repository:
    2. Choose: Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package

      CentOS 7 is based on and fully compatible with) Red Hat Enterprise Linux 7.

    3. Install the yum repository rpm package:

      Use this terminal command to load the package file:
      sudo <downloads directory>/<download name>.rpm
      For example:

      sudo rpm -ivh /home/parallels/Downloads/mysql80-community-release-el7-1.noarch.rpm
  2. Install the unixODBC package:
    1. Terminal Command: Get the latest package info:
      yum check-update
    2. Terminal Commands: install unixodbc:
      sudo yum install unixODBC.x86_64
      sudo yum install unixODBC-devel.x86_64
  3. Install MySQL Connector/ODBC:
    1. Terminal Command: Install MySQL Connector/ODBC
      sudo yum install mysql-connector-odbc.x86_64
    2. Check file names and create symbolic links to the versioned file names if needed:
      • Terminal Command: Check to see if the expected files exist:
        ls /usr/lib64/libmyodbc* -l
      • And as we can see the the files exist:
        centos lib files check
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum UNICODE driver (libmyodbc8w.so):
        Note: Change [My UNICODE MySQL connection name] to the name you prefer for your ODBC connection.

        [My UNICODE MySQL connection name]
        Driver = /usr/lib64/libmyodbc8w.so
        Server = your_server_name
        Port   = 3306
      • Bare minimum ANSI driver (libmyodbc8a.so):
        [My ANSI MySQL connection name]
        Driver = /usr/lib64/libmyodbc8a.so
        Server = your_server_name
        Port   = 3306
      • UNICODE with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My UNICODE MySQL connection name]
        Driver   = /usr/lib64/libmyodbc8w.so
        Server   = your_server_name <network name or IP address for the server>
        Port     = 3306 <3306 = default - a different port can be used>
        Database = dbname <if omitted the default database will be used>
        Username = dbusername <if omitted then integrated security is used - needs kerberos>
        Password = dbpassword
      • ANSI with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My ANSI MySQL connection name]
        Driver   = /usr/lib64/libmyodbc8a.so
        Server   = your_server_name <network name or IP address for the server>
        Port.    = 3306 <3306 = default - a different port can be used>
        Database = dbname <if omitted the default database will be used>
        Username = dbusername <if omitted then integrated security is used - needs kerberos>
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini MySQL entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Ubuntu:

Note: The procedure and screenshots in this section are from Ubuntu 18.04 — and may differ somewhat if you are using a different version of Ubuntu, or another Debian based Linux.

  1. Install the unixODBC package:
    1. Terminal Command: Get the latest package info:
      sudo apt update
    2. Terminal Commands: Install unixodbc:
      sudo apt install unixodbc
      sudo apt install unixodbc-dev
  2. Install MySQL Connector/ODBC:
    1. Download the Connector/ODBC TAR Archive:
      1. From: https://dev.mysql.com/downloads/connector/odbc/
    2. Choose: Ubuntu Linux 18.04 (x86, 64-bit), Compressed TAR Archive

      This assumes you are using Ubuntu 18.04 — otherwise choose the TAR to match the version you are using.

    3. Terminal Commands: To extract the archive:
      gunzip mysql-connector-odbc-8.0.12-i686-pc-linux.tar.gz
      tar xvf mysql-connector-odbc-8.0.12-i686-pc-linux.tar
    4. Terminal Command: Change directory to directory containing the extracted archive:

      cd <..>/Downloads/<extraction directory>
      for example:

      cd /home/parallels/Downloads/mysql-connector-odbc-8.0.13-linux-ubuntu18.04-x86-64bit
    5. Terminal Commands: Copy the lib and bin subdirectories to /usr/local/… (or another location):
      sudo cp bin/* /usr/local/bin
      sudo cp lib/* /usr/local/lib
    6. Check file names and create symbolic links to the versioned file names if needed:
      1. Terminal Command: Check if the expected files exist:
        ls /usr/local/lib/libmyodbc* -l
      2. And as we can see the the files exist:
        check lib files
  3. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum UNICODE driver (libmyodbc8w.so):
        Note: Change [My UNICODE MySQL connection name] to the name you prefer for your ODBC connection.

        [My UNICODE MySQL connection name]
        Driver = /usr/local/lib/libmyodbc8w.so
        Setup  = /usr/local/lib/libmyodbc8S.so
        Server = your_server_name
        Port   = 3306
      • Bare minimum ANSI driver (libmyodbc8a.so):
        [My ANSI MySQL connection name]
        Driver = /usr/local/lib/libmyodbc8a.so
        Setup  = /usr/local/lib/libmyodbc8S.so
        Server = your_server_name
        Port   = 3306
      • UNICODE with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My UNICODE MySQL connection name]
        Driver   = /usr/local/lib/libmyodbc8w.so
        Setup    = /usr/local/lib/libmyodbc8S.so
        Server   = your_server_name <network name or IP address for the server>
        Port     = 3306 <3306 = default - a different port can be used>
        Database = dbname <if omitted the default database will be used>
        Username = dbusername <if omitted then integrated security is used - needs kerberos>
        Password = dbpassword
      • ANSI with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My ANSI MySQL connection name]
        Driver   = /usr/local/lib/libmyodbc8a.so
        Setup    = /usr/local/lib/libmyodbc8S.so
        Server   = your_server_name <network name or IP address for the server>
        Port     = 3306 <3306 = default - a different port can be used>
        Database = dbname <if omitted the default database will be used>
        Username = dbusername <if omitted then integrated security is used - needs kerberos>
        Password = dbpassword
  4. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  5. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (4)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (4)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini MySQL entry
  6. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Mac:

We were not able to run the MySQL ODBC drivers using unixODBC, as they appear to be dependent on the default libiodbc.dylib driver manager supplied with the macOS. For this reason we strongly recommend that you connect to MySQL using the libmysql shared library instead.

Warning: We found that other ODBC drivers work better with the unixODBC driver manager, see Setup unixODBC on the Mac. And for this reason we use the unixODBC driver manager for all other ODBC database connections.

If you use unixODBC for ODBC connections to Oracle, Microsoft SQL Server, PostgreSQL etc, then you cannot use ODBC with MySQL — you must use a libmysql connection instead.

Note: The procedure and screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

  1. Install the MySQL ODBC drivers:
    1. Download the Connector/ODBC TAR Archive:
      1. From: https://dev.mysql.com/downloads/connector/odbc/
    2. Choose: macOS 10.14 (x86, 64-bit), Compressed TAR Archive

      This assumes you are using Mojave or High Sierra. Otherwise choose the TAR Archive that corresponds to your version of macOS.

    3. Terminal Command: Extract the archive:
      tar xvzf mysql-connector-odbc-8.0.13-macos10.14-x86-64bit.tar.gz
    4. Terminal Command: Change directory to directory containing the extracted archive:

      cd <..>/Downloads/<extraction directory>
      for example:

      cd ~/Downloads/mysql-connector-odbc-8.0.13-macos10.14-x86-64bit
    5. Terminal Commands: Copy the lib and bin subdirectories to /usr/local/… (or another location):
      sudo cp bin/* /usr/local/bin
      sudo cp lib/* /usr/local/lib
  2. Install the ODBC Manager:
    1. Download the ODBC Manager dmg installer from: http://www.odbcmanager.net/
    2. Open the dmg file and double click on ODBC Manager.pkg to run the installer:
      ODBC Manager package
    3. Accept the defaults.
  3. Register the MySQL ODBC drivers using ODBC Manager:
    1. Go to the Drivers tab and click Add:
      add odbc driver
    2. Enter the Unicode Driver credentials:

      If you installed the Driver File in a different directory then use that instead, also you can choose a different Driver Name if you prefer.

      • Driver Name: MySQL ODBC 8.0 Unicode Driver
      • Driver File: /usr/local/mysql-connector-odbc-8.0.13-macos10.14-x86-64bit/lib/libmyodbc8w.so
      • Define As: Ensure that System is checked
        enter odbc driver credentials
    3. Optional: Create a second Ansi Driver:
      • Driver Name: MySQL ODBC 8.0 Ansi Driver
      • Driver File: /usr/local/mysql-connector-odbc-8.0.13-macos10.14-x86-64bit/lib/libmyodbc8a.so
      • Define As: Ensure that System is checked
    4. The drivers will look something like this:
      two new ODBC drivers
  4. Configure the ODBC Source:
    1. Enter the ODBC Data Source credentials:
      • Data Source Name (DSN): My UNICODE MySQL connection (or another name you prefer)
      • (Optional) Description: Use a meaningful description
      • Click Add to create the DSN:
        create DSN
    2. Use Add to enter two Keyword/Value pairs:
      • Keyword: Server
      • Value: localhost
      • Keyword: Port
      • Value: 3306
      • Then click OK to save the settings:
        odbc keyword value
  5. If you have problems configuring the ODBC driver or connection speak to your DBA (database administrator).

Troubleshooting [top]

  • Problems with connection settings:

    Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

  • Iguana may not always support the very latest version of the ODBC drivers:

    Please contact us at support@interfaceware.com if you need to know the latest supported version of the ODBC drivers.

  • Incorrect port number:

    MySQL defaults to port 3306, but a different port can be used. Speak to your database administrator (DBA) or network administrator.

  • Using integrated security:

    Kerberos is needed on Linux, this SQL Server page Using Integrated Authentication has some information

  • If you get a blank error message like this:
    blankODBC error message

    We got this error when trying to use the MySQL ODBC drivers with unixODBC with the macOS. You need to use the the default libiodbc.dylib driver manager supplied with the macOS instead. However we strongly recommend that you connect to MySQL using the libmysql shared library instead.

  • Versioned Packages:

    When a package installs a shared library, it can append a version number, so double check the actual library names and if needed symlink them to the library names shown in the previous section (libtdsodbc.so & libtdsS.so), for example:

    sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.1 /usr/lib/x86_64-linux-gnu/libodbc.so

If you need more help please contact us at support@interfaceware.com.

More Information [top]

Leave A Comment?