> ## Documentation Index
> Fetch the complete documentation index at: https://docs2.zenskar.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL

## Overview

MySQL is a widely used, open-source relational database known for its speed, ease of use, and strong community support. In Zenskar, you can use MySQL as a data source to import transactional, usage, or customer data for metering, billing, and analytical reporting.

## Prerequisites

### Create a dedicated read-only user in MySQL (recommended)

Create a dedicated read-only database user for replicating data. Alternatively, you may use an existing MySQL user in your database.

```sql theme={null}
CREATE USER 'zenskar'@'%' IDENTIFIED BY 'your_password_here';
```

For CDC replication, grant appropriate permissions:

```sql theme={null}
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zenskar'@'%';
```

### Enable binary logging on your MySQL server

You must enable binary logging for MySQL replication using CDC. Most cloud providers (AWS, GCP, etc.) provide easy one-click options for enabling the binlog on your source MySQL database.

If you are self-managing your MySQL server, configure your MySQL server configuration file with the following properties:

```
server-id                  = 223344
log_bin                    = mysql-bin
binlog_format              = ROW
binlog_row_image           = FULL
binlog_expire_logs_seconds  = 864000
```

| Parameter                    | Description                                                                                                                   | Notes                                                                                                                                     |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `server-id`                  | Must be unique for each server and replication client in the MySQL cluster. Use a non-zero value between 1 and 4,294,967,295. | Any non-zero integer. [Docs](https://dev.mysql.com/doc/refman/8.0/en/replication-options.html#sysvar_server_id)                           |
| `log_bin`                    | Base name of the binary log file sequence. Required for enabling replication.                                                 | Ensure it is set. [Docs](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_log-bin)               |
| `binlog_format`              | Defines how changes are written to the binary log. Must be set to `ROW` for CDC.                                              | `ROW`. [Docs](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_format)                           |
| `binlog_row_image`           | Controls how row images are written to the binary log. Must be set to `FULL`.                                                 | `FULL`. [Docs](https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_row_image)                       |
| `binlog_expire_logs_seconds` | Number of seconds before binlog files are automatically removed. Ensures recovery bandwidth in case of sync failure or pause. | `864000` (10 days). [Docs](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds) |

## Set up a MySQL data-source connector via Zenskar dashboard

1. Log into your Zenskar dashboard.
2. In the left side bar, click **Usage > Data Sources**.
3. In the top-right corner, click **+ ADD DATA SOURCE**.
4. On the **Add New Data Source** page, [configure the MySQL connector](https://docs.zenskar.com/docs/data-source-connector-for-mysql#connector-configuration).
5. Click on the **SAVE SOURCE** button.

### Connector configuration

#### General configuration

| Field           | Description                                   | Required |
| :-------------- | :-------------------------------------------- | :------- |
| **Source Name** | Enter a unique name for this data source.     | Yes      |
| **Source Type** | Select **ClickHouse** from the dropdown menu. | Yes      |

#### Connector configuration

| **Field**          | **Description**                                                                                                                                                                       |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **SSL Connection** | Select **Yes** or **No** depending on whether you want Zenskar to establish an SSL connection with the data source.                                                                   |
| **Host**           | The hostname or IP address of the MySQL server.                                                                                                                                       |
| **Port**           | The port number on which MySQL listens for incoming requests.                                                                                                                         |
| **Database**       | The name of the database to connect to.                                                                                                                                               |
| **Username**       | The read-only database user that Zenskar will use to access data.                                                                                                                     |
| **Password**       | The password associated with the database user.                                                                                                                                       |
| **SSL Mode**       | Choose from `prefer`, `require`, `verify-ca`, or `verify-full`. Refer to the [SSL modes section](https://www.zenskar.com/docs/data-source-connector-for-mysql#ssl-modes) for details. |

* Refer the [section on SSH tunneling](https://www.zenskar.com/docs/data-source-connector-for-mysql#-connect-with-ssl-or-ssh-tunneling) for details on all SSH-related configuration parameters.
* **Replication Method**: select from **Standard** and **Logical Replication (CDC)**. Refer the section on [MySQL replication modes](https://www.zenskar.com/docs/data-source-connector-for-mysql#-mysql-replication-modes) for more details.

> 🚧 Note
>
> You must allow inbound traffic from [Zenskar IP addresses](https://www.zenskar.com/docs/zenskar-ips).

## Addendum

### MySQL replication modes

#### Change data capture (CDC)

Zenskar uses logical replication of the [MySQL binlog](https://dev.mysql.com/doc/refman/8.0/en/binary-log.html) to incrementally capture deletes. We recommend configure your MySQL source with CDC whenever possible, as it provides:

* A record of deletions, if needed.
* Scalable replication to large tables (1 TB and more).
* A reliable cursor not reliant on the nature of your data. For example, if your table has a primary key but doesn't have a reasonable cursor field for incremental syncing (i.e. updated\_at), CDC allows you to sync your table incrementally.

#### Standard

Zenskar offers incremental replication using a custom cursor available in your source tables (e.g. `updated_at`). We generally recommend against this replication method, but it is well suited for the following cases:

* Your MySQL server does not expose the binlog.
* Your data set is small, and you just want snapshot of your table in the destination.

### Connect with SSL or SSH Tunneling

#### SSL Modes

Here is a breakdown of available SSL connection modes:

* `disable` to disable encrypted communication between Zenskar and the source
* `allow` to enable encrypted communication only when required by the source
* `prefer` to allow unencrypted communication only when the source doesn't support encryption
* `require` to always require encryption. Note: The connection will fail if the source doesn't support encryption.
* `verify-ca` to always require encryption and verify that the source has a valid SSL certificate
* `verify-full`to always require encryption and verify the identity of the source

### Connect via SSH Tunnel

You can connect to a MySQL server via an SSH tunnel.

When using an SSH tunnel, you are configuring Zenskar to connect to an intermediate server (also called a bastion or a jump server) that has direct access to the database. Zenksar connects to the bastion and then asks the bastion to connect directly to the server.

To connect to a MySQL server via an SSH tunnel:

1. While setting up the MySQL source connector, from the SSH tunnel dropdown, select:
   1. **SSH Key Authentication** to use a private as your secret for establishing the SSH tunnel
   2. **Password Authentication** to use a password as your secret for establishing the SSH Tunnel

2. For **SSH Tunnel Jump Server Host**, enter the hostname or IP address for the intermediate (bastion) server that Zenskar will connect to.

3. For **SSH Connection Port**, enter the port on the bastion server. The default port for SSH connections is 22.

4. For **SSH Login Username**, enter the username to use when connecting to the bastion server.

> 🚧 Note
>
> This is the operating system username and not the MySQL username.

1. For authentication:
   1. If you selected **SSH Key Authentication**, set the **SSH Private Key** to the private key that you are using to create the SSH connection.
   2. If you selected Password Authentication, enter the password for the operating system user to connect to the bastion server.

> 🚧 Note
>
> This is the operating system password and not the MySQL password.

### Generating a private key for SSH Tunneling

The connector expects an RSA key in PEM format. To generate this key:

```shell theme={null}
ssh-keygen -t rsa -m PEM -f myuser_rsa
```

This produces the private key in pem format, and the public key remains in the standard format used by the `authorized_keys` file on your bastion host. The public key should be added to your bastion host to whichever user you want to use with Zenskar. The private key is provided via copy-and-paste to the Zenskar connector configuration screen, so it may log in to the bastion.