> For the complete documentation index, see [llms.txt](https://docs.erathos.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.erathos.com/connectors/databases/mysql.md).

# 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

![](https://help.erathos.com/rails/active_storage/blobs/redirect/eyJfcmFpbHMiOnsibWVzc2FnZSI6IkJBaHBBYjg9IiwiZXhwIjpudWxsLCJwdXIiOiJibG9iX2lkIn19--0141d7bbc892fd163d35c018ab8828ac0288b921/mysql%20form.png)

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

![](https://help.erathos.com/rails/active_storage/blobs/redirect/eyJfcmFpbHMiOnsibWVzc2FnZSI6IkJBaHBBY0E9IiwiZXhwIjpudWxsLCJwdXIiOiJibG9iX2lkIn19--e4aa49f32c9efb5103c03f86e5e613bc8ec655e9/SSH%20MySQL.png)

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

![](https://help.erathos.com/rails/active_storage/blobs/redirect/eyJfcmFpbHMiOnsibWVzc2FnZSI6IkJBaHBBZFk9IiwiZXhwIjpudWxsLCJwdXIiOiJibG9iX2lkIn19--df86f4129465181563c0c9ec75f60dacddeb2c5a/IP%20Fixo%20Firebire.png)

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

![](https://help.erathos.com/rails/active_storage/blobs/redirect/eyJfcmFpbHMiOnsibWVzc2FnZSI6IkJBaHBBaEVCIiwiZXhwIjpudWxsLCJwdXIiOiJibG9iX2lkIn19--95ff8235b083b1c88751d5ca3111e682b9c5d141/SSH%20MySQL.png)

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:

```sql
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.

```sql
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
```

{% hint style="warning" %}
Important: You must restart the database to apply changes to the binlog settings above.
{% endhint %}

{% hint style="danger" %}
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.
{% endhint %}

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

```sql
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.