MySQL (Generic)

​

Prerequisites

• MySQL version ≥ 5.7
• MySQL binlog 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

​

MySQL Setup

​

1. Grant Database Access

• Configure one of the Connection Options to ensure Streamkap can reach your database.

​

2. Configure Binary Logging

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

[mysqld]
binlog-format=ROW
log-bin=mysql-binlog
server-id=123456789
expire-logs-days=3
log-slave-updates=1

• Restart your MySQL server to effect these changes.

​

3. Verify Binary Logs Are Enabled

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

​

4. Create Database User

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

​

5. Enable Snapshots

​

Method 1: Enable GTID (Recommended)

​

Method 2: Create a table in the source database

-- 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 ON streamkap.streamkap_signal TO 'streamkap_user';

​

6. Heartbeats

​

Configure source database heartbeats

-- 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, INSERT, UPDATE ON streamkap.streamkap_heartbeat TO STREAMKAP_USER;

-- Insert the first row into the heartbeat table
INSERT INTO streamkap_heartbeat (text) VALUES ('test_heartbeat');

​

Streamkap Setup

​

1. Create the Source

• Navigate to Add Connectors.
• 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 for setup instructions.
• Username: Username to access the database. By default, Streamkap scripts use streamkap_user.
• Password: Password to access the database.
• Heartbeats: Crucial for low and intermittent traffic databases. Enabled by default.
  • See Heartbeats for setup instructions.
• Connection Timezone: The timezone of your database.

​

3. Snapshot Settings

• Use GTID: If your database is using GTID, leave this as ‘Yes’. See Enable GTID for more information.
  • If ‘No’, please ensure you create the signal table as described in Method 2.
• Signal Table Database: Streamkap will use a table in this database to manage snapshots. See Enable Snapshots for more information.

​

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 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 for details.

​

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.