> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/mysql.md).

# MySQL

MySQL batch source. Reads tables or custom SQL with optional incremental ingestion; the connector uses the pymysql driver and rewrites the URI internally.

[MySQL](https://dev.mysql.com/doc/) is the most widely deployed open-source relational database. Nilus reads from MySQL as a **batch source** through the standard MySQL SQL path, with optional incremental ingestion driven by a monotonic key column. Internally Nilus rewrites `mysql://` to `mysql+pymysql://` before connecting.

For row-level change capture from MySQL, use the [MySQL (CDC)](https://v2.dataos.info/references/resources/nilus/cdc/cdc-sources/mysql.md) instead.

## Requirements

Connectivity and credentials must both be in place before the pipeline can run.

### Connectivity

* The Nilus runtime must reach the MySQL host on the configured port (default `3306`).
* The configured user must be able to connect from the runtime's network and have `SELECT` on the target tables.
* For managed MySQL (RDS, Cloud SQL, Aurora, Cloud SQL for MySQL), allowlist the runtime egress IP on the database side.

### Required parameters

| Parameter  | Required | Default | Description                                                                                                                                                               |
| ---------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `host`     | Yes      | -       | MySQL server hostname or IP address.                                                                                                                                      |
| `port`     | No       | `3306`  | MySQL port.                                                                                                                                                               |
| `database` | Yes      | -       | Source database name (the URI path segment). For Depot-backed configs, prefer leaving the default empty and using fully qualified `<database>.<table>` in `source_table`. |
| `username` | Yes      | -       | MySQL user with `SELECT` access on the target tables.                                                                                                                     |
| `password` | Yes      | -       | Password for the MySQL user.                                                                                                                                              |

### Database-side permissions

```sql
GRANT SELECT ON `<database_name>`.* TO '<username>'@'<host>';
FLUSH PRIVILEGES;
```

For very fine-grained access, scope the grant to specific tables:

```sql
GRANT SELECT ON `<database_name>`.`<table_name>` TO '<username>'@'<host>';
```

### URI format

```
mysql://<username>:<password>@<host>:<port>/<database>
```

Depot-backed equivalent (recommended for production):

```
dataos://<mysql-depot>?purpose=ro
```

## Source options

| Option                 | Required | Description                                                                                                                                                                    |
| ---------------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `source_table`         | Yes      | Fully qualified table name in `<database>.<table>` form (e.g. `sales.orders`).                                                                                                 |
| `incremental_key`      | No       | Timestamp or numeric column used to identify newly visible rows for each run. Use a monotonic column (`updated_at`, sequence-backed `id`).                                     |
| `interval_start`       | No       | Optional ISO-8601 lower bound for the extraction window.                                                                                                                       |
| `interval_end`         | No       | Optional ISO-8601 upper bound for the extraction window.                                                                                                                       |
| `page_size`            | No       | Rows per extraction batch (default `50000`). Lower it for wide tables that strain runtime memory.                                                                              |
| `sql_reflection_level` | No       | `full` (default) or reduced, controls how thoroughly Nilus reflects the source schema before extraction.                                                                       |
| `sql_limit`            | No       | Caps total rows extracted per run. Use for sampling and validation.                                                                                                            |
| `sql_exclude_columns`  | No       | Comma-separated column names to skip during extraction.                                                                                                                        |
| `type_hints`           | No       | Object map of `column_name: <type>` to override inferred types. Supported types: `text`, `bigint`, `bool`, `timestamp`, `date`, `decimal`, `double`, `binary`, `json`, `time`. |
| `max_table_nesting`    | No       | String. `"0"` for fully flattened analytics-friendly output (default for SQL sources).                                                                                         |

## Sample Nilus configs

Each example below is self-contained and uses the current Nilus pipeline shape.

### Batch, incremental ingestion to Lakehouse

```yaml
name: nilus-mysql-batch
version: v1alpha
type: nilus
description: MySQL → DataOS Lakehouse incremental snapshot
spec:
  type: batch
  compute: runnable-default
  source:
    address: dataos://mysqldepot?purpose=ro
    options:
      source_table: sales.orders
      incremental_key: updated_at
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: sales.orders_raw
      incremental_strategy: merge
      aws_region: us-west-2
```

### Batch, direct URI with bounded backfill

```yaml
spec:
  type: batch
  source:
    address: mysql://{MYSQL_USERNAME}:{MYSQL_PASSWORD}@mysql.example.com:3306/sales
    options:
      source_table: sales.orders
      incremental_key: updated_at
      interval_start: "2024-01-01T00:00:00Z"
      interval_end: "2024-04-01T00:00:00Z"
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: sales.orders_q1_2024
      incremental_strategy: replace
```

## Behavior and capabilities

* **Compute model**: Nilus connects through the `pymysql` driver and submits parameterized SQL reads.
* **Object model**: standard MySQL tables and views addressed as `<database>.<table>`.
* **Pipeline type**: `batch` only on this page; for row-level change capture see the [MySQL (CDC)](https://v2.dataos.info/references/resources/nilus/cdc/cdc-sources/mysql.md).
* **URI scheme rewrite**: `mysql://` URIs are rewritten internally to `mysql+pymysql://` to bind to the MySQL driver. Authoring `mysql+pymysql://` directly in the manifest also works.
* **Identifier case**: MySQL identifier case sensitivity depends on the server's `lower_case_table_names` setting. Match the server convention exactly in `source_table`.
* **Custom queries**: supply `source_table: "query:SELECT ... FROM ..."` to extract from a hand-authored SQL query instead of a single table.
* **Lakehouse decimals**: table reads preserve source precision and scale (`DECIMAL(20,4)` becomes Iceberg `decimal(20,4)`). `query:` extracts store decimals as Iceberg `decimal(38,9)`. Do not use both extract styles with the same `dest_table`.

## Troubleshooting

| Symptom                                                                                    | Likely cause                                                                                                                                                              | Resolution                                                                                                                                                                |
| ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `Access denied for user '<user>'@'<host>'`                                                 | Wrong credentials, or the user is provisioned for a different host pattern (e.g. `'%'` vs the runtime IP).                                                                | Re-check credentials and confirm the user is granted from `'%'` or the runtime egress range.                                                                              |
| `Can't connect to MySQL server on '<host>'`                                                | Network path blocked, or the server is bound to `127.0.0.1`.                                                                                                              | Verify the runtime egress IP is allowlisted on the database side and that the server is listening on a routable interface.                                                |
| `Table '<db>.<tbl>' doesn't exist` despite the table existing in the UI                    | Server has `lower_case_table_names=0` (case-sensitive); the manifest used a different case than the on-disk table.                                                        | Match the on-disk identifier case exactly in `source_table`.                                                                                                              |
| `Lost connection to MySQL server during query`                                             | Long-running extraction exceeded `wait_timeout`/`net_read_timeout`.                                                                                                       | Lower `page_size` so each batch is shorter, or have your DBA raise the relevant timeouts on the source.                                                                   |
| Run extracts the full table every time despite `incremental_key`                           | The column is not monotonically increasing in the source, or the pipeline state was reset between runs.                                                                   | Pick a column that is genuinely monotonic; consider `updated_at` set by an `ON UPDATE CURRENT_TIMESTAMP` clause.                                                          |
| Wide tables OOMKill the runtime                                                            | `page_size` (default `50000`) holds too much data in memory for very wide rows.                                                                                           | Lower `page_size` to `25000` or `10000`. See [Optimize Sink Datasets](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets.md). |
| `ValueError: Cannot serialize value, scale of value does not match type decimal(38, 9): 4` | The Lakehouse table was created by a `query:` extract (`decimal(38,9)`), then a table read tried to write source-scale decimals such as `DECIMAL(20,4)` — or the reverse. | Keep one extract style per table. Point `dest_table` at a new table, or drop and reload.                                                                                  |

## Related docs

* [Batch data movement](https://v2.dataos.info/references/resources/nilus/batch.md)
* [Batch pipeline config](https://v2.dataos.info/references/resources/nilus/batch/pipeline-config.md)
* [Batch sample configs](https://v2.dataos.info/references/resources/nilus/batch/sample-configs.md)
* [MySQL](https://v2.dataos.info/references/resources/nilus/cdc/cdc-sources/mysql.md): companion CDC connector for row-level change capture.
* [Optimize sink datasets](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets.md): guidance on `incremental_strategy`, `partition_by`, and other shape settings.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/resources/nilus/batch/batch-sources/mysql.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
