> ## Documentation Index
> Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL destination for ELT & CDC

> How to set up and configure MySQL as a destination in Integrate.io ELT & CDC, including required privileges and enabling local_infile.

|                           |                                            |
| :------------------------ | :----------------------------------------- |
| **Description**           | Load replicated data into MySQL or MariaDB |
| **Type**                  | RDBMS Destination                          |
| **Supported Replication** | Initial Sync, Continuous Sync              |
| **Authentication Type**   | Password Authentication                    |

## Overview

Integrate.io ELT & CDC syncs data to MySQL using a two-stage bulk-load pattern. Batches are streamed into a temporary staging table with `LOAD DATA LOCAL INFILE`, then merged into the destination tables using inserts, updates, and deletes. No files are written on either side: the data streams directly over the connection.

Because staging uses session `TEMPORARY` tables, there are no orphan staging tables to clean up. The destination database doubles as the schema, so there is no separate schema field to configure.

## Requirements

* MySQL **5.5** or later, or MariaDB **10.x**.
* `local_infile` enabled on the server (covered in the steps below).
* An admin user for the one-time setup (your RDS master user works).
* Network access from Integrate.io's egress IPs, or an [SSH tunnel](/docs/cdc/ssh-tunnel).

## Create sync user

Create the sync user. Run the following as an admin user (your RDS master user works):

```sql theme={null}
CREATE USER 'integrateio'@'%' IDENTIFIED BY '<your_password>';
```

## Grant database privileges

Grant the sync user privileges on the destination database. The sink creates and alters destination tables, stages batches in `TEMPORARY` tables, and merges with inserts, updates, and deletes. Specify the database you want the data synced to and run:

```sql theme={null}
CREATE DATABASE IF NOT EXISTS `<database_name>`;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,
  CREATE TEMPORARY TABLES ON `<database_name>`.* TO 'integrateio'@'%';
```

### Required privileges

| Privilege                              | Why it's needed                                                    |
| :------------------------------------- | :----------------------------------------------------------------- |
| `SELECT`, `INSERT`, `UPDATE`, `DELETE` | Read and merge replicated rows into destination tables.            |
| `CREATE`, `DROP`, `ALTER`              | Create and evolve destination tables as the source schema changes. |
| `CREATE TEMPORARY TABLES`              | Stage each batch in a session temporary table before merging.      |

## Enable local\_infile

The sink loads data with `LOAD DATA LOCAL INFILE`, which requires `local_infile` to be enabled on the server.

* **Self-managed MySQL:** run the statement below.

  ```sql theme={null}
  SET GLOBAL local_infile = 1;
  ```

* **Managed MySQL (Amazon RDS, Aurora, Google Cloud SQL):** set `local_infile = 1` in the server's parameter group instead.

<Note>
  If `local_infile` is disabled, the connection test fails with a remediation message. Enable it before saving the connection.
</Note>

## Features

| Feature                | Supported | Notes                                  |
| :--------------------- | :-------- | :------------------------------------- |
| Full (Historical) sync | Yes       |                                        |
| Incremental sync       | Yes       |                                        |
| UPSERT                 | Yes       | Last write wins, keyed by primary key. |
| Replicate DELETE       | Yes       |                                        |
| Append only mode       | Yes       | Same-batch replays are de-duplicated.  |
| SSH tunnel             | Yes       | [SSH Tunnel Guide](/docs/cdc/ssh-tunnel/)   |

## Limitations

* String primary keys are stored as `VARCHAR(191)` to stay within the InnoDB 767-byte key limit on the MySQL 5.5 and 5.6 floor. Primary key values longer than 191 characters are truncated at load, and prefix-colliding keys may collapse into a single row.
* `VARCHAR` primary keys longer than 191 characters require a MySQL 5.7 or later destination.

## Frequently Asked Questions (FAQs)

<AccordionGroup>
  <Accordion title="Why does the connection test fail with a local_infile error?">
    The sink loads data with `LOAD DATA LOCAL INFILE`, which requires `local_infile` to be enabled on the server. Run `SET GLOBAL local_infile = 1;` on self-managed MySQL, or set `local_infile = 1` in the parameter group on managed MySQL (RDS, Aurora, Cloud SQL), then test again.
  </Accordion>

  <Accordion title="Do I need to create the destination database first?">
    No. If you grant the sync user `CREATE` privileges, the `CREATE DATABASE IF NOT EXISTS` step creates the database for you. The destination tables are created and altered automatically as data syncs.
  </Accordion>

  <Accordion title="Is there a separate schema to configure?">
    No. In MySQL the schema is the database, so there is no separate schema field. Data syncs into the database you specify in the grant step.
  </Accordion>

  <Accordion title="Does the sink leave temporary staging tables behind?">
    No. Staging uses session `TEMPORARY` tables on a pinned connection per batch, so there are no orphan tables to clean up.
  </Accordion>

  <Accordion title="What MySQL versions are supported?">
    MySQL 5.5 or later, and MariaDB 10.x. Some features, such as `VARCHAR` primary keys longer than 191 characters, require MySQL 5.7 or later.
  </Accordion>
</AccordionGroup>

## Related

<CardGroup cols={2}>
  <Card title="MySQL Source" icon="arrow-right" href="/docs/cdc/mysql" horizontal />

  <Card title="SSH Tunnel" icon="arrow-right" href="/docs/cdc/ssh-tunnel" horizontal />

  <Card title="IP Allowlist" icon="arrow-right" href="/docs/cdc/ip-list" horizontal />

  <Card title="Initial Sync Process" icon="arrow-right" href="/docs/cdc/initial-sync-process" horizontal />
</CardGroup>