Skip to Content

Connect to MySQL using the Database connector in Jitterbit Integration Studio

This page details how to connect to MySQL using the Database connector.

We recommend using the MySQL JDBC driver for MySQL that ships with the Jitterbit agent. Using the JDBC driver is recommended for additional features, such as being able to provide a manual query, that are not available through an ODBC connection.

If you want to use an ODBC driver (private agents only), one can be downloaded at MySQL Community Downloads, Connector/ODBC.

ODBC and JDBC

After the driver is installed, specify these inputs during configuration of the Database connection:

  • Driver Type: Select either ODBC or JDBC as appropriate.

  • Driver: Select the MySQL driver. The driver is typically called "MySQL" for JDBC or "MySQL ODBC 3.51 Driver" for ODBC.

  • Server Name: Enter the name or IP address of the database server.

  • Database Name: Enter the name of the catalog that Harmony needs to access.

  • Login and Password: Enter the user credentials for Harmony to use to access the database.

Note

If the ODBC driver does not display in the Driver dropdown in Integration Studio, use the ODBC manager on the private agent to see if the driver is visible. Check to see if the driver is listed in the Data Sources (ODBC) Windows applet (under Administrative Tools).

If you are still unable to see the driver after seeing it in the ODBC manager, double-check that you are connecting to the correct machine. To do so, stop the Jitterbit Apache service on that machine and make sure you can no longer connect using the client.

Permission errors

If you get permission errors such as "Access denied for user 'root'@'%' to database 'test'" and you are certain that the credentials are correct, it may be a MySQL configuration problem. You can configure MySQL to accept different credentials for different IP addresses or a group of addresses. See the MySQL documentation or contact your MySQL administrator.

Ports

The port required to allow the Jitterbit agent to communicate with a remote MySQL database depends on the port that MySQL has been configured to listen to. Open that port (default 3306) on the machine where the MySQL database server is running.

Secure or encrypt the connection

Securing the connection between a database and a private agent depends on the ODBC driver being used. The standard MySQL ODBC driver does not use encryption. The best way to enable such encryption is by tunneling the connection through SSH. This can be done on private agents only.

WHERE clause examples

These WHERE clause examples are provided for reference purposes when configuring a Database query activity:

WHERE
    jobtitle = 'Sales Rep' AND
    officeCode = 1;

WHERE
    jobtitle <> 'Sales Rep';

WHERE
    officecode > 5;

WHERE
    officecode <= 4;

WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

WHERE key_part1=constant;

WHERE key_part1=val1 AND key_part2=val2;

For additional examples, refer to Basic MySQL Tutorial and to the MySQL Reference Manual article WHERE Clause Optimization.