Connect to MySQL with libmysql

Introduction

This article explains how to connect to MySQL databases for Windows, Linux and Mac.

It covers the following topics:

  1. How to connect to MySQL (with code samples).
  2. How to setup the libmysql shared library:
    • Install libmysql
    • Configure Iguana to use libmysql

If you are having trouble getting Windows to recognize MySQL you should look at the last section Making the MySQL Database Option Visible in Windows.

Note: If you are using a 64 bit Windows you should usually use a 64 bit version of MySQL. If you are using 32 bit Windows you will need to use an older 32 bit version of MySQL (5.7.24 or earlier).

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 the libmysql shared library:
    • 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='mydatabase@server_name',
            user='your_login', 
            password='secret',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The location (name) of the host can be specified as any of the following:
      • The machine name: mydatabase@server_name
      • The server name: mydatabase@server_name
      • The IP address: mydatabase@10.10.10.10
    • If the MySQL server is using a port other the standard port of 3306, you can specify it as part of the name parameter:
      • mydatabase@server_name:1111
  3. Adapt the code to your requirements.

Setup libmysql [top]

Note: Iguana may not always support the very latest version of MySQL, 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.

Windows:

The easiest way to install the drivers on Windows is to use an MSI installer rather than a ZIP archive, as the install process is simpler.

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

  1. Download MySQL:
    • Go to the MySQL download site
    • Download the MSI install file for the latest version of MySQL for Windows
  2. Install the MySQL drivers:
    • Double click the MSI file to open it.
    • Click Yes to allow the installer to make changes on your machine.
    • Choose Client Only to install the client tools which includes the drivers:
      windows mysql install client
    • Click Next.
    • Ignore the two failed Check Requirements:
      ignore failed check items

      • Click Next to continue.
      • Click Yes to continue in the warning dialog box.
    • Follow the remaining steps to finish installing the MySQL client tools:
      • We strongly recommend accepting the default install values.
      • Note: Do not configure MySQL Router — just click Finish.
  3. In Iguana, click the Settings tab.
  4. Click IGUANA Setup > Database to view the available database APIs.
  5. Click the Edit button for MySQL Compliant:
    edit button MySQL
  6. Load the MySQL shared library for the version of MySQL you installed:

    We suggest various paths for the MySQL Library as this can change depending on the version of MySQL that you have installed. You should use the the latest driver which will normally be in the install directory that you use for MySQL.

    • Try these library paths:
      • C:\Program Files\MySQL\MySQL Server 8.0\lib\libmysql.dll
      • C:\Program Files\MySQL\MySQL Workbench 8.0 CE\libmysql.dll
      • C:\Program Files\MySQL\MySQL Router 8.0 CE\libmysql.dll
      • Otherwise search for libmysql.dll in the MySQL install directory
    • Paste the path into the Preferred Version text box:
      preferred version
    • If you installed MySQL in a different location (than the default) then you can browse to find the libmysqlclient.dll driver file.

Linux:

  1. Download MySQL:
    • Go to the MySQL download site
    • Choose Linux – Generic from the Select Operating System dropdown.
    • Download the TAR archive file for the latest version of MySQL.
  2. Install the libmysql driver:
    1. Terminal Commands: Unpack the TAR archive:

      This assumes file is <home>/Downloads, otherwise change the source path to match.

      cd ~/Downloads
      tar xvf ~/Downloads/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
    2. Copy the driver to the drivers directory:

      This directory can differ on different versions of Linux. For Ubuntu (and other Debian based Linux) use /usr/local/lib, for CentOS (and other Red Hat based Linux) use /usr/lib.

      1. Terminal Command: To copy the drivers for Ubuntu:

        Replace <version> to match the path where you unpacked the TAR file.

        sudo cp ~/Downloads/mysql-<version>-linux-glibc2.12-x86_64/lib/libmysqlclient.so* /usr/local/lib
      2. Terminal Command: To copy the drivers for CentOS:

        Replace <version> to match the path where you unpacked the TAR file.

        sudo cp ~/Downloads/mysql-<version>-linux-glibc2.12-x86_64/lib/libmysqlclient.so* /usr/lib
  3. In Iguana, click the Settings tab.
  4. Click IGUANA Setup > Database to view the available database APIs.
  5. Click the Edit button for MySQL Compliant:
    edit button MySQL
  6. Load the MySQL shared library:
    •  Choose Automatically choose which OCI Oracle shared library to load.
    • Alternatively (if Iguana does not find the driver) you can Browse to find the Library you just installed and click Save Changes:
      find libmysql library
  7. Your installed library should look something like this:
    libmysql installed

Mac:

The easiest way to install the libmysql shared library on a Mac is to install the full MySQL database server. We recommend using a DMG archive rather than a TAR archive, as the install process is simpler.

But you may find it easier to use ODBC connections on the Mac 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.

Tip: When you install the MySQL on Windows the installer includes a “drivers only” option. There is no “drivers only” option for the MacOS installer — so you need to install the full database engine.

  1. Download MySQL:
    • Go to the MySQL download site
    • Download the DMG install file for the latest version of MySQL for the MacOS
  2. Install MySQL:
    • Double click the DMG file to open it.
    • Double click the package to run it:
      MySQL install package
    • Follow the steps to install MySQL:

      We strongly recommend accepting the default install values.

  3. In Iguana, click the Settings tab.
  4. Click IGUANA Setup > Database to view the available database APIs.
  5. Click the Edit button for MySQL Compliant:
    edit button MySQL
  6. Load the MySQL shared library:

    If you are using an older version of MySQL it may use libmysqlclient_r.dylib instead of libmysqlclient.dylib.

    • Use the default library path: /usr/local/mysql/lib/libmysqlclient.dylib
    • Paste the default path into the Preferred Version text box:
      MySQL preferred version
    • If you installed MySQL in a different location (than the default) you will need to browse to find the libmysqlclient.dylib driver file.

Making the MySQL Database Option Visible in Windows [top]

You must have MySQL installed and the system PATH must be set correctly to ensure that the MySQL database option is visible in the database API drop-down menu.

Note: The paths for the MySQL Library directory as this can change depending on the version of MySQL that you have installed. You should use the the latest driver which will normally be in the install directory that you use for MySQL.

The library path will be something like this:

  • C:\Program Files\MySQL\MySQL Server 8.0\lib
  • C:\Program Files\MySQL\MySQL Workbench 8.0 CE
  • C:\Program Files\MySQL\MySQL Router 8.0 CE
  • Otherwise use the path for the MySQL install directory (which contains the libmysql.dll)

API drop down menu

Note: The screenshots in this section are from Windows 10 — the screens will be different for other versions of Windows.

To make the MySQL database option visible:

  1. Search for “Environment” and choose Edit the system environment variables:
    edit the envirnment variables
  2. The System Properties window will open on the Advanced page:
    system properties advanced
  3. Click the Environment Variables button near the bottom of the page:
    environment variables button
  4. Highlight the System Path variable and click Edit:
    highlight system path
  5. The Edit environment variable window appears.
  6. Add the location of your libmysql.dll file on the next line:
    add libmysql path
  7. Click OK to save your changes.
  8. Restart your computer for the changes to take effect.
  9. The MySQL database option should now be visible in the API drop-down menu:
    API drop down menu

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.

More Information

Leave A Comment?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.