MySQL and MariaDB

The mysql and mariadb sources read the binary log the way a replica does. Use mysql for MySQL 8 and mariadb for MariaDB; the configuration is the same.

Prerequisites

Server settings

Binary logging must be on, in row format, with full row images. In my.cnf:

[mysqld]
server-id        = 1          # any id, unique in your replication topology
log-bin          = mysql-bin
binlog_format    = ROW
binlog_row_image = FULL
# Keep binlogs long enough to cover a stopped task (7 days here).
binlog_expire_logs_seconds = 604800

MySQL 8 enables binary logging and row format by default; check the values with:

SELECT @@GLOBAL.log_bin, @@GLOBAL.binlog_format, @@GLOBAL.binlog_row_image;
-- MySQL only: must not contain PARTIAL_JSON
SELECT @@GLOBAL.binlog_row_value_options;

The task fails with a precise message when a setting is wrong, for example:

mysql: binlog_format is MIXED but change capture needs ROW; run SET GLOBAL binlog_format = 'ROW' and persist it in the server config
mysql: binlog_row_image is MINIMAL but change capture needs FULL; run SET GLOBAL binlog_row_image = 'FULL' and persist it in the server config
mysql: binlog_row_value_options = PARTIAL_JSON logs JSON diffs; set it to '' so full values are logged
mysql: binary logging is disabled; start the server with --log-bin (and --server-id)

On Amazon RDS for MySQL or MariaDB, automated backups enable the binary log; set binlog_format = ROW in the parameter group and extend retention with CALL mysql.rds_set_configuration('binlog retention hours', 168);.

A dedicated user

CREATE USER 'cdc'@'%' IDENTIFIED BY 'change-me';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc'@'%';
GRANT SELECT ON shop.products TO 'cdc'@'%';

REPLICATION SLAVE lets the task stream the binlog; REPLICATION CLIENT lets it read the current binlog position and list binlog files; SELECT serves the snapshot and the schema. Missing grants fail the task with the grant to add, for example (grant REPLICATION SLAVE, REPLICATION CLIENT ON *.*).

Connection string

The go-sql-driver/mysql DSN format. The address must be TCP (the binlog protocol does not use Unix sockets); without a port, 3306 is used.

cdc:change-me@tcp(db.internal:3306)/shop
cdc:change-me@tcp(db.internal:3306)/shop?tls=true

The database comes from the task's database field, the connector's database field, or the DSN path, in that order. Without any: no database: set source.database or put it in the dsn. The service connects with UTC as the session time zone, so TIMESTAMP values match the binlog.

Task fields

How changes are applied

• Snapshot. Keyset pagination over the primary key, 5,000 rows per query. Tables without a primary key are read with a plain scan; they also need mapping.primaryKey so documents have an id.
• Inserts and updates become upserts, committed per transaction (at the XID event). An update that changes the primary key deletes the old document first.
• Deletes delete the document.
• TRUNCATE TABLE on the task's table holds the task unless allowTruncate is on.
• Other DDL on the table (ALTER, RENAME, …) makes the source reload the column list before decoding further rows.
• MariaDB JSON columns (stored as LONGTEXT with a json_valid check) are recognised and decoded as JSON.

Positions and resume

Positions are binlog coordinates file:offset, for example mysql-bin.000003:1234, taken right after each transaction's commit. The server keeps no per-replica state, so nothing needs cleaning up when a task is deleted. On resume the source checks that the binlog file still exists (SHOW BINARY LOGS); if it was purged, or the server answers with error 1236, the position is lost and the task resyncs automatically. Keep binlog retention longer than any planned downtime.

GTIDs are not used: after a failover to another server the coordinates differ, and the task needs a resync (it detects a missing binlog file by itself; otherwise use Resync).

Troubleshooting