Articles in this section

Set up a connection to MySQL

Kathyana Queeman
Updated

A MySQL connection stores the host, credentials, and options integrator.io uses to reach your MySQL database, so you can use the database as a source or destination in flows. This article describes each field on the MySQL connection form and the additional server requirements that apply when a MySQL listener uses the connection to stream changes in real time.

MySQL links: API guide, Authentication

Connection form fields

  1. Name (required): Enter the name you'll use to refer to this connection in integrator.io.
  2. Connection type (required): Select MySQL, or accept the default if you're editing a MySQL connection. The type appears as rdbms in your connections list.
  3. Mode (required): Select one of the following:
    • Cloud to connect to a publicly accessible server.
    • On-premise to connect to a server that isn't publicly accessible, for example a server inside a private AWS VPC or your own data center, through an on-premise agent.
  4. Agent (required when Mode is On-premise; otherwise unavailable): Select an agent from the list. An on-premise agent is a small application installed on a networked computer inside your network. It establishes a secure tunnel to integrator.io, so you don't need to allow integrator.io's IP addresses through your firewall. When you install an agent, you specify an access token, and the agent then appears in this list. One agent can serve multiple connections.
  5. Host (required): Enter the server's hostname or endpoint — for example, the endpoint shown for your instance in the Amazon RDS console, or the address your DBA provides for a self-managed server. If your database is provided by a web hosting service, the hostname is usually in the hosting control panel:
    1. Sign in to your host's Control Panel and select the MySQL Databases menu.
      MySQL Databases menu in a hosting control panel
    2. Locate your MySQL Database information on the page that opens. 
      MySQL database details in a hosting control panel
    3. Copy the MySQL Host value, and enter it in Host.
  6. Database name (required): Enter the database name your administrator configured. In a hosting control panel, this is usually shown as the MySQL Database value.
  7. Username (required): Enter the database username created for this account.
  8. Password (required): Enter the password for the database account.
  9. Port (required): Enter the TCP/IP port the MySQL server listens on. The default is 3306.
  10. Use SSL (optional): Turn on this setting to encrypt data in transit between integrator.io and the database.
  11. Certificate authority (optional; available when Use SSL is on): If the database uses a certificate that doesn't match or chain to a known certificate authority (CA), enter a CA certificate that the server's certificate can match or chain to. For a self-signed certificate, the certificate is its own CA and you must provide it. You must re-enter the certificate each time you edit this connection. See MySQL documentation on creating SSL and RSA certificate keys.
  12. Configure properties (optional): Select this to add name-value pairs that integrator.io sends to MySQL with the connection. Select Save & close to return to the connection form.

Requirements for a MySQL listener (CDC)

A MySQL connection used by a listener needs MySQL 8.0 or later, row-based binary logging on the server, and replication privileges on the database user. A MySQL listener streams inserts, updates, and deletes from the database's binary log (binlog) as they happen; these requirements don't apply to scheduled, query-based exports.

  • The server runs MySQL 8.0 or later (8.0.x, 8.4.x, 9.0, or 9.1). MySQL 5.7 isn't supported for CDC.
  • The database is hosted on Amazon RDS for MySQL, Amazon Aurora MySQL, or a self-managed MySQL server. Azure Database for MySQL and Google Cloud SQL for MySQL aren't validated for CDC.
  • The server is configured for row-based binary logging: log_bin = ON, binlog_format = ROW, and binlog_row_image = FULL.
  • The database user has, at minimum, the REPLICATION SLAVE and REPLICATION CLIENT privileges and SELECT on the tables to capture. The replication privileges are global (ON *.*) and can't be limited to one database.

If the server isn't configured this way, the listener fails to start and the flow reports a DEBEZIUM_ENGINE_ERROR. For the listener procedure, see Listen for real-time changes from MySQL. For statements that check each setting, see FAQ – MySQL listener for change data capture (CDC).

Optional concurrency and rate limit settings

  • Borrow concurrency from (optional): Select another connection from the list. By default, data flowing through a connection is submitted to the endpoint at the concurrency level set on that connection. When several connections need to share one concurrency level, this field specifies which connection to borrow from, and data from both connections is submitted to the endpoint under the shared level.
  • Auto-recover rate limit errors: On by default with a predefined Target concurrency level; you can turn it off. See Auto-recover rate limit errors. Changing any of these settings requires admin or manage permissions.
  • Target concurrency level: Available only when Auto-recover rate limit errors is on. Change the predefined value to limit the number of concurrent requests the connection allows. See Setting concurrency level on a connection.
  • Concurrency level: When Auto-recover rate limit errors is on, you can't change this value because integrator.io sets it automatically. When it's off, enter a value to limit the number of concurrent requests the connection allows. See Setting concurrency level on a connection.

Read more about this connector

Related articles