MySQL (Generic)
MySQL Change Data Capture Setup with Streamkap
Prerequisites
- MySQL version ≥ 5.7
- MySQL binlog enabled on the primary server
- Connection details including Server ID
- Streamkap user and role
Granting Privileges
It's recommended to create a separate user and role for Streamkap to access your MySQL database. Below is an example script that does that.
-- 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';
Enable Snapshots
You can perform ad-hoc snapshots of all or some of your tables in the Streamkap app. See Snapshots & Backfilling for more information.
To enable this feature, there are 2 methods available for MySQL databases.
Method 1: Enable GTID (Recommended)
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.
GTIDs only available in MySQL version ≥ 5.6.5
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 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
. Streamkap will use this collection for managing snapshots.
Please create the signal table with the name
streamkap_signal
in a new schema calledstreamkap
. It will not be recognised if given another name.
-- 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';
Update Server Configuration File
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 Streamkap needs to perform incremental updates.
- 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
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.
Verify binary logs are enabled
You can either:
- Run the following SQL query on the DB instance
SHOW VARIABLES LIKE '%log_bin%';
. Result should beON
- Run
SHOW BINARY LOGS
If you upgrade your database version to 8.0.23 or above, please set the parameter
binlog_row_metadata
value toMINIMAL
before upgrading. Failing to do this may result in your MySQL connectors failing
Consider Access Restrictions
- Visit Connection Options to ensure Streamkap can reach your database
Setup MySQL 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
- Heartbeat - Required for low volume connectors. See MySQL Heartbeats
- Connection Timezone - The timezone of your database
-
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 is recommended so your source and destination timestamps are consistent.
- 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 here
- Signal Table Database: Streamkap will use a table in this database to manage snapshots e.g.
public
. See Enable Snapshots for more information
- Signal Table Database: Streamkap will use a table in this database to manage snapshots e.g.
- Connect via SSH Tunnel. See SSH Tunnel
- Advanced Parameters
- Represent Binary Data As (Default
bytes
)
- Represent Binary Data As (Default
- 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.
- Click Save
The connector will take approximately 1 minute to start processing data.
Updated about 1 month ago