MySQL

Find out how to connect MySQL to your data warehouse effortlessly.

In this page you will learn how to connect MySQL to Erathos to move your data seamlessly.

The available connections between databases and the Erathos platform are through Open Connection, Static IP and SSH tunnel.

Connecting MySQL:

1. Log in to your Erathos account

2. Select MySQL as connector

3. Name your connection

4. Fill in the following form with the required information

On the Advanced config toggle button, you can select which connection you'd like to perform.

If you choose to connect through Static IP, you will need to follow the instuction below:

If you choose to connect through SSH Tunnel, you will need to fill in the additional form below:

When using CDC, the following values may be used in cdc_snapshot_mode:

• initial: performs an initial snapshot, after it completes, streaming following changes from the binlog.

• no_data: does not perform any snapshot, only streams changes that are currently available in the binlog.

Don't forget to click on Save and close to advance to the next steps of the integration.

With this connection created you can seemlessly move your MySQL data to your BigQuery, Redshift or Postgres data-warehouse.

CDC setup

In order to enable CDC in your MySQL connection you need to confirm the following settings in your database.

The user used for the connection must have the following privileges granted:

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'your_existing_user';
FLUSH PRIVILEGES;

• SELECT: reads rows from tables. Used only when performing a snapshot.

• RELOAD: allows flushing/acquiring locks. Used only when performing a snapshot.

• SHOW DATABASES: allows listing database names. Used only when performing a snapshot.

• REPLICATION SLAVE: allows connecting to and reading the binlog.

• REPLICATION CLIENT: allows checking binlog/replication status. Always required.

Binary logging must be enabled in ROW format with FULL row images, otherwise UPDATE/DELETE events will not carry the complete before/after state of the affected rows. The following query can be used to check the current configuration.

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';

-- If necessary, add the following to your MySQL configuration file (my.cnf) and restart the server:
-- server-id                  = 223344 (must be unique for every server/replication client in the cluster)
-- log_bin                    = mysql-bin
-- binlog_format              = ROW
-- binlog_row_image           = FULL
-- binlog_expire_logs_seconds = 864000

Important: You must restart the database to apply changes to the binlog settings above.

Important: binlog_expire_logs_seconds controls how long binlog files are retained before being automatically purged (MySQL's default is 30 days). If a binlog file that CDC still needs to read gets purged before it does so, the connection will need to perform a fresh initial snapshot to recover.

For UPDATE events to be captured correctly, binlog_row_value_options must not be set to PARTIAL_JSON.

SHOW GLOBAL VARIABLES WHERE variable_name = 'binlog_row_value_options';

-- If set to PARTIAL_JSON, unset it with:
SET @@global.binlog_row_value_options = "";

Each replication client connected to a given MySQL server or cluster, including this connection, must use a unique numeric server-id. By default Erathos derives one automatically from the connection, but you can set your own value through the cdc_server_id field if you need to guarantee it does not collide with other replicas or CDC consumers already attached to this database.