Enable the Binary Log on MySQL Server
The MySQL server must be configured to use a row-level binary log, which is described in more detail in the MySQL documentation. MySQL’s binary log, or binlog, records all operations in the same order they are committed by the database, including changes to the schemas of tables or changes to data stored within the tables. MySQL uses its binlog for replication and recovery.
Debezium’s MySQL connector reads MySQL’s binary log to understand what and in what order data has changed. It then produces a change event for every row-level insert, update, and delete operation in the binlog, recording all the change events for each table in a separate Kafka topic. The Debezium MySQL connector requires every event it processes to be formatted as row-level events.
Important
If the MySQL server used by the connector is a replica, then all of that replica’s MySQL primary servers
must also be configured to use a row-level binary log. Without this, the events from the primary will be
in the wrong format and will be replicated to the secondary’s binlog in the wrong format.
Note
If the MySQL binlog events are in the wrong format, the connector will fail with an error that identifies
the event and a message similar to binlog probably contains events generated with statement or mixed based replication format.
If the event is for a table other than the tables being captured by the connector, you can add a regular expression
that matches that problematic event’s DML or DDL statement to the internal.database.history.ddl.filter connector
configuration property. However, if the event is for a table that is being captured, then skipping the event would
result in lost changes, and the only practical way to remediate the problem is to perform another snapshot of the
table(s) by creating a new connector with a different name. This can be expensive, which is why it’s important
to ensure the MySQL server’s binlog format is set correctly before starting a connector.
Setting the MySQL binlog format is most often done in the MySQL server configuration file, and will look similar to the following fragment:
server-id = 223344
log_bin = mysql-bin
binlog_format = row
binlog_row_image = full
expire_logs_days = 10
where:
- the value for server-id must be unique for each server and replication client within the MySQL cluster. When you set up the connector, you also assign the connector a unique server ID.
- the value for log_bin is the base name for the sequence of binlog files.
- the value for binlog_format must be set to row or ROW.
- the value for binlog_row_image must be set to full or FULL.
- the value for expire_log_days is the number of days for automatic binary log file removal. The default is 0, which means “no automatic removal,” so be sure to set a value that is appropriate for your environment.