Skip to main content

Setup MySQL source

Updated

Connects directly to a MySQL database, letting you extract chosen schemas, tables and fields — optionally through a bastion tunnel.

Setup at a glance

  1. 01MySQLAcquire credentials
  2. If neededBastionKey & Auth
  3. 02KleeneComplete Setup
  4. Connected
  • Connector type Database
  • Auth method Database credentials

Before you start

  • MySQL version 8.0.0 or higher
  • The ability to whitelist Kleene's IP addresses
  • Bastion host access arranged — only if you connect through one

Connect MySQL to Kleene

  1. Get your MySQL credentials

    1. Whitelist Kleene's IPs. You will need to whitelist 54.78.204.135 and 34.242.207.164.
    2. Gather your connection details. If you don't already know them: the hostname can be found in Hosting → Manage → MySQL database; the port by executing show variables where variable_name = 'port'; and the user by executing SELECT CURRENT_USER(); in an IDE connected to the database; and the password by following these steps.

  2. Add the source in Kleene

    1. In the Kleene app, go to Add a source.
    2. Scroll down or search for MySQL.
    3. Click Connect to open the credentials panel.

  3. Enter your credentials and connect

    • HostnameBy default localhost
    • PortBy default 3306
    • Database UserThe user Kleene should connect as
    • Database Password
    • Database nameThe database you would like to connect to
    • Extra parametersEnter exactly: zeroDateTimeBehavior=ConvertToNull&enabledTLSProtocols=TLSv1.2
    • Requires tunnelingTick this only if connecting via a bastion, then enter your Bastion Host credentials — see Bastion server set-up

    The new source now appears on the Sources screen.

Good to know

Connecting through a bastion

If the database sits behind a bastion, tick Requires tunneling and add your Bastion Host credentials. Kleene authenticates over SSH with a key, so you supply a private .pem file. Setting up a bastion server covers the host side.

Before you extract — MySQL limits and report notes

This guide covers source setup only. When you move on to creating extracts, these are the connector-specific things to know.

Connector capabilities

Incremental Yes — you select the incremental key at extract level

What you can extract

There are no fixed reports — you choose the schema, table and fields at extract level. See the full MySQL connector documentation for the complete list of options.

Extract notes

  • You will be asked to select a schema, then a table to extract.
  • You will also have the option to select the fields you wish to pull back.
  • Select your load method and Extract Type.
  • If you select incremental, you will need to select your incremental key — see how incremental works.
  • Kleene pre-selects the primary key so data is pulled without duplicates or missing rows. If that selection is wrong or empty, override it correctly.

Limits and gotchas

Don't change selected fields on a running extract

If you change the fields you have selected after the extract has been running, any further extracts on append will fail due to the column mismatch. To add new fields, run a new extract on Drop, Create, Load to a new destination table, then set your extract to append onto this new table.

Related to

Powered by Zendesk