# MySQL / MariaDB

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

To learn more about tap-mysql, refer to the repository at https://github.com/transferwise/pipelinewise-tap-mysql and documentation at https://transferwise.github.io/pipelinewise/connectors/taps/mysql.html.

# 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

  4. Run a data integration (EL) pipeline

# 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
    pip_url: pipelinewise-tap-mysql
    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>

# Export Batch Rows

Number of rows to export from MySQL in one batch.

# How to use

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

meltano config tap-mysql set export_batch_rows 100000

export TAP_MYSQL_EXPORT_BATCH_ROWS=100000

# 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
    pip_url: pipelinewise-tap-mysql
    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>", ...]'