Function of Binary Log in MySQL Replication

This article explains the specific role of the binary log (binlog) in MySQL replication. It covers how the binary log records data modifications on the source database, how these events are transmitted to replica databases, and how replicas apply these logs to maintain data synchronization and consistency across a database cluster.

What is the MySQL Binary Log?

The binary log, often referred to as the binlog, is a set of log files that record all DDL (Data Definition Language) and DML (Data Manipulation Language) statements that modify database schema or content. It does not record statements that merely query data, such as SELECT or SHOW.

In a MySQL replication setup, the binary log acts as the primary source of truth, capturing every change on the master (source) server so those same changes can be duplicated on one or more slave (replica) servers.

The Role of the Binary Log in the Replication Process

The replication process relies on the binary log to transfer data state changes from the source to the replica. This process occurs in three distinct phases:

1. Recording Events on the Source

When a client updates data on the source server, MySQL writes the transaction to the database and simultaneously records the change in the binary log. This logging happens in near real-time as transactions commit.

2. Transmitting Events to the Replica

The replica server initiates a connection to the source server. Once connected, the source server spawns a Binlog Dump Thread. This thread reads the active binary log files and sends the recorded events over the network to the replica.

3. Receiving and Applying Events on the Replica

The replica server utilizes two threads to process the incoming binary log data: * The I/O Thread: This thread connects to the source, receives the binary log updates sent by the Binlog Dump Thread, and writes them locally to files called relay logs. * The SQL Thread: This thread reads the relay logs on the replica and executes the database transactions sequentially. This reproduces the exact state changes that originally occurred on the source.

Binary Log Formats

The binary log can record events in three different formats, which dictate how data is replicated:

• Statement-Based Replication (SBR): The binlog records the literal SQL queries executed on the source (e.g., UPDATE users SET status = 'active' WHERE id = 5). The replica then executes the exact same SQL queries.
• Row-Based Replication (RBR): The binlog records the actual changes made to individual table rows rather than the SQL statements. This format is highly reliable and prevents inconsistencies caused by non-deterministic functions (like NOW() or UUID()).
• Mixed-Based Replication (MBR): MySQL automatically uses statement-based replication by default but switches to row-based replication when it detects queries that could cause inconsistencies if replicated as statements.

Additional Functions of the Binary Log

Beyond replication, the binary log serves other critical administrative purposes: * Point-in-Time Recovery: If a database crashes or data is accidentally deleted, administrators can restore a full backup and then apply the binary log events from the time of the backup to restore the database to the exact moment before the failure. * Data Auditing: Because the binary log records all write operations, it can be analyzed to audit changes made to the database.