Based on Binlog

In the landscape of modern distributed architectures, the demand for real-time data consistency is paramount. Traditiona

In the landscape of modern distributed architectures, the demand for real-time data consistency is paramount. Traditional methods of synchronizing data—such as frequent database polling—are increasingly viewed as inefficient, as they introduce unnecessary latency and impose a significant performance tax on the primary database. To overcome these limitations, engineers turn to Change Data Capture (CDC), a technique that leverages the MySQL Binlog (Binary Log) to achieve seamless, event-driven data synchronization.
The MySQL Binlog is a critical component of the database engine that maintains a chronological record of all changes made to the data. Specifically, it captures Data Definition Language (DDL) statements (such as CREATE, ALTER, or DROP) and Data Manipulation Language (DML) statements (such as INSERT, UPDATE, or DELETE). It is important to note that the Binlog does not record read-only operations like SELECT or SHOW, making it an efficient stream of state changes rather than a full audit of all queries.

Historically, the Binlog has served two fundamental purposes:

  • Replication: It acts as the backbone for Master-Slave architectures, where the master node transmits its Binlog to slave nodes, which then replay the events to stay in sync.
  • Data Recovery: Through tools like mysqlbinlog, administrators can perform point-in-time recovery, restoring a database to a specific state by replaying logs up to a precise moment.

In contemporary microservices, however, the Binlog is repurposed as a real-time event stream. By "listening" to these logs, developers can trigger downstream actions—such as refreshing a Redis cache, updating an Elasticsearch index, or pushing messages to a queue—the moment a database change occurs.

Binlog Formats and Configuration

To implement a reliable CDC pipeline, the first step is choosing the correct Binlog format. MySQL offers three distinct modes, each with specific trade-offs regarding performance and accuracy:

  • Statement-Based Replication (SBR): This mode records the actual SQL statements executed. While it results in smaller log files and lower I/O overhead, it is prone to data inconsistency. This is because certain "non-deterministic" functions (e.g., UUID(), NOW(), or RAND()) may produce different results when replayed on a slave node.
  • Row-Based Replication (RBR): Instead of the SQL command, this mode records the actual change to each individual row. This is the gold standard for CDC because it guarantees absolute data integrity and eliminates the risks associated with non-deterministic functions. The trade-off is a significantly larger log volume.
  • Mixed-Based Replication (MBR): A hybrid approach where MySQL automatically switches between Statement and Row modes based on the nature of the query.

For production-grade CDC applications, Row-based logging is highly recommended. To enable and configure this, you must modify your MySQL configuration file (typically my.cnf or my.ini) under the [mysqld] section:

[mysqld]
# Enable binary logging
server-id = 1
log-bin = mysql-bin

# Use ROW mode for high-fidelity CDC
binlog_format = ROW

# Capture the full content of the row for easier parsing
binlog_row_image = FULL

After applying these changes and restarting the service, you can verify the status using:

SHOW VARIABLES LIKE 'log_bin';

Industry-Standard Binlog Listeners

Parsing raw binary files is a complex task involving low-level network protocols and intricate parsing logic. Rather than building a parser from scratch, most engineering teams utilize mature, open-source frameworks.

Canal

Developed by Alibaba, Canal is one of the most widely adopted components for MySQL Binlog subscription. It operates by simulating a MySQL Slave. By mimicking the Slave-Master interaction protocol, Canal "tricks" the MySQL Master into sending its Binlog via the Dump protocol. Canal then parses these binary events into structured objects (such as Java POJOs), making them easy for developers to consume.

Debezium

Debezium is a distributed platform built on top of Kafka Connect. It is designed for high-scale, cloud-native environments. Debezium connects to various databases (including MySQL, PostgreSQL, and MongoDB) to capture row-level changes and streams them into Apache Kafka in standardized formats like JSON or Avro. This makes it an ideal choice for organizations already heavily invested in the Kafka ecosystem.

Practical Implementation: A Java Example with Canal

The following example demonstrates how to implement a basic Java client using the Canal library to subscribe to database changes and process INSERT, UPDATE, and DELETE events.

1. Dependency Management

If you are using Maven, add the following dependency to your pom.xml:

<dependency>
    <groupId>com.alibaba.otter</groupId>
    <artifactId>canal.client</artifactId>
    <version>1.1.5</version>
</dependency>

2. Implementation Logic

The client connects to the Canal server, subscribes to a specific database pattern, and enters a continuous loop to poll for new entries.

import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
import com.alibaba.otter.canal.protocol.Message;
import com.alibaba.otter.canal.protocol.CanalEntry.*;
import com.google.protobuf.InvalidProtocolBufferException;

import java.net.InetSocketAddress;
import java.util.List;

public class BinlogClientExample {
    public static void main(String[] args) {
        // Initialize the connection to Canal Server
        CanalConnector connector = CanalConnectors.newSingleConnector(
                new InetSocketAddress("127.0.0.1", 11111),
                "example", "", ""
        );

        int batchSize = 1000;
        try {
            connector.connect();
            // Subscribe to all tables within 'mydb'
            connector.subscribe("mydb\\..*"); 
            connector.rollback();
            
            while (true) {
                // Fetch a batch of messages
                Message message = connector.getWithoutAck(batchSize);
                long batchId = message.getId();
                int size = message.entries().size();
                
                if (batchId == -1 || size == 0) {
                    try {
                        Thread.sleep(1000);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                } else {
                    processEntries(message.entries());
                }
                
                // Acknowledge successful processing
                connector.ack(batchId);
            }
        } finally {
            connector.disconnect();
        }
    }

    private static void processEntries(List<Entry> entries) {
        for (Entry entry : entries) {
            // Skip transaction boundary events
            if (entry.getEntryType() == EntryType.TRANSACTIONBEGIN || 
                entry.getEntryType() == EntryType.TRANSACTIONEND) {
                continue;
            }

            RowChange rowChange;
            try {
                rowChange = RowChange.parseFrom(entry.getStoreValue());
            } catch (InvalidProtocolBufferException e) {
                throw new RuntimeException("Failed to parse Binlog entry", e);
            }

            EventType eventType = rowChange.getEventType();
            System.out.println(String.format("--> DB: %s, Table: %s, Action: %s",
                    entry.getHeader().getSchemaName(),
                    entry.getHeader().getTableName(),
                    eventType));

            for (RowData rowData : rowChange.getRowDatasList()) {
                if (eventType == EventType.DELETE) {
                    printColumns(rowData.getBeforeColumnsList());
                } else if (eventType == EventType.INSERT) {
                    printColumns(rowData.getAfterColumnsList());
                } else {
                    System.out.println("--- Before Update ---");
                    printColumns(rowData.getBeforeColumnsList());
                    System.out.println("--- After Update ---");
                    printColumns(rowData.getAfterColumnsList());
                }
            }
        }
    }

    private static void printColumns(List<Column> columns) {
        for (Column column : columns) {
            System.out.println(column.getName() + " : " + column.getValue() + "    updated=" + column.getUpdated());
        }
    }
}

Critical Considerations for Production

Deploying a Binlog-based architecture in a production environment requires careful planning to avoid system instability.

  • Idempotency is Mandatory: Due to network fluctuations or consumer restarts, the same Binlog event might be delivered more than once. Your downstream logic (e.g., updating a cache or a search index) must be idempotent, meaning that processing the same event multiple times does not change the result beyond the initial application.
  • Handling Large Transactions: A single transaction that modifies millions of rows can create a massive Binlog entry. This can lead to significant parsing latency or even Out of Memory (OOM) errors in tools like Canal. It is a best practice to design business logic to avoid "mega-transactions" by breaking them into smaller, manageable chunks.
  • Schema Evolution (DDL Changes): When a table structure changes (e.g., via ALTER TABLE), the downstream consumer must be able to handle the new schema. If the consumer's parsing logic is hardcoded to an old schema, it may fail to deserialize the new Binlog format. Implementing a schema registry or a dynamic parsing mechanism is highly recommended.

Conclusion

Leveraging MySQL Binlog for data synchronization represents a paradigm shift from "pull-based" to "push-based" data architectures. By utilizing tools like Canal or Debezium, organizations can build highly decoupled, low-latency systems that respond to data changes in near real-time. Whether you are synchronizing search engines, invalidating distributed caches, or building complex ETL pipelines, mastering Binlog-based CDC is a vital skill for any modern backend architect.