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

# MySQL (Generic)

> MySQL Change Data Capture Setup with Streamkap

## Prerequisites

* MySQL version ≥ 5.7
* MySQL [binlog](https://dev.mysql.com/doc/refman/8.0/en/binary-log.html) enabled on the primary server
* Connection details including Server ID
* A database user with sufficient privileges to configure the database, including enabling binary logging and creating users

<Accordion title="Quick Reference: Minimum Required Permissions">
  The following table summarizes all permissions the Streamkap user needs. The setup steps on each source connector page walk through granting each one.

  **Core permissions (always required):**

  | Permission           | Scope        | Purpose                                                  |
  | -------------------- | ------------ | -------------------------------------------------------- |
  | `REPLICATION CLIENT` | `*.*`        | Read binlog metadata and positions                       |
  | `REPLICATION SLAVE`  | `*.*`        | Read binlog events for CDC streaming                     |
  | `RELOAD`             | `*.*`        | Flush operations required for consistent snapshots       |
  | `SHOW DATABASES`     | `*.*`        | Discover available databases and schemas                 |
  | `SELECT`             | `{schema}.*` | Read table data during initial and incremental snapshots |

  **Snapshot signal table (required if GTID is not enabled):**

  | Permission | Scope                        | Purpose                    |
  | ---------- | ---------------------------- | -------------------------- |
  | `SELECT`   | `streamkap.streamkap_signal` | Read signal table state    |
  | `INSERT`   | `streamkap.streamkap_signal` | Trigger snapshot signals   |
  | `UPDATE`   | `streamkap.streamkap_signal` | Update signal table state  |
  | `DELETE`   | `streamkap.streamkap_signal` | Clean up processed signals |

  **Heartbeat table (required if heartbeats are enabled):**

  | Permission | Scope                           | Purpose                        |
  | ---------- | ------------------------------- | ------------------------------ |
  | `SELECT`   | `streamkap.streamkap_heartbeat` | Read heartbeat state           |
  | `INSERT`   | `streamkap.streamkap_heartbeat` | Write heartbeat records        |
  | `UPDATE`   | `streamkap.streamkap_heartbeat` | Update heartbeat timestamps    |
  | `DELETE`   | `streamkap.streamkap_heartbeat` | Clean up old heartbeat records |

  <Info>
    If GTID is enabled (default on Google Cloud SQL), the signal table is not required.
  </Info>

  <Note>
    If using **read-only** mode with GTID enabled, the signal table permissions are not needed. The heartbeat table still requires permissions for monitoring and diagnostics.
  </Note>

  **Combined GRANT statements:**

  ```sql SQL theme={null}
  -- Core permissions (always required)
  GRANT REPLICATION CLIENT, RELOAD, SHOW DATABASES, REPLICATION SLAVE ON *.* TO 'streamkap_user'@'%';
  GRANT SELECT ON {schema}.* TO 'streamkap_user'@'%';

  -- Signal table (if GTID is not enabled)
  GRANT SELECT, UPDATE, INSERT, DELETE ON streamkap.streamkap_signal TO 'streamkap_user'@'%';

  -- Heartbeat table (if heartbeats are enabled)
  GRANT SELECT, UPDATE, INSERT, DELETE ON streamkap.streamkap_heartbeat TO 'streamkap_user'@'%';
  ```
</Accordion>

## MySQL Setup

### 1. Grant Database Access

* Configure one of the [Connection Options](/connection-options) to ensure Streamkap can reach your database.

### 2. Configure Binary Logging

Binary logging records all changes to your database tables. The Connector relies on MySQL's implementation of this.

Open a connection to your MySQL database's server. Access your MySQL server configuration file (usually `/etc/my.cnf`). Ensure that this file has the following lines in the `[mysqld]` section. These lines enable ROW format binary log replication, which the Connector needs to perform incremental updates.

* Enable binlog
* Set Server-ID if not set already
* Set minimum 3 days for log expiry

<CodeGroup>
  ```ini MySQL Configuration theme={null}
  [mysqld]
  binlog-format=ROW
  log-bin=mysql-binlog
  server-id=123456789
  expire-logs-days=3
  log-slave-updates=1
  ```
</CodeGroup>

If your configuration already has a `server-id` entry, you don't need to change it. Otherwise, choose any number between 1 and 4294967295 as the server-id.

* Restart your MySQL server to effect these changes.

### 3. Verify Binary Logs Are Enabled

You can verify using any of these methods:

* Run the following SQL query on the DB instance `SHOW VARIABLES LIKE '%log_bin%';`. Result should be `ON`
* Run `SHOW BINARY LOGS`

<Warning>
  If you upgrade your database version to 8.0.23 or above, please set the parameter `binlog_row_metadata` value to `MINIMAL` before upgrading. Failing to do this may result in your MySQL connectors failing.
</Warning>

### 4. Create Database User

It's recommended to create a separate user and role for the Connector to access your MySQL database. Below is an example script that does that.

<CodeGroup>
  ```SQL SQL theme={null}
  -- Replace { ... } placeholders as required

  -- Create user
  CREATE USER 'streamkap_user'@'%' IDENTIFIED WITH mysql_native_password BY '{password}';

  -- Grant Permissions
  GRANT REPLICATION CLIENT, RELOAD, SHOW DATABASES, REPLICATION SLAVE ON *.* TO 'streamkap_user'@'%';

  -- Grant Select on all schemas needed
  GRANT SELECT ON {schema}.* TO 'streamkap_user'@'%';
  ```
</CodeGroup>

### 5. Enable Snapshots

To backfill your data, the Connector needs to be able to perform snapshots. See [Snapshots & Backfilling](/snapshots) for more information.

To enable this feature, there are 2 methods available:

#### Method 1: Enable GTID (Recommended)

<Check>
  This method is recommended if you cannot create and grant Streamkap read/write privileges on a 'signal' table (method 2) for any reason. It's the equivalent of a 'read only' connection.
</Check>

<Warning>
  GTIDs only available in MySQL version ≥ 5.6.5
</Warning>

Global transaction identifiers (GTIDs) uniquely identify transactions that occur on a server within a cluster. Though not required, using GTIDs simplifies replication and enables you to more easily confirm if primary and replica servers are consistent as well as carry out incremental snapshots.

Set up following these instructions. Ensure you follow the guide for your version [Replication GTIDs - How To](https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html) and that `GTID` mode is `ON`.

#### Method 2: Create a table in the source database

If you cannot enable GTID mode, you will need to create the table and give permissions to the `streamkap_user`. The Connector will use this collection for managing snapshots.

<Info>
  The examples below use `streamkap_signal` as the signal table name, but you can choose any name. During [Streamkap Setup](#3-snapshot-settings), provide the full path to your signal table in `database.table` format (e.g., `streamkap.streamkap_signal`).
</Info>

<CodeGroup>
  ```SQL SQL theme={null}
  -- Create the schema
  CREATE SCHEMA streamkap;

  CREATE TABLE streamkap_signal (
    id VARCHAR(255) PRIMARY KEY,
    type VARCHAR(32) NOT NULL,
    data VARCHAR(2000) NULL
  );

  GRANT SELECT, UPDATE, INSERT, DELETE ON streamkap.streamkap_signal TO 'streamkap_user'@'%';
  ```
</CodeGroup>

### 6. Heartbeats

Connectors use "offsets"—like bookmarks—to track their position in the database's log or change stream. When no changes occur for long periods, these offsets may become outdated, and the Connector might lose its place or stop capturing changes.

Heartbeats ensure the Connector stays active and continues capturing changes.

There are two layers of heartbeat protection:

#### Layer 1: Connector heartbeats (enabled by default)

The Connector periodically emits heartbeat messages to an internal topic, even when no actual data changes are detected. This keeps offsets fresh and prevents staleness.

No configuration is necessary for this layer; it is automatically enabled. We recommend keeping this layer enabled for all deployments.

#### Layer 2: Source database heartbeats (recommended)

<Info>
  **Why we recommend configuring Layer 2**

  While Layer 2 is **crucial** for low-traffic or intermittent databases, we recommend configuring it for all deployments. It provides additional resilience and helps prevent issues during periods of inactivity.
</Info>

You can configure regular updates to a dedicated heartbeat table in the source database. This simulates activity, ensuring change events are generated consistently, maintaining log progress and providing additional resilience.

How this layer is configured depends on the connection type (if supported by the Source):

* **Read-write connections** (when **Read only** is **No** during Streamkap Setup): The Connector updates the heartbeat table directly.
* **Read-only connections** (when **Read only** is **Yes** during Streamkap Setup): A scheduled job on the **primary** database updates the heartbeat table, and these changes replicate to the read replica for the Connector to consume.

This layer requires you to set up a heartbeat table—and for read-only connections, a scheduled job (e.g., `pg_cron` for PostgreSQL, `event_scheduler` for MySQL)—on your source database.

<Tabs>
  <Tab title="Read-write connections">
    For read-write connections (when **Read only** is **No** during Streamkap Setup), the Connector writes to the heartbeat table directly.

    <CodeGroup>
      ```SQL SQL theme={null}
      -- Create the streamkap schema
      CREATE SCHEMA IF NOT EXISTS streamkap;

      -- Switch to the streamkap schema
      USE streamkap;

      -- Create the heartbeat table with id, text, and last_update fields
      CREATE TABLE streamkap_heartbeat (
          id INT AUTO_INCREMENT PRIMARY KEY,
          text TEXT,
          last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- Grant permission to the Streamkap user
      GRANT SELECT, UPDATE, INSERT, DELETE ON streamkap.streamkap_heartbeat TO 'streamkap_user'@'%';

      -- Insert the first row into the heartbeat table
      INSERT INTO streamkap_heartbeat (text) VALUES ('test_heartbeat');
      ```
    </CodeGroup>
  </Tab>

  <Tab title="Read-only connections">
    For read-only connections (when **Read only** is **Yes** during Streamkap Setup), the Connector cannot write to the heartbeat table directly. Instead, you must configure a scheduled job on the **primary** database to generate artificial traffic. These changes will replicate to the read replica, which the Connector then consumes.

    <Warning>
      **Run these commands on the primary database, not the read replica.**

      The heartbeat table and scheduled event must be created on the primary database. The changes will automatically replicate to the read replica.
    </Warning>

    <Steps>
      <Step title="Enable the event scheduler">
        The MySQL Event Scheduler flag must be enabled on your database (`event_scheduler=ON`). See your provider's documentation:

        <CardGroup cols={2}>
          <Card title="Amazon RDS" icon="aws" href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Modifying.html">
            Modifying parameters in a DB parameter group in Amazon RDS
          </Card>

          <Card title="Amazon Aurora" icon="aws" href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html">
            Aurora MySQL configuration parameters reference
          </Card>

          <Card title="Google Cloud SQL" icon="google" href="https://cloud.google.com/sql/docs/mysql/flags#tips-event_scheduler">
            Configuring database flags
          </Card>

          <Card title="Azure Database" icon="microsoft" href="https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-server-parameters">
            Server parameters
          </Card>
        </CardGroup>

        Check if the event scheduler is enabled:

        ```sql SQL theme={null}
        SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler';
        ```

        <Info>
          **Making this setting persistent**

          For self-hosted MySQL, add `event_scheduler = ON` to your MySQL configuration file (usually `my.cnf` or `my.ini`) in the `[mysqld]` section.

          For managed databases (RDS, Cloud SQL, Azure), configure the `event_scheduler` parameter through your provider's console or CLI.
        </Info>
      </Step>

      <Step title="Create the heartbeat table">
        ```sql SQL theme={null}
        -- Create the streamkap schema
        CREATE SCHEMA IF NOT EXISTS streamkap;

        -- Create the heartbeat table
        CREATE TABLE streamkap.streamkap_heartbeat (
            id INT AUTO_INCREMENT PRIMARY KEY,
            text TEXT,
            last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        -- Insert the initial row
        INSERT INTO streamkap.streamkap_heartbeat (text) VALUES ('test_heartbeat');
        ```
      </Step>

      <Step title="Create the scheduled event">
        ```sql SQL theme={null}
        CREATE EVENT streamkap.streamkap_heartbeat_event
        ON SCHEDULE EVERY 1 MINUTE
        DO
          UPDATE streamkap.streamkap_heartbeat
          SET text = 'updated_heartbeat',
              last_update = CURRENT_TIMESTAMP
          WHERE id = 1;
        ```
      </Step>

      <Step title="Grant permissions">
        Whichever database user is used to create and run the event scheduler (often the MySQL root user or a dedicated event scheduler user) needs appropriate permissions on the heartbeat table. Additionally, the Streamkap user also needs permissions to monitor the heartbeat table.

        ```sql SQL theme={null}
        GRANT EVENT ON streamkap.* TO {event scheduler user};
        GRANT SELECT, UPDATE, INSERT, DELETE ON streamkap.streamkap_heartbeat TO {event scheduler user};

        -- Grant permissions to the Streamkap user for monitoring and diagnostics
        GRANT SELECT, UPDATE, INSERT, DELETE ON streamkap.streamkap_heartbeat TO 'streamkap_user'@'%';
        ```
      </Step>
    </Steps>

    <Accordion title="Useful event scheduler commands">
      ```sql SQL theme={null}
      -- View all scheduled events in the streamkap schema
      SHOW EVENTS IN streamkap;

      -- View event details
      SELECT * FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'streamkap';

      -- Disable an event temporarily
      ALTER EVENT streamkap.streamkap_heartbeat_event DISABLE;

      -- Enable an event
      ALTER EVENT streamkap.streamkap_heartbeat_event ENABLE;

      -- Drop an event
      DROP EVENT IF EXISTS streamkap.streamkap_heartbeat_event;
      ```
    </Accordion>
  </Tab>
</Tabs>

***

## Streamkap Setup

Follow these steps to configure your new connector:

### 1. Create the Source

* Navigate to [Add Connectors](https://app.streamkap.com/connectors/add?tab=Sources).
* Choose **MySQL**.

### 2. Connection Settings

* **Name**: Enter a name for your connector.

* **Hostname**: Specify the hostname.

* **Port**: Default is `3306`.

* **Connect via SSH Tunnel**: The Connector will connect to an SSH server in your network which has access to your database. This is necessary if the Connector cannot connect directly to your database.

  * See [SSH Tunnel](/ssh-tunnel) for setup instructions.

* **Username**: Username to access the database. By default, Streamkap scripts use `streamkap_user`.

* **Password**: Password to access the database.

* **Read only**: Whether or not to use a read-only connection. Requires GTID to be enabled on the source database. See [Enable GTID](#method-1%3A-enable-gtid-recommended) for more information.

* **Heartbeats**: Enabled by default.

  * For **read-write** connections, configure a heartbeat table in the source database and set **Heartbeat Table Database**. See [Heartbeats](#6-heartbeats) for setup instructions.

  * For **read-only** connections, configure a scheduled heartbeat event on the primary database using the MySQL Event Scheduler, and include the heartbeat table in [Schema and Table Capture](#5-schema-and-table-capture). See [Heartbeats](#6-heartbeats) for setup instructions.

* **Connection Timezone**: The timezone of your database.

<Info>
  **Timezone conversion**

  MySQL converts `TIMESTAMP` values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. By default, the current time zone for each connection is the database server's time zone but this option allows you to override that.

  As long as the time zones remain the same, you get back the same value you store.

  We recommend using the default `SERVER` option which attempts to detect the session time zone from the values configured on the MySQL server session variables 'time\_zone' or 'system\_time\_zone'. It also reduces the chance of problems with daylight savings adjustment 'fall back' and 'spring forward'.

  If either time zones change, an [ad-hoc snapshot](/snapshots#triggering-a-snapshot) is recommended so your source and destination timestamps are consistent.
</Info>

### 3. Snapshot Settings

<Info>
  If you set **Read only** to **No**, you will need to create a snapshot signal table and give permissions to the `streamkap_user`. See [Enable Snapshots](#5-enable-snapshots) for setup instructions.
</Info>

* **Signal Table**: Full path to the signal table including database and table name (e.g., `streamkap.streamkap_signal`). This table is used for incremental snapshotting. See [Enable Snapshots](#5-enable-snapshots) for setup instructions.

### 4. Advanced Parameters

* **Represent binary data as**: Specifies how the data for binary columns e.g. `blob`, `binary`, `varbinary` should be interpreted. Your destination for this data can impact which option you choose. Default is `bytes`.
* **Capture Only Captured Databases DDL**: Specifies whether the connector records schema structures from all logical databases in the database instance or only captured databases. Enabling this when you have many databases in your instance can improve performance and avoid timeouts. Default is `false`. See [Schema History Optimization](/schema-history-optimization) for details.
* **Capture Only Captured Tables DDL**: Specifies whether the connector records schema structures from all logical tables in the captured schemas or databases, or only captured tables. Enabling this when you have many tables can improve performance and avoid timeouts. Default is `false`. See [Schema History Optimization](/schema-history-optimization) for details.

Click **Next**.

### 5. Schema and Table Capture

* **Add Schemas/Tables**: Specify the schema(s) and table(s) for capture.
  * You can bulk upload here. The format is a simple list of schemas and tables, with each entry on a new row. Save as a `.csv` file without a header.

<Tip>
  **Adding tables programmatically** -- You can add tables to an existing source via the API or Terraform using the `table.include.list.user.defined` parameter (Terraform: `table_include_list_user_defined`). See the [REST API](/api) for getting started with the API, or [Terraform Resources](/terraform-resources#sources) for Terraform source configuration.
</Tip>

<Warning>
  **CDC only captures base tables, not Views**

  Change Data Capture reads the MySQL binary log (binlog), which only records changes to physical tables. Database Views are query-time computations with no physical storage—they don't generate binlog entries.

  **What you cannot capture:** Views, temporary tables, MEMORY tables, BLACKHOLE tables, or system tables (information\_schema, performance\_schema).

  **Solution:** Specify only the underlying base tables that feed your views. You can recreate the view logic in your destination or transformation layer.
</Warning>

Click **Save**.

<Info>
  **Have questions?** See the [MySQL Source FAQ](/mysql-source-faq) for answers to common questions about MySQL sources, troubleshooting, and best practices.
</Info>