Binlog

Set up MySQL binary logging, the capture user and TLS so SQL Flow can stream row changes from the binlog.

SQL Flow captures MySQL changes by reading the row-based binary log (binlog) over the replication protocol, the way a MySQL replica does. Nothing is installed on the database host; you configure the server and create a user.

How it works

Before the initial load, SQL Flow records the server's current binlog position, so changes made during the load aren't lost. The agent then connects as a replica, streams the binlog from that position, and decodes the row events of the captured tables. After a restart, it resumes from the last binlog file and offset it saved. SQL Flow keeps nothing on the MySQL server, so the server's binlog retention decides how far back a stopped or paused pipeline can resume.

Requirements

Setup

1. Configure binary logging in the server's option file, then restart MySQL. Replace <seconds> with your binlog retention.

   [mysqld]
   log_bin                    = binlog
   binlog_format              = ROW
   binlog_row_image           = FULL
   binlog_row_metadata        = FULL
   binlog_expire_logs_seconds = <seconds>

2. Confirm the settings.

   SELECT @@log_bin, @@binlog_format, @@binlog_row_image, @@binlog_row_metadata,
          @@binlog_row_value_options, @@binlog_expire_logs_seconds;

   @@binlog_row_value_options must return an empty string. If it returns PARTIAL_JSON, clear it with SET PERSIST binlog_row_value_options = '';.

3. Create the capture user. Replace <agent_host>, <password> and <database_name>.

   CREATE USER 'sqlflow'@'<agent_host>' IDENTIFIED BY '<password>' REQUIRE SSL;
   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sqlflow'@'<agent_host>';
   GRANT SELECT ON <database_name>.* TO 'sqlflow'@'<agent_host>';

   Grant the replication privileges to the user itself, not through a role: the startup check doesn't count privileges that come from a role.

4. Give the server a TLS certificate that the agent can validate: issued for the host name or address the agent connects to, by a certificate authority that the agent trusts. If the certificate authority is private, install its certificate in the agent host's trusted root store, or name it with the connection option sslRootCertificate (see Encrypted data). The self-signed certificate MySQL generates on its own doesn't pass this check.

5. In the pipeline wizard, choose MySQL on the Source step, fill in Host, Port, Database, Username and the password, leave Encryption at Require (default) unless you have a reason to relax it, and click Test connection & list tables.

The source test doesn't check the binlog settings or the replication privileges. The agent checks them when the pipeline starts, and stops with a message that lists everything that is missing. See Troubleshooting.

Encrypted data

The connection's Encryption field has two choices. The same setting applies to the agent's ordinary connections and to its binlog connection.

Use Prefer only for a server you trust on a network you trust.

Under Require (default), the agent validates the server certificate against the trusted root certificates of the agent host. For a private certificate authority, either install its certificate in the agent host's trust store, or name a PEM file that holds it with the connection option sslRootCertificate, a path on the agent host. The wizard has no field for it; set it on a saved connection with the sqlflow CLI. Replace <host>, <database_name>, <ca_file> and <NAME>, the environment variable that holds the password on the agent host.

sqlflow connection add orders-mysql --engine mysql --host <host> --port 3306 --db <database_name> --user sqlflow --encryption require --opt sslRootCertificate=<ca_file> --secret-ref env:<NAME>

Pros and cons

Pros

• Nothing to install on the database host.
• MySQL pushes binlog events to the agent as they are written; there is no polling.
• Updates and deletes carry the whole old row, and large text and blob values arrive in full in the event.
• SQL Flow holds nothing on the server, so a stopped pipeline never fills the server's disk.
• Compressed binlog transactions (binlog_transaction_compression) are supported.
• TRUNCATE TABLE and schema changes on the captured tables are captured from their statements in the binlog.

Cons

• The binlog settings are server-wide, and binlog_row_metadata = FULL makes every row event carry column names.
• Resuming depends on the server's binlog retention. If the binlog a pipeline needs has been purged, the pipeline needs a reload.
• The replication privileges apply to the whole server.
• With Encryption at Require (default), the server must present a certificate the agent trusts.

Limitations

• SQL Flow tracks its position by binlog file and offset, not GTID. After a failover to another server, the saved position doesn't exist there, and the pipeline needs a reload.
• MySQL logs TRUNCATE TABLE and DDL as statements, not row events. SQL Flow reads them for the captured tables, applies a TRUNCATE to the target, and propagates schema changes. See Schema changes.
• The source test lists only the tables in the database named in Database.
• Authentication plugins other than caching_sha2_password and mysql_native_password are refused.

Column types

No column type is refused at the start. Tested: signed and unsigned integer types, BIT, BOOLEAN, DECIMAL, FLOAT, DOUBLE, CHAR, VARCHAR, BINARY, VARBINARY, the TEXT and BLOB types, JSON, DATE, TIME, DATETIME, TIMESTAMP, YEAR, ENUM and SET. Limits you can hit:

• A column type SQL Flow can't decode from the binlog, such as MySQL 9 VECTOR, stops the pipeline at the first change to the table. See Unsupported column type.
• A zero date (0000-00-00), or a date with a zero month or day, is captured as 0001-01-01.
• TIMESTAMP values are captured in UTC.
• Inside a JSON document, a decimal, date, time or binary value that MySQL stores in its own format, for example one that JSON_OBJECT took from a DECIMAL or DATE column, is captured as a string that starts with opaque:.
• GEOMETRY and the other spatial types are captured as MySQL's internal bytes: a 4-byte SRID followed by WKB.
• Text in the armscii8, dec8, geostd8, hp8, keybcs2 or swe7 character set is read as UTF-8, so a value that isn't valid UTF-8 arrives as bytes.
• Generated columns, and the MariaDB types UUID, INET4 and INET6, aren't tested.

See Data type support by engine for every type.

Troubleshooting

Missing privileges or settings at start

MySQL CDC reader is missing privileges / settings

Cause: the agent's startup check found something missing. The message lists each item, for example REPLICATION SLAVE, REPLICATION CLIENT, binlog_format=ROW (currently 'MIXED'), binlog_row_metadata=FULL (currently 'MINIMAL' …), or binlog_row_value_options='' (currently 'PARTIAL_JSON' …).

Fix: apply each item from Setup, then start the pipeline again. If the privileges come from a role, grant them to the user directly.

Resume position purged from the binlog

MySQL binlog resume position purged (error 1236)

Cause: the binlog file the pipeline needs to resume from no longer exists on the server. Its retention expired, someone ran PURGE BINARY LOGS or RESET MASTER, or the pipeline now points at a different server.

Fix: reload the pipeline, which runs the initial load again from a fresh binlog position. Kafka, Google Pub/Sub and Fabric Open Mirroring targets can't be reloaded; create a new pipeline for them. To prevent a repeat, raise binlog_expire_logs_seconds above the longest time a pipeline may be stopped or behind.

Unsupported column type

Unsupported column type: <type> (0x<code>)

Cause: a captured table has a column type SQL Flow can't decode from the binlog, for example a MySQL 9 VECTOR column. The pipeline stops at the first change to that table.

Fix: remove the table from the pipeline (see Changing tables), then start the pipeline again.

Partial JSON update in the binlog

Encountered a PARTIAL_UPDATE_ROWS_EVENT (binlog event type 39). This is emitted when binlog_row_value_options=PARTIAL_JSON, which makes MySQL log a partial JSON diff instead of a full row image; the CDC reader cannot reconstruct it and refuses to proceed to avoid silently corrupting rows. Set GLOBAL binlog_row_value_options='' on the source and restart capture.

Cause: binlog_row_value_options was set to PARTIAL_JSON after the pipeline started, so MySQL logged a JSON change as a partial update. The startup check refuses this setting, so it appears only when the setting changes later.

Fix: clear the setting with SET PERSIST binlog_row_value_options = '';, then reload the pipeline. A restart alone stops again on the same binlog event. Kafka, Google Pub/Sub and Fabric Open Mirroring targets can't be reloaded; create a new pipeline for them.

TLS handshake with the database fails

TLS handshake failed

Cause: with Encryption at Require (default), the server doesn't offer TLS, its certificate doesn't chain to an authority the agent trusts, or the certificate's name doesn't match the Host you entered. MySQL's self-signed certificate fails for the last two reasons.

Fix: install a certificate on the server that matches the host name, and trust its certificate authority on the agent host or name it with sslRootCertificate. Enter the host name the certificate was issued for, not an IP address it doesn't list. Choose Prefer only for a server you trust on a network you trust.

Retired sslMode option

option 'sslMode' is retired

Cause: the connection has the sslMode option, which the Encryption setting replaces. The Connections page shows such a connection's encryption as Retired option, edit and save.

Fix: open the connection on the Connections page, choose Encryption, and save it, or run sqlflow connection edit <connection> --encryption require. A pipeline keeps its own copy of its connections: pause it, replace the copy with sqlflow pipeline set-connection <pipeline> --which source --from <connection>, giving the password again, then start it.

Unsupported authentication plugin

Auth plugin '<plugin>' is not supported

Cause: the server asked the agent to authenticate with a plugin other than caching_sha2_password or mysql_native_password.

Fix: create the capture user with caching_sha2_password.

See also

• MySQL sources
• Lag, spool and log retention
• Source log retention
• Initial load
• MySQL target