> ## 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.

# MariaDB (Generic)

> MariaDB Change Data Capture Setup with Streamkap

# Prerequisites

* MariaDB version ≥ 11.4.3
* MariaDB [binlog](https://mariadb.com/kb/en/activating-the-binary-log/) enabled on the primary server
* Connection details
* Streamkap user and role

<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 disabled):**

  | 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>
    MariaDB has GTID enabled by default. If you are using the default GTID configuration with a read-only connection, the signal table permissions are not needed. The heartbeat table still requires permissions for monitoring and diagnostics.
  </Info>

  **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 disabled)
  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>

# Granting Privileges

It's recommended to create a separate user and role for Streamkap to access your MariaDB 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 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>

## Enable Snapshots

You can perform ad-hoc snapshots of all or some of your tables in the Streamkap app. See [Snapshots & Backfilling](/snapshots) for more information.

To enable this feature, there are 2 methods available for MariaDB databases.

### Method 1: Enable GTID (default)

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.

For MariaDB, this is **enabled by default**, no additional setup is necessary.

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

If for some reason you have disabled GTIDs and cannot enable them, you will need to create the table and give permissions to the `streamkap_user`. Streamkap 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.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>

## 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 ON UPDATE 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 MariaDB 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="MariaDB Documentation" icon="database" href="https://mariadb.com/kb/en/event-scheduler/">
            Event Scheduler documentation
          </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 MariaDB, add `event_scheduler = ON` to your MariaDB configuration file (usually `my.cnf` or `my.ini`) in the `[mysqld]` section.

          For managed databases (RDS), 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;

        -- Switch to the streamkap schema
        USE 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 MariaDB 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>

# Update Server Configuration File

Open a connection to your MariaDB database's server. Access your MariaDB server configuration file (usually `/etc/my.cnf`). These lines enable `ROW` format binary log replication which Streamkap needs to perform incremental updates.

* Enable binary logging
* Set a unique `server-id` number if not set already. If your configuration already has a `server-id` entry, you don't need to change it. Otherwise, choose a number between 1 and 4294967295 as the `server-id`.
* Set a minimum 3 days for log expiry (default is 30 days)

```bash theme={null}
binlog_format=ROW
binlog_row_image=FULL
log_bin=mariadb-binlog  
server-id=123456789
binlog_expire_logs_seconds=259200
```

* Restart your MariaDB server for these changes to take effect

## Validate binlog row value options

To enable the connector to consume `UPDATE` events, this variable must be set to a value other than `PARTIAL_JSON`.

* Check current variable value: `show global variables where variable_name = 'binlog_row_value_options';`
* If the value of the variable is set to `PARTIAL_JSON`, run the following command to unset it: `set @@global.binlog_row_value_options="";`

## Verify binary logs are enabled

You can either:

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

# Consider Access Restrictions

* Visit [Connection Options](/connection-options) to ensure Streamkap can reach your database

# Setup MariaDB Connector in Streamkap

* Go to Sources and click Create New

* Input

  * Name for your Connector

  * Hostname

  * Port (Default `3306`)

  * Username (Username you chose earlier, our scripts use `streamkap_user`)

  * Password

  * Read only

    * Whether or not to use a read-only connection. MariaDB has GTID enabled by default, so no additional configuration is necessary. See [Enable GTID](/mariadb-generic#method-1-enable-gtid) for more information.
    * If you have disabled GTID mode and cannot enable it, set **Read only** to **No** and create the signal table as described [here](/mariadb-generic#method-2-create-a-table-in-the-source-database).
      * 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](/mariadb-generic#enable-snapshots) for setup instructions.

  * Heartbeats: Enabled by default.

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

    * For **read-only** connections, configure a scheduled heartbeat event on the primary database using the MariaDB Event Scheduler, and include the heartbeat table in [Add Schemas/Tables](#setup-mariadb-connector-in-streamkap). See [Heartbeats](#heartbeats) for setup instructions.

  * Connection Timezone - The timezone of your database

  * <Info>
      **Timezone conversion**

      MariaDB 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 MariaDB 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>

* Connect via SSH Tunnel. See [SSH Tunnel](/ssh-tunnel)

* Advanced Parameters
  * Represent Binary Data As (Default `bytes`)
  * Capture Only Captured Databases DDL (Default `false`) - Used to control whether the connector records schema structures from all databases defined in the server (the default) or only those databases for which you've explicitly configured the connector. Specify `true` to capture schema history only for the specific databases you've configured. This is particularly valuable when databases are large, to reduce the volume of DDL stored in the schema history topic. It also improves startup times when the connector restarts or recovers from failures. See [Schema History Optimization](/schema-history-optimization) for details.
  * Capture Only Captured Tables DDL (Default `false`) - Used to control whether the connector records the schema structure for all tables in the configured databases (the default) or only the tables whose changes the connector captures. Specify `true` to capture schema history only for the specific tables you've configured. This is particularly valuable when tables are large, to reduce the volume of DDL statements stored in the schema history topic. It also improves startup times when the connector restarts or recovers from failures. See [Schema History Optimization](/schema-history-optimization) for details.

* Add Schemas/Tables. Can also bulk upload here. The format is a simple list of each schema or table per row saved in csv format without a header.

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

  Change Data Capture reads the MariaDB 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 The connector will take approximately 1 minute to start processing data.

<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>