• Introduction
• Connect to MySQL
• Setup libmysql
• Making the MySQL Database Option Visible in Windows
• Troubleshooting
• More Information

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.

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]

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.

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:
     
   • Click Next.
   • Ignore the two failed Check Requirements:
     
     • 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:
   
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:
     
   • 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:
   
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:
     
7. Your installed library should look something like this:
   

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.

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:
     
   • 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:
   
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:
     
   • 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.

To make the MySQL database option visible:

1. Search for “Environment” and choose Edit the system environment variables:
   
2. The System Properties window will open on the Advanced page:
   
3. Click the Environment Variables button near the bottom of the page:
   
4. Highlight the System Path variable and click Edit:
   
5. The Edit environment variable window appears.
6. Add the location of your libmysql.dll file on the next line:
   
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:
   

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

• Using Database Connection Objects
• Forum thread on Windows ODBC
• Connect to MySQL from Windows with ODBC
• Connect to MySQL from Linux or Mac with ODBC