Data synchronization principles based on MySQL Binlog

In distributed systems and big data applications, real-time data synchronization between databases is a fundamental and important requirement. Whether it is master-slave replication, data backup, or building data warehouses and real-time analysis systems, timely and accurate capture and transmission of data changes are necessary. MySQL's binary log (Binlog), as a mechanism for recording all change operations in the database, provides a technical foundation for efficient and reliable data synchronization. This article will analyze in detail the working principle of data synchronization based on MySQL Binlog, including the underlying client/server protocol mechanism and the processing flow of Binlog events.

MySQL Client/Server Protocol

The MySQL client/server protocol is the basis for communication between the MySQL client and the MySQL server. The interaction process between the MySQL client and server is divided into the handshake authentication phase and the command execution phase:

图片加载失败

mysql-cs-seq

Handshake Authentication Phase

After the client establishes a connection with the server, the server sends a handshake initialization packet (Handshake Initialization Packet) to the client. The handshake initialization packet contains information such as protocol version, server version, character encoding, and server capability flags. Among these, the server capability flags are used to negotiate communication methods with the client, such as whether to connect to a specified database, whether to use a compressed protocol, etc.

After the client receives the handshake initialization packet, it selects the necessary features for this connection from the server capability flags and generates a client capability flag. The client writes the username, password, database name (optional), client capability flag, and other information into the client authentication packet (Client Authentication Packet) and sends it to the server. After receiving the authentication packet, the server checks whether the authentication information is correct. If correct, it returns an OK Packet to the client, ending the handshake authentication phase and entering the command execution phase.

Command Execution Phase

In the command execution phase, the client sends a command (Command Packet) to the server, and the server returns the command execution result. Depending on the command executed, the server may return one or more response packets. For example, when the client sends a "select * from user" query command, the server will sequentially return:

1. Result Set Header Packet - Contains the number of columns in the query result
2. Several Field Packets - Contain the descriptors of one of the columns in the query result
3. One EOF Packet - Indicates that all Field Packets have been sent
4. Several Row Data Packets - Contain the data of one of the rows in the query result
5. One End Packet - Indicates that all Row Data Packets have been sent

When the client sends an exit command, the command execution phase ends, and the connection between the client and server is closed.

Protocol Message Structure

The message structure of the MySQL client/server protocol is divided into two parts: the message header and the message body:

图片加载失败

mysql-cs-body

• Message Header: Occupies a fixed 4 bytes, containing two fields: message length and sequence number
  • Message Length: Indicates the length of the message data in bytes
  • Sequence Number: Used to ensure message order; each time the client initiates a request, the sequence number starts from 0
• Message Body: Used to store the data of requests and responses, with fields determined by the type of data packet

MySQL Binlog

MySQL logs include binary logs (Binlog), error logs (Error Log), query logs (Query Log), slow query logs (Slow Query Log), etc. By default, the MySQL server only opens the error log to reduce I/O overhead. However, in practical application scenarios, it is necessary to open MySQL's binary log, as Binlog records all database modification operations (including INSERT, UPDATE, DELETE, and other DML statements that modify data, as well as CREATE, ALTER, DROP, and other DDL statements that modify data tables), which is essential for MySQL replication, backup, and recovery.

MySQL Replication Mechanism

MySQL replication refers to the process where one MySQL server obtains Binlog logs from another MySQL server, parses the logs, and applies the changes to itself. MySQL replication has three modes: row-based replication (Row mode), statement-based replication (Statement mode), and mixed-mode replication (Mixed mode). Correspondingly, Binlog formats are also divided into Row, Statement, and Mixed.

Row Mode

Does not record contextual information for each SQL statement execution; it only records the values of all data rows affected by the SQL statement before and after modification. Therefore, it does not encounter issues with incorrect replication due to the use of functions or triggers. The downside is that the volume of Binlog logs is relatively large in this mode.

Statement Mode

Records each SQL statement that modifies data. Compared to Row mode, Statement mode does not need to record changes for each row, which can reduce the volume of Binlog logs and lower I/O overhead. The downside is that it may lead to data inconsistency during replication when functions or triggers are used.

Mixed Mode

A hybrid use of Row mode and Statement mode, where MySQL selects the log storage method based on the executed SQL statement. General operations are recorded using Statement format, while operations that may not replicate correctly in Statement mode are recorded using Row format.

Binlog Event Mechanism

In the Binlog file, database changes are recorded in the form of events (Binlog Event). For example, in Row mode, executing the "update user set password='admin'" statement will record an UpdateRowsEvent in the Binlog file, which contains the values of each updated row before and after modification. By parsing Binlog Events, database changes can be obtained.

Replication Position Location

MySQL replication determines the replication position through the Binlog filename (Binlog Filename) and the offset address of the event in the Binlog file (Binlog Position). This mechanism ensures the accuracy and recoverability of data synchronization.

Steps for Implementing Data Synchronization Based on Binlog

Combining the MySQL client/server protocol and Binlog mechanism, the implementation steps for a data synchronization system based on MySQL Binlog are as follows:

1. Establish Connection and Complete Authentication

• Connect to the MySQL server via TCP
• Receive the handshake initialization packet sent by the server and parse protocol version, server version, and other information
• Generate the client capability flag based on the server capability flags and construct the client authentication packet
• Send the authentication packet and wait for the OK Packet to confirm the connection is successful

2. Send Binlog Synchronization Request

• In the command execution phase, send the COM_BINLOG_DUMP command packet
• Specify the starting Binlog filename and position offset (replication position)
• The server begins to continuously send Binlog event data

3. Parse Binlog Event Stream

• Parse each data packet according to the MySQL protocol message structure
• Identify different types of Binlog Events (such as UpdateRowsEvent, InsertRowsEvent, etc.)
• Correctly parse event content based on Binlog format (Row/Statement/Mixed)

4. Apply Data Changes

• Apply the parsed data changes to the target system
• In Row mode, directly use the values before and after modification for synchronization
• In Statement mode, re-execute the SQL statement
• Record the current processing replication position for fault recovery

5. Handle Exceptions and Recovery

• Monitor connection status and handle exceptions such as network interruptions
• Re-establish the connection based on the saved replication position and continue synchronization
• Ensure the integrity of transaction boundaries to avoid data inconsistency

Through the above steps, the data synchronization system can capture all change operations of the MySQL database in real-time and accurately synchronize these changes to the target system, achieving real-time data consistency. This Binlog-based synchronization mechanism has been widely applied in MySQL master-slave replication, data backup, real-time data warehousing, and other scenarios.