The tap-mysql extractor pulls data from a MySQL or MariaDB database.

Getting Started

Prerequisites

If you haven’t already, follow the initial steps of the Getting Started guide:

  1. Install Meltano
  2. Create your Meltano project

Then, follow the steps in the “Setup requirements” section of the documentation.

Installation and configuration

Using the Command Line Interface

  1. Add the tap-mysql extractor to your project using meltano add:

     meltano add extractor tap-mysql
    
  2. Configure the settings below using meltano config.

Using Meltano UI

  1. Start Meltano UI using meltano ui:

     meltano ui
    
  2. Open the Extractors interface at http://localhost:5000/extractors.
  3. Click the “Add to project” button for “MySQL”.
  4. Configure the settings below in the “Configuration” interface that opens automatically.

Next steps

Follow the remaining steps of the Getting Started guide:

  1. Select entities and attributes to extract
  2. Choose how to replicate each entity

    Supported replication methods: LOG_BASED, INCREMENTAL, FULL_TABLE

  3. Add a loader to send data to a destination

    Note that this extractor is incompatible with the default datamill-co variants of target-postgres and target-snowflake, because they don’t support stream names that include the source schema in addition to the table name: <schema>-<table>, e.g. public-accounts.

    Instead, use the transferwise variants that were made to be used with this extractor: target-postgres and target-snowflake.

  4. Run a data integration (EL) pipeline

If you run into any issues, learn how to get help.

Settings

tap-mysql requires the configuration of the following settings:

These and other supported settings are documented below. To quickly find the setting you’re looking for, use the Table of Contents in the sidebar.

Minimal configuration

A minimal configuration of tap-mysql in your meltano.yml project file will look like this:

plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    config:
      host: mysql.example.com
      port: 3306
      user: my_user

Sensitive values are most appropriately stored in the environment or your project’s .env file:

export TAP_MYSQL_PASSWORD=my_password

Host

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set host <host>

export TAP_MYSQL_HOST=<host>

Port

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set port 3307

export TAP_MYSQL_PORT=3307

User

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set user <user>

export TAP_MYSQL_USER=<user>

Password

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set password <password>

export TAP_MYSQL_PASSWORD=<password>

Database

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set database <database>

export TAP_MYSQL_DATABASE=<database>

SSL

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set ssl true

export TAP_MYSQL_SSL=true

Filter DBs

Comma separated list of schemas to extract tables only from particular schemas and to improve data extraction performance

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config tap-mysql set filter_dbs <schema1>,<schema2>

export TAP_MYSQL_FILTER_DBS=<schema1>,<schema2>

Session SQLs

  • Name: session_sqls
  • Environment variable: TAP_MYSQL_SESSION_SQLS
  • Default:

    [
      "SET @@session.time_zone='+0:00'",
      "SET @@session.wait_timeout=28800",
      "SET @@session.net_read_timeout=3600",
      "SET @@session.innodb_lock_wait_timeout=3600"
    ]
    

List of SQL commands to run when a connection made. This allows to set session variables dynamically, like timeouts.

How to use

Manage this setting directly in your meltano.yml project file:

plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    config:
      session_sqls:
        - SET @@session.<variable>=<value>
        # ...

Alternatively, manage this setting using meltano config or an environment variable:

meltano config tap-mysql set session_sqls '["SET @@session.<variable>=<value>", ...]'

export TAP_MYSQL_SESSION_SQLS='["SET @@session.<variable>=<value>", ...]'