Practical MySQL & MariaDB

MySQL Replication Explained: Setup, GTID, Monitoring, and Troubleshooting Guide

Advanced Topics
目次

1. What Is MySQL Replication? Overview and Use Cases

MySQL replication is a feature that synchronizes copies of a database to other servers in real time. This improves database redundancy and performance. Below, we explain in detail when MySQL replication is used and how it works.

Overview of MySQL Replication

MySQL replication shares database contents across multiple servers using a master server and one or more slave servers. Specifically, data updates recorded in the master server’s binary log are read and applied by slave servers to keep the data synchronized. This enables service continuity by switching to a slave server if the master server encounters a failure.

Use Cases of MySQL Replication

MySQL replication is widely used in the following scenarios:

  • High Availability: In case of failure, switching to a slave server minimizes downtime.
  • Load Balancing: Read-only queries can be distributed to slave servers, reducing the load on the master server.
  • Data Protection and Backup: Because replication duplicates data in real time, it can also serve as a backup mechanism.

Types of Replication

MySQL replication includes the following types based on the synchronization method:

  • Asynchronous Replication: The master proceeds without waiting for confirmation from the slave. This allows faster response times but may result in some data not reaching the slave during a failure.
  • Semi-Synchronous Replication: The master waits for confirmation that data has been received by the slave before proceeding. This provides higher reliability than asynchronous replication, though with slightly slower response times.

The next section explains the fundamental concepts of MySQL replication, including binary logs and GTID.

2. Basic Concepts of MySQL Replication

To understand MySQL replication, it is important to grasp the roles of the binary log and GTID (Global Transaction ID), which play critical roles in the replication process. These elements form the foundation for accurate data replication.

Roles of Master and Slave

In MySQL replication, the master server and slave server have distinct roles. The master records data updates in the binary log and sends them to the slave. The slave applies the received logs to update its data. As a result, the slave maintains the same data as the master.

Binary Log and Relay Log

MySQL replication relies on the following two logs:

  1. Binary Log
  • The binary log records data updates (such as INSERT, UPDATE, and DELETE) on the master server. This allows the slave server to maintain the same data state as the master.
  1. Relay Log
  • The relay log stores the binary log events received from the master on the slave server. The slave’s SQL thread executes the relay log sequentially to apply data changes.

What Is GTID (Global Transaction ID)?

GTID is a mechanism that assigns a unique ID to each transaction, helping maintain consistency across multiple slaves. By using GTID, specifying binary log positions becomes unnecessary. Only transactions not yet retrieved from the master are automatically applied to the slave, greatly simplifying management.

Advantages of GTID

  • Unique Identification: Each transaction is assigned a unique GTID, clearly identifying which transactions have been applied.
  • Easier Recovery: When using GTID, only unapplied transactions are reprocessed after a restart of the master or slave.
  • Efficient Operational Management: Even in large-scale environments with multiple slaves, transaction consistency can be maintained with simplified management.

To use GTID, the settings gtid_mode=ON and enforce_gtid_consistency=ON are required. Configuring these settings on both the master and slave enables GTID-based replication.

The next section explains the specific steps for setting up MySQL replication.

3. MySQL Replication Setup Procedure

This section explains the steps required to set up MySQL replication. By following these steps, you can configure a basic master-slave structure and enable real-time data synchronization.

Configuring the Master Server

First, edit the master server’s configuration file (usually my.cnf or my.ini) to enable the binary log and set the server ID.

  1. Edit the Configuration File
  • Add the following settings under the [mysqld] section and set a unique server ID (for example, 1).
   [mysqld]
   server-id=1
   log-bin=mysql-bin
  • The server-id must be a unique number for each server, and log-bin enables the binary log.
  1. Create a Replication User
  • Create a dedicated replication user on the master server and grant the necessary privileges.
   CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
   FLUSH PRIVILEGES;
  • This user is required for the slave server to access data from the master.
  1. Check the Master Status
  • Check the current binary log file and position (log location). This information is required when configuring the slave server.
   SHOW MASTER STATUS;
  • The File (log file name) and Position displayed by this command will be used in the slave configuration.

Configuring the Slave Server

Next, edit the slave server’s configuration file and configure the server ID and master information.

  1. Edit the Configuration File
  • Set a unique server-id on the slave server as well (for example, 2). This value must differ from the master’s server ID.
   [mysqld]
   server-id=2
  • It is also common to set read_only=ON to prevent data writes on the slave server.
  1. Configure Master Information on the Slave
  • Execute the following command on the slave server, specifying the master’s hostname, user, binary log file name, and position.
   CHANGE MASTER TO
       MASTER_HOST='master_host',
       MASTER_USER='repl',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=123;
  • Enter the values confirmed earlier on the master for MASTER_LOG_FILE and MASTER_LOG_POS.
  1. Start Replication
  • Run the following command on the slave server to start replication.
   START SLAVE;

Verifying Replication Status

Verify that replication between the master and slave is configured correctly.

  • Check Master Status
  SHOW MASTER STATUS;
  • Check Slave Status
  SHOW SLAVE STATUS\G;
  • If Slave_IO_Running and Slave_SQL_Running both show Yes, replication is operating normally.

The next section explains advanced replication configurations, including differences between asynchronous and semi-synchronous replication and setup using GTID.

4. Types of Replication and Advanced Usage

MySQL replication includes two main types based on the data synchronization method: asynchronous replication and semi-synchronous replication. By understanding their characteristics and choosing based on your use case, you can improve both system performance and reliability. This section also explains the benefits of using GTID (Global Transaction Identifier) for replication configuration.

Differences Between Asynchronous and Semi-Synchronous Replication

1. Asynchronous Replication

In asynchronous replication, the master server returns a response to the client immediately after completing a transaction. In other words, the master can continue processing new requests even while synchronization to the slave server is delayed. This provides excellent response performance and is suitable for systems focused on load distribution. However, during a failure, there is a risk that data not yet applied to the slave server may be lost.

2. Semi-Synchronous Replication

In semi-synchronous replication, the master server returns a response to the client only after confirming that data transfer to the slave server has completed. This improves data consistency, but because it waits for the slave, transaction response times may increase. Semi-synchronous replication is well suited for systems that require high data consistency or environments where data reliability is the top priority.

Replication Using GTID

GTID (Global Transaction Identifier) assigns a unique ID to each transaction and maintains transaction consistency between the master and slave. Enabling GTID makes replication management easier compared to traditional binary log position-based replication.

Advantages of GTID

  • Improved Data Consistency: With GTID, the slave can automatically identify transactions that have not yet been applied, making it easier to preserve consistency.
  • Simplified Replication Management: GTID improves efficiency for failover, master/slave switching, and recovery tasks. Because you no longer need to specify binary log positions, operations become simpler.

GTID Replication Configuration

To use GTID, you must add and enable the following options in the configuration files of both the master and the slave.

Master Server Configuration

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON

Slave Server Configuration

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON

In a GTID-enabled environment, replication is performed automatically via GTID simply by setting the master information on the slave using the CHANGE MASTER TO command.

The next section explains maintenance methods for MySQL replication and key monitoring points for operational management.

5. Maintenance and Monitoring of Replication

To operate MySQL replication properly, regular maintenance and monitoring are essential. This section explains commands for checking whether replication is functioning normally and how to handle common errors.

How to Check Replication Status

Use the following commands to check synchronization status between the master and the slave.

Checking Master Status

You can verify the master server’s replication status using the SHOW MASTER STATUS command. This command displays the current binary log file name and position, allowing you to confirm the latest updates that should be delivered to the slave.

SHOW MASTER STATUS;

The output typically includes the following fields:

  • File: The current binary log file name generated by the master
  • Position: The current position within the binary log
  • Binlog_Do_DB and Binlog_Ignore_DB: Databases included/excluded from replication

Checking Slave Status

You can check the slave server’s replication status using the SHOW SLAVE STATUS command. The results include information needed to determine whether the slave is operating correctly.

SHOW SLAVE STATUS\G;

Key fields include:

  • Slave_IO_Running and Slave_SQL_Running: If both are Yes, the slave is running normally.
  • Seconds_Behind_Master: Indicates how far the slave is behind the master (in seconds). Ideally, this value should be 0.

Troubleshooting Replication

Common issues during replication operation include connection errors and data inconsistencies. Below are typical error cases and how to address them.

1. Connection Errors

If Slave_IO_Running is No, it means the slave cannot connect to the master. Try the following:

  • Verify the master hostname or IP address: Make sure the master address is correct.
  • Check firewall settings: Confirm that the required port (usually 3306) is open.

2. Data Inconsistency

If Last_Error contains an error message, a data inconsistency may have occurred between the master and slave. In such cases, stop the slave and apply corrections before restarting replication.

STOP SLAVE;
# Restart after applying fixes
START SLAVE;

3. Reducing Replication Lag

Replication lag can be caused by slave hardware limitations or network issues. If needed, upgrading the slave server configuration can improve performance.

The next section explores replication troubles in more detail and provides further solutions.

6. Common Issues and Their Solutions

During MySQL replication operation, various issues may arise. This section explains common problems and how to resolve them. Detecting issues early and applying the correct fixes helps maintain stable system operation.

1. When Slave_IO_Running Is Stopped

Symptom: If Slave_IO_Running shows No in the output of SHOW SLAVE STATUS, the slave is unable to connect to the master.

Causes and Solutions:

  • Network Issues: If there is a network connectivity problem, the slave cannot access the master. Check firewall settings and confirm that the master is reachable.
  • Incorrect Master Hostname or IP Address: Verify that the hostname or IP address specified in CHANGE MASTER TO is correct.
  • User Privilege Issues: If the replication user on the master does not have sufficient privileges, the connection will fail. Confirm that the correct privileges have been granted using GRANT REPLICATION SLAVE.

2. Data Inconsistency on the Slave

Symptom: If data does not match between the master and slave, the slave may enter an inconsistent state.

Causes and Solutions:

  • Manual Data Correction: Stop the slave, manually fix the problematic transaction, and then restart replication.
    STOP SLAVE; # Fix data if necessary START SLAVE;
  • Resynchronization: If the inconsistency is large-scale, take a full backup from the master and resynchronize the slave.

3. Replication Delay

Symptom: If Seconds_Behind_Master is not 0 in the output of SHOW SLAVE STATUS, the slave is lagging behind the master. Ideally, this value should be as close to 0 as possible.

Causes and Solutions:

  • Slave Hardware Limitations: If the slave server has insufficient resources, it may not keep up. Upgrading hardware can be effective.
  • Query Optimization: If queries received from the master take too long to execute on the slave, replication delay can occur. Adding indexes and optimizing queries can reduce processing time.

4. Replication User Permission Errors

Symptom: If Last_Error shows a permission-related message, the slave may not have sufficient privileges to connect to the master.

Causes and Solutions:

  • Reconfigure User Privileges: Ensure that a user with appropriate privileges exists on the master. Reconfigure if necessary.
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip_address'; FLUSH PRIVILEGES;

5. Binary Log Growth

Symptom: The master’s binary logs may grow excessively, consuming disk space.

Causes and Solutions:

  • Binary Log Rotation: Regularly delete or archive binary logs to prevent excessive growth. By configuring expire_logs_days, you can automatically remove logs older than a specified period.
    SET GLOBAL expire_logs_days = 7; # Delete logs older than 7 days

By understanding these common MySQL replication issues and their solutions, you can ensure smoother operational management. The next section summarizes key points for replication management.

7. Conclusion

MySQL replication is a critical feature for improving data consistency and system reliability. In this article, we covered everything from the basic concepts of MySQL replication to setup procedures, monitoring practices, and troubleshooting methods. Below is a summary of the key points for effective replication management.

Key Takeaways

  1. Choosing the Right Replication Type
  • Asynchronous replication provides faster response times and is ideal for load distribution. However, if reliability is the priority, semi-synchronous replication is more suitable. Choose based on your system requirements.
  1. Effective Use of GTID
  • GTID eliminates the need to specify binary log positions and enables smooth transaction management. It is especially useful in environments with multiple slaves or where failover is critical.
  1. Regular Status Monitoring
  • Use SHOW MASTER STATUS and SHOW SLAVE STATUS to regularly monitor the operational state of both master and slave servers. Prompt action upon detecting anomalies minimizes risks such as data inconsistency or lag.
  1. Mastering Basic Troubleshooting
  • Common replication issues include slave connection errors, data inconsistencies, and lag. Understanding basic solutions for each problem ensures smoother operations.
  1. Binary Log Management
  • Because binary logs can consume significant disk space, it is recommended to configure expire_logs_days for automatic cleanup and perform regular maintenance.

MySQL replication is not a one-time setup task. Continuous monitoring and proper maintenance are essential. By regularly reviewing system status and adjusting configurations when necessary, you can build and maintain a highly reliable database system.

We hope this guide helps you better understand and implement MySQL replication. Wishing you smooth and stable replication operations.