# MySQL

> Bring tables from a MySQL or MariaDB database into your warehouse, on a schedule, with optional incremental reads.

## Before you start

You need three things on the MySQL side: a server sanda can reach, a user that can only read, and sanda's address allowed through your firewall.

- **A reachable server.** MySQL 8.0 or newer, or MariaDB 10.5 or newer. sanda connects from the internet, so the host name must resolve on the public internet. A private address, or a name that only works inside your network, is refused before sanda tries.
- **The connection details.** The host name, the port (3306 unless you changed it) and the name of the database.
- **A read-only user.** sanda only ever reads. Give it a user of its own, so its access is easy to see and easy to revoke:

```sql title="A read-only user for sanda"
create user 'becca_reader'@'%' identified by 'use-a-long-random-password';
grant select, show view on acme.* to 'becca_reader'@'%';
```

Replace `acme` with your database name. The `%` lets the user sign in from any address. To accept logins only from sanda, replace it with sanda's fixed address from the allowlist page.

- **sanda's address allowed.** Add sanda's address to your provider's network rules or security group. See [Allowlist sanda's address](https://docs.sanda-os.com.au/connections/allowlist).

## Connect MySQL

:::steps
1. **Choose MySQL.** Go to **Data · Connections**, press **New connection** and pick **MySQL**.
2. **Say where the database is.** Enter the host name alone in **Host**, with no port, and the name of the database in **Database**. **Port** sits under **Advanced settings** and starts at 3306.
3. **Enter the login.** Put the user's name in **User** and its password in **Password**.
4. **Check the encryption.** Under **Advanced settings**, **Encryption** opens on **required**, which always encrypts the connection and fails if the server cannot. **preferred** allows an unencrypted connection when the server does not support encryption. **verify_ca** and **verify_identity** also check the server's certificate, and ask for the **CA certificate**.
5. **Continue.** Press **Continue**, name the connection, and press **Read schema**. sanda signs in and lists the tables the user can read. If it fails, the message says why. See [Troubleshoot connections](https://docs.sanda-os.com.au/connections/troubleshooting).
:::

The rest of the flow, naming the connection, setting a frequency and choosing streams, is the same for every source. See [Add a connection](https://docs.sanda-os.com.au/connections/add-a-connection).

## What syncs

sanda reads the tables in the database you name. Each table you choose becomes a stream, and lands in your warehouse as its own table.

To limit which tables sanda can see, add a filter under **Table Filters** in **Advanced settings**. The field takes JSON: the database name, which must match **Database**, and a list of SQL `like` patterns for the table names to include.

```json title="Only the orders tables and customers"
[{"database_name": "acme", "table_name_patterns": ["orders%", "customers"]}]
```

**Check Table and Column Access Privileges** is on by default. During schema discovery sanda tests each table individually and leaves out any the user cannot read. A table missing from the stream list usually means the user lacks `select` on it.

### How changes are found

**Update Method**, under **Advanced settings**, decides how sanda can tell what has changed since the last run.

| Update Method | How it finds changes | Sees deletes | What your database needs |
|---|---|---|---|
| **Scan Changes with User Defined Cursor** | Reads the rows whose cursor column, such as `updated_at`, has moved since the last run | No | `select` only. You pick the cursor for each table in the stream list |
| **Read Changes using Change Data Capture (CDC)** | Reads MySQL's binary log | Yes | Binary logging in row format, and a user with replication privileges |

The form opens on the cursor option. A table left on a full refresh mode is read whole on every run and replaced, so it does reflect deletes at the source, at the cost of reading everything each time. See [sync modes](https://docs.sanda-os.com.au/connections/sync-modes).

## Tips

- **Read the big tables incrementally.** The first run reads every table you choose in full. For a large table, switch to an incremental mode and choose a cursor column that only moves forward, such as `updated_at`. See [choose what to sync](https://docs.sanda-os.com.au/connections/choose-streams).
- **Watch TINYINT(1) columns.** They arrive as booleans. If you use them for small counts, turn on **Treat TINYINT(1) Columns as Integers** under **Advanced settings**.
- **Prefer a read replica.** If you have one, point sanda at it so syncs never compete with your application. A replica that lags hands sanda older data.
- **Reach a private database through a tunnel.** **SSH Tunnel Method** sits under **Advanced settings**, with **SSH Key Authentication** and **Password Authentication** options. The jump server has to be reachable from the internet, and it needs sanda's address allowed, exactly as a database would. See [Allowlist sanda's address](https://docs.sanda-os.com.au/connections/allowlist).

### Set up change data capture

Only do this if you need deletes captured and can change server settings. The server must write its binary log in row format, with full row images (`binlog_format = ROW` and `binlog_row_image = FULL`). The user needs `reload`, `show databases` and the replication privileges on top of `select`. These are global privileges, so they are granted on `*.*`:

```sql title="Privileges for the sanda user"
grant reload, show databases, replication slave, replication client on *.* to 'becca_reader'@'%';
```

Choose **Read Changes using Change Data Capture (CDC)** under **Update Method**. Its own options sit under it:

- **Configured server timezone for the MySQL source (Advanced).** Set this only if the server's timezone is not a standard one.
- **Invalid CDC Position Behavior (Advanced).** MySQL removes old binary logs on its own schedule. If sanda's saved position has been removed by the time it next runs, **Fail sync** stops the sync until the connection is reset, which sanda support does for you, and **Re-sync data** starts a fresh full load automatically, which costs more and can lose data. It opens on **Fail sync**.

Keep binary log retention longer than the longest gap between syncs, so sanda's position is still there when the next run starts.

## Configuration fields

What the connection form asks for. Required fields are marked; the rest are optional.

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Host** | string | Yes | Hostname of the database. |
| **Port** | integer | Yes | Port of the database. Default `3306`. |
| **User** | string | Yes | The username which is used to access the database. |
| **Password** | string, secret | No | The password associated with the username. |
| **Database** | string | Yes | The database name. |
| **Table Filters** | array | No | Optional filters to include only specific tables from the specified database. |
| **JDBC URL Params** | string | No | Additional properties to pass to the JDBC URL string when connecting to the database formatted as 'key=value' pairs separated by the symbol '&'. (example: key1=value1&key2=value2&key3=value3). |
| **Encryption** | one of 4 options | No | The encryption method which is used when communicating with the database. |
| **SSH Tunnel Method** | one of 3 options | No | Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use. |
| **Update Method** | one of 2 options | Yes | Configures how data is extracted from the database. |
| **Checkpoint Target Time Interval** | integer | No | How often (in seconds) a stream should checkpoint, when possible. Default `300`. |
| **Check Table and Column Access Privileges** | boolean | No | When this feature is enabled, during schema discovery the connector will query each table or view individually to check access privileges and inaccessible tables, views, or columns therein will be removed. In large schemas, this might cause schema discovery to take too long, in which case it might be advisable to disable this feature. Default `true`. |
| **Max Concurrent Queries to Database** | integer | No | Maximum number of concurrent queries to the database. Leave empty to let becca optimize performance. |
| **Treat TINYINT(1) Columns as Integers** | boolean | No | When enabled, TINYINT(1) columns are emitted as integers instead of booleans. Default `false`. |

### Encryption

The encryption method which is used when communicating with the database.

Choose one of the following. Each asks for its own fields.

**preferred**

No further fields.

**required**

No further fields.

**verify_ca**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **CA certificate** | string, secret | Yes |  |
| **Client certificate File** | string, secret | No | Client certificate (this is not a required field, but if you want to use it, you will need to add the Client key as well). |
| **Client Key** | string, secret | No | Client key (this is not a required field, but if you want to use it, you will need to add the Client certificate as well). |
| **Client key password** | string, secret | No | Password for keystorage. This field is optional. If you do not add it, the password will be generated automatically. |

**verify_identity**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **CA certificate** | string, secret | Yes |  |
| **Client certificate File** | string, secret | No | Client certificate (this is not a required field, but if you want to use it, you will need to add the Client key as well). |
| **Client Key** | string, secret | No | Client key (this is not a required field, but if you want to use it, you will need to add the Client certificate as well). |
| **Client key password** | string, secret | No | Password for keystorage. This field is optional. If you do not add it, the password will be generated automatically. |


### SSH Tunnel Method

Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use.

Choose one of the following. Each asks for its own fields.

**No Tunnel**

No further fields.

**SSH Key Authentication**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **SSH Tunnel Jump Server Host** | string | Yes | Hostname of the jump server host that allows inbound ssh tunnel. |
| **SSH Connection Port** | integer | Yes | Port on the proxy/jump server that accepts inbound ssh connections. Default `22`. |
| **SSH Login Username** | string | Yes | OS-level username for logging into the jump server host. |
| **SSH Private Key** | string, secret | Yes | OS-level user account ssh key credentials in RSA PEM format ( created with ssh-keygen -t rsa -m PEM -f myuser_rsa ). |

**Password Authentication**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **SSH Tunnel Jump Server Host** | string | Yes | Hostname of the jump server host that allows inbound ssh tunnel. |
| **SSH Connection Port** | integer | Yes | Port on the proxy/jump server that accepts inbound ssh connections. Default `22`. |
| **SSH Login Username** | string | Yes | OS-level username for logging into the jump server host. |
| **Password** | string, secret | Yes | OS-level password for logging into the jump server host. |


### Update Method

Configures how data is extracted from the database.

Choose one of the following. Each asks for its own fields.

**Scan Changes with User Defined Cursor**

No further fields.

**Read Changes using Change Data Capture (CDC)**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Configured server timezone for the MySQL source (Advanced)** | string | No | Enter the configured MySQL server timezone. This should only be done if the configured timezone in your MySQL instance does not conform to IANNA standard. |
| **Invalid CDC Position Behavior (Advanced)** | string | No | Determines whether becca should fail or re-sync data in case of an stale/invalid cursor value in the mined logs. If 'Fail sync' is chosen, syncs fail until becca support resets the connection. If 'Re-sync data' is chosen, becca will automatically trigger a refresh but could lead to higher costs and data loss. One of `Fail sync`, `Re-sync data`. Default `Fail sync`. |
| **Initial Load Timeout in Hours (Advanced)** | integer | No | The amount of time an initial load is allowed to continue for before catching up on CDC logs. Default `8`. |


## Related

:::links
- [Sync modes](https://docs.sanda-os.com.au/connections/sync-modes): How sanda reads each stream on every run.
- [Sync schedules](https://docs.sanda-os.com.au/connections/schedules): How often a connection runs.
- [Allowlist sanda’s address](https://docs.sanda-os.com.au/connections/allowlist): For a system behind a firewall.
- [Troubleshoot connections](https://docs.sanda-os.com.au/connections/troubleshooting): What an error means and how to fix it.
:::