Understanding MySQL Master-Slave Replication Principles

Introduction to Master-Slave Replication

MySQL master-slave replication is a mechanism that allows data from one MySQL server (the master) to be copied to one or more MySQL servers (the slaves). This process is achieved by enabling binary logging (binlog) on the master server. The slave servers then use an I/O thread to read the binlog from the master and transfer it to their own relay logs. Subsequently, a SQL thread on the slave reads from the relay log and applies the changes to the slave's database, ensuring data synchronization between master and slave servers.

Benefits of Replication

Through replication, organizations can build one or more slave servers to improve database high availability and scalability. When the master server experiences a failure, operations can be quickly switched to one of the slave servers, which can then be promoted to master status. Since the data is consistent across servers, this transition minimizes system downtime. Additionally, when the database needs to handle more read requests, read traffic can be distributed across multiple slave servers while write requests remain directed to the master server. This architecture enables read/write separation, providing better read scalability and load balancing.

Replication Fundamentals

MySQL master-slave replication supports two primary modes:

• Row-based replication
• Statement-based replication

Row-based replication has been available since MySQL 3.23, while statement-based replication was introduced in version 5.1. Both approaches fundamentally rely on the master server's binlog. The master records binlog entries, and the slave replays these entries on its own server, ensuring data consistency between master and slave.

Binary Log (binlog)

In MySQL, logs are categorized into two dimensions: MySQL server logs and storage engine logs. The binlog mentioned earlier belongs to the MySQL server logs and is also known as the binary log. It records all changes made to the MySQL database.

The replication mode (row-based or statement-based) is related to how binlog is stored. Binlog supports three storage formats:

• Statement
• Row
• Mixed

Statement Format

This format records only the SQL statements that modify data, effectively reducing binlog data volume and improving performance when reading and replaying based on binlog.

Row Format

This format records only the modified rows. Consequently, binlog logs in row format are typically larger than in statement format. Row-based binlogs provide complete and clear records of all data changes. However, a potential drawback is the increased log volume. For example, a single UPDATE statement might modify all rows, and ALTER TABLE operations might affect every record.

Mixed Format

This format combines Statement and Row approaches. For statements like UPDATE or ALTER TABLE, it uses Statement format, while for data modifications like UPDATE and DELETE, it uses Row format. This hybrid approach exists because Statement format only records SQL statements, which may not always be correctly replayed on the slave server due to potential ordering issues.

When is Binlog Recorded?

MySQL records binlog when a transaction is committed, not according to the statement execution order. After recording the binlog, the server notifies the underlying storage engine to commit the transaction. This can potentially lead to errors if statement order is incorrect.

Inspecting Binlog

In MySQL 5.6, for example, binlog is disabled by default. You can check binlog configuration using the command SHOW VARIABLES LIKE '%log_bin%'.

The log_bin variable indicates whether binlog is enabled, with a default value of OFF. The log_bin_basename provides the full name of the binlog storage file, which typically has an incrementing number appended (e.g., mysql-bin.000001). The log_bin_index variable gives the binlog index file name (e.g., mysql-bin.index). The sql_log_bin variable allows disabling binlog for the current session when binlog is enabled.

You can view all binlog files using the SHOW BINARY LOGS command in MySQL.

Viewing Binlog Content

After identifying the available files, you can examine binlog content using the SHOW BINLOG EVENTS command. To view a specific binlog file, such as mysql-bin.000001, use SHOW BINLOG EVENTS IN 'mysql-bin.000001'.

Each INSERT operation starts a transaction. You might wonder why transactions are created even when executing a simple INSERT without explicitly starting a transaction. This is because MySQL uses auto-commit (AUTOCOMMIT) mode. The InnoDB storage engine, which supports transactions, ensures that all user activities occur within transactions. You can verify auto-commit status with SHOW VARIABLES LIKE '%AUTOCOMMIT%', which returns ON when enabled.

Core Replication Steps

Assuming the master has binlog enabled and is recording properly, the replication process follows these steps:

1. The slave starts an I/O thread and establishes a client connection with the master.
2. The master starts a binlog dump thread, reads binlog events, and sends them to the slave's I/O thread.
3. The slave's I/O thread receives binlog events and writes them to its Relay Log.
4. The slave starts a SQL thread that reads from the Relay Log and replays the data, completing the slave's data update.

In summary, the master has only one thread (binlog dump), while the slave has two threads (I/O and SQL).

Relay Log

Relay Log is similar to binlog. Before MySQL 4.0, Relay Log did not exist, and the replication process involved only two threads. However, this approach had problems: replication needed to be synchronous, making it vulnerable to interruptions and inefficient. For example, the master had to wait for the slave to finish reading before sending the next binlog event, similar to a blocking channel.

Imagine a counter scenario where you must hand documents directly to a clerk without a place to set them down (blocking channel) versus having a surface where you can place documents for the clerk to pick up later (non-blocking channel).

The introduction of Relay Log decoupled the event acquisition and replay processes, allowing them to occur asynchronously. Relay Log acts as a buffer. Relay Log uses a relay-log.info file to track replication progress, indicating where the next event should be written. This file is updated by the SQL thread.

Relay Log Core Parameters

Several key parameters control Relay Log behavior:

• max_relay_log_size: Maximum size of relay logs. Default is 0, which uses a default size of 1G; otherwise, it uses the specified value.
• relay_log: Defines the relay log name, defaulting to hostname + relay-bin (e.g., hostname-relay-bin).
• relay_log_basename: Full path to relay logs (path + filename), with a maximum length of 256 characters.
• relay_log_index: Full path to the relay log index file, also with a maximum length of 256 characters.
• relay_log_info_file: Name of the relay-log.info file.
• relay_log_info_repository: Method for storing relay log replay data, either FILE or TABLE.
• relay_log_purge: Whether to automatically purge unnecessary relay logs (default ON).
• relay_log_recovery: If enabled, when a slave crashes and relay log is damaged, it discards all unreplayed relay logs and reacquires them from the master (default OFF).
• relay_log_space_limit: Maximum value for relay logs to prevent disk filling. Default is 0 (unlimited).
• sync_relay_log: Controls relay log writing to disk. If set to n, fdatasync() is called after every n binlog events. Default is 10000.
• sync_relay_log_info: Affects how relay log info is persisted, with behavior dependent on relay_log_info_repository and transaction support.

Replication Models

Similar to architectural evolution in software development, database replication models can be scaled as needs grow. Starting with a single MySQL server, organizations can adopt various replication models as their requirements expand.

Master-Multiple Slaves

This is the simplest replication model, suitable for scenarios with few write operations and many read requests. Read requests are distributed across various slave servers, effectively dispersing the master's load and improving read concurrency. Slaves can also serve as disaster recovery repositories without handling other requests.

One creative use case is using a slave as a staging environment database, though this requires careful consideration of data sensitivity and proper access controls.

It's worth noting that with n slaves, the master will have n binlog dump threads. If n is large, replication might cause performance fluctuations on the master. In such cases, cascading replication can be beneficial.

Cascading Replication

In this model, instead of all slaves directly replicating from the master, some slaves replicate from other slaves. For example:

• Slaves B and C replicate from Master A
• Slaves D and E replicate from Slave B
• Slaves F and G replicate from Slave C

Advantages:

• Significantly reduces master's load, as the master only needs to communicate directly with its immediate slaves.

Disadvantages:

• Errors in higher-level servers affect all dependent slaves, amplifying the impact of failures.

Master-Master Replication

In this model, two MySQL servers replicate data from each other. Clients can perform write operations on either server. Any changes on one server are automatically synchronized to the other. This resembles the dual-node mode of Eureka Server, where two registration centers register with each other.

Benefits:

• Improves high availability, as the system remains operational if one server fails.
• Enables horizontal scaling, potentially breaking performance limitations imposed by database concurrency.

However, master-master replication is not entirely reliable, as data conflicts are likely. For example, if replication stops while the system continues writing to both masters, data may become inconsistent between servers, with some data on Server A and other data on Server B, without synchronization. Recovering from such a scenario can be challenging.

Therefore, master-master replication is more valuable for high availability than for load balancing.

Active-Passive Master-Master Replication

This model uses a master-master setup where one server is read-only (passive). Clients do not write to this passive server.

A common use case is performing maintenance or optimization on MySQL without interrupting service. For instance, when modifying table structures:

1. Have Master A (active) and Passive Master B (read-only).
2. Stop replication from A to B by halting the SQL thread on A.
3. Execute time-consuming operations that might require table locks on B. These won't immediately sync to A because replication is paused.

The I/O thread continues writing to Relay Log, but the SQL thread doesn't replay these changes, allowing the active server to continue serving clients without interruption.

Replication Methods

MySQL master-slave replication can be implemented using different methods, each with distinct characteristics.

Asynchronous Replication

This is MySQL's default replication method. In asynchronous replication, the master doesn't proactively send messages to slaves. Instead, it waits for slaves' I/O threads to establish connections, then creates a binlog dump thread to send binlog events to the I/O thread.

After executing transactions and recording them in binlog, the master directly returns to the client without confirming results. The binlog dump thread asynchronously reads and sends binlog to slaves. Request processing and replication are completely separate asynchronous processes.

Synchronous Replication

In synchronous replication, when the master executes a transaction, it must wait for all slaves to complete and commit the transaction before returning success to the client.

It's important to note that the master commits the transaction directly rather than waiting for all slaves to respond. MySQL only delays returning to the client, not the transaction itself.

Synchronous replication significantly impacts performance as it couples client requests with replication. If any slave's replication thread executes slowly, client responses will also be delayed.

Semi-Synchronous Replication

Semi-synchronous replication differs from synchronous replication in that it only requires one slave to commit before returning to the client. If no slave commits within a default timeout, it switches to asynchronous mode for that transaction.

This approach ensures that at least one slave node is available even if the master fails, while reducing wait time compared to fully synchronous replication.

Data Consistency in Replication

As discussed in the core replication steps, the process seems straightforward: the master's binlog dump reads binlog, the slave's I/O thread reads and writes to Relay Log, and the slave's SQL thread reads from Relay Log to replay changes. However, what happens if the I/O thread fails midway? Or if the master crashes? How is data consistency maintained?

The relay-log.info file records the current replication position (binlog and Relay Log position). As long as this file exists, when the slave restarts after a crash, it can resume replication from where it left off. This is similar to Redis's master-slave replication, where both parties maintain an offset for PSYNC incremental data synchronization.

However, in MySQL 5.5 and earlier, replication progress could only be recorded in the relay-log.info file (the relay_log_info_repository parameter only supported FILE). As mentioned earlier, the relay-log.info file was only flushed to disk after sync_relay_log_info transactions. If the slave crashed before this flush, the actual execution position would differ from the recorded position after restart, causing data consistency issues.

In MySQL 5.6, the relay_log_info_repository parameter gained support for TABLE. This allows storing replication progress in the mysql.slave_relay_log_info table, binding progress updates and SQL thread execution into a single transaction. Even if the slave crashes, MySQL's built-in crash recovery mechanism can restore the actual execution position to match the saved progress.

Semi-synchronous replication also presents a consistency challenge. The master commits the transaction before waiting for slave acknowledgment, but what if the slave crashes during this wait? The data exists on the master but not on the slave.

To address this, MySQL 5.7 introduced lossless semi-synchronous replication with the rpl_semi_sync_master_wait_point parameter. In MySQL 5.7, the default value is after_sync, while in MySQL 5.6 it was after_commit.

• after_sync: The master doesn't commit the transaction until receiving confirmation from at least one slave. This ensures consistency even if a slave crashes without responding.
• after_commit: The master commits first, then waits for slave acknowledgment before notifying the client (the original approach).

Traditional Replication vs. GTID Replication

MySQL offers two main replication approaches: traditional replication based on binary log coordinates and Global Transaction ID (GTID) replication introduced in MySQL 5.6.

GTID Replication Principles

GTID replication assigns a unique identifier to every transaction committed on the master, ensuring it has a unique ID across the cluster. In traditional log-based replication, slaves must specify the exact offset for incremental synchronization. If this offset is incorrect, data might be missed, leading to inconsistency.

With GTID replication, slaves inform the master about GTIDs of already executed transactions. The master then returns a list of unexecuted GTIDs to the slave. This ensures that each transaction is executed exactly once on each slave.

A GTID consists of a server_uuid and a transaction_id, formatted as GTID=server_uuid:transaction_id. The server_uuid is auto-generated during database startup and is unique for each machine, stored in the auto.conf file in the data directory. The transaction_id is a sequential, non-repeating number assigned when the transaction is committed.

You can view the server_uuid with:

SELECT @@server_uuid;

Note: server_uuid is MySQL's unique identifier, while server_id is the ID within the cluster to prevent conflicts.

Benefits of GTID Replication

• Uses master_auto_position=1 instead of specifying binlog and position coordinates, making master-slave setup easier than traditional methods.
• Simplifies failover between master and slave, eliminating the need to manually find position and binlog file information.

Setting Up GTID Replication

Both master and slave require specific parameter configurations, typically added to the my.cnf configuration file:

Master Configuration

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server-id=33062200  # Must be unique across the cluster
binlog_format=row

Slave Configuration

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
server-id=33062211  # Must be unique across the cluster

If encountering the error:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

Execute on the slave:

RESET MASTER;

Like traditional replication, use the CHANGE MASTER command, but replace binlog and position with master_auto_position=1:

CHANGE MASTER TO
MASTER_HOST='192.168.112.220',
MASTER_USER='repluser',
MASTER_PASSWORD='repluser123',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

View executed GTIDs with:

SHOW MASTER STATUS;

The mysql.gtid_executed Table

In MySQL 5.7 and later, the gtid_executed value is persisted, with a new gtid_executed table in the mysql database:

SELECT * FROM mysql.gtid_executed;

Table structure:

• source_uuid: The UUID of the server where the GTID originated
• interval_start: Starting transaction ID of the interval
• interval_end: Ending transaction ID of the interval

Each row corresponds to a GTID or group of GTIDs. For rows representing a single GTID, the start and end values are identical. This information corresponds to the Executed_Gtid_Set in SHOW MASTER STATUS output.

The following command shows GTIDs that have been executed and can potentially be purged:

SELECT @@GLOBAL.gtid_purged;

Benefits of the gtid_executed Table

• Records executed GTID information, eliminating the need for the log_slave_updates parameter in MySQL 5.6 for replication.
• GTID information is stored in the gtid_executed table, allowing binlog to be disabled on slaves to save storage space. RESET MASTER clears all data from this table.
• The gtid_executed_compression_period parameter (default 1000) controls table compression after every 1000 transactions.

From MySQL 5.7.6, gtid_mode can be dynamically modified with possible values:

• OFF: GTID transactions not supported
• OFF_PERMISSIVE: New transactions are anonymous, but replication can include both GTID and anonymous transactions
• ON_PERMISSIVE: New transactions use GTID, but replication can include both GTID and anonymous transactions
• ON: GTID transactions supported

Limitations of GTID Replication

• Does not support statements like CREATE TABLE table_name SELECT * FROM table_name
• Does not allow mixing transactional and non-transactional tables within a single transaction
• Does not support CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statements
• When skipping errors in GTID replication, does not support the sql_slave_skip_counter parameter
• Does not support non-transactional storage engines (slaves will report errors)
• Does not support CREATE TABLE ... SELECT statements (master will report errors)
• Does not allow a single SQL statement to update both transactional and non-transactional tables
• Requires all servers in a replication group to have GTID either uniformly enabled or disabled
• Does not support CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements
• Does not support sql_slave_skip_counter parameter