The World of MySQL & MariaDB

MySQL Replication Guide: Setup, Types, and Troubleshooting for High Availability

Advanced Topics
目次

1. What is MySQL Replication? Overview and Use Cases

MySQL Replication is a feature that synchronizes a copy of a database to another server in real time. This enhances database redundancy and performance. Below, we explain in detail where MySQL Replication is used and how it works.

Overview of MySQL Replication

MySQL Replication consists of a master server and one or more slave servers, sharing database content across multiple servers. Specifically, the master server records updates in the binary log, and the slave server reads and applies those updates to stay in sync. This allows services to continue even if the master server fails, by switching to a slave server.

Use Cases of MySQL Replication

MySQL Replication is widely used in the following scenarios:

  • High Availability: Minimize downtime by switching to a slave server in the event of a failure.
  • Load Balancing: Distribute read-only queries to slave servers to reduce the load on the master.
  • Data Protection and Backup: Since replication copies data in real time, it can also serve as a backup solution.

Types of Replication

MySQL Replication has the following types, depending on how data is synchronized:

  • Asynchronous Replication: The master does not wait for the slave to confirm receipt of updates, allowing faster responses. However, some data may not reach the slave if a failure occurs.
  • Semi-Synchronous Replication: The master waits until at least one slave confirms receipt of data before proceeding. This provides higher reliability but may be slightly slower.

In the next section, we will explain the basic concepts of MySQL Replication, including binary logs and GTIDs.

2. Basic Concepts of MySQL Replication

To understand MySQL Replication, it is essential to know the role of the binary log and GTID (Global Transaction ID), both of which ensure accurate data replication.

Roles of Master and Slave

In MySQL Replication, the master server and slave server have distinct roles. The master records updates in the binary log and distributes them to slaves. The slave server applies these logs to update its data, maintaining the same state as the master.

Binary Log and Relay Log

MySQL Replication relies on two key logs:

  1. Binary Log
  • The binary log records data changes (INSERT, UPDATE, DELETE, etc.) on the master server. This ensures the slave can maintain the same state as the master.
  1. Relay Log
  • The relay log is stored on the slave server, containing the binary log received from the master. The slave’s SQL thread executes this relay log sequentially to apply changes.

What is GTID (Global Transaction ID)?

GTID assigns a unique ID to each transaction, ensuring consistency across multiple slaves. With GTID, binary log position tracking is unnecessary, and only unapplied transactions are automatically applied, simplifying management.

Advantages of GTID

  • Unique Identification: Each transaction has a unique GTID, making it clear which transactions have been applied.
  • Easy Recovery: After a restart, only unapplied transactions are re-applied automatically.
  • Efficient Management: GTID simplifies replication management in large environments with multiple slaves.

To enable GTID, set gtid_mode=ON and enforce_gtid_consistency=ON on both master and slave servers. This activates GTID-based replication.

In the next section, we will cover the step-by-step setup of MySQL Replication.

3. Steps to Set Up MySQL Replication

This section explains how to configure MySQL Replication step by step. By following these instructions, you can set up a basic master-slave environment and achieve real-time data synchronization.

Master Server Configuration

First, edit the master server configuration file (usually my.cnf or my.ini) to enable binary logging and assign a server ID.

  1. Edit the Configuration File
  • Add the following settings to the [mysqld] section, and set a unique server ID (e.g., 1).
[mysqld]
server-id=1
log-bin=mysql-bin
  • server-id must be unique per server. log-bin enables binary logging.
  1. Create a Replication User
  • Create a replication user on the master server and grant the required privileges.
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
  • This user is required for the slave to access the master’s data.
  1. Check Master Status
  • Check the current binary log file and position, which will be needed for slave configuration.
SHOW MASTER STATUS;
  • The File and Position values shown will be used when configuring the slave.

Slave Server Configuration

Next, edit the slave server configuration file to set a unique server ID and specify master information.

  1. Edit the Configuration File
  • Assign a unique server-id (e.g., 2) to the slave server. The server ID must differ from the master’s.
[mysqld]
server-id=2
  • It is also common to enable read_only=ON to prevent unintended writes on the slave.
  1. Configure Master Information on the Slave
  • Run the following command on the slave server, specifying the master host, user, binary log file, and log position.
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123;
  • Use the MASTER_LOG_FILE and MASTER_LOG_POS values obtained from the master’s SHOW MASTER STATUS output.
  1. Start Replication
  • Execute the following command on the slave server to start replication.
START SLAVE;

Check Replication Status

Verify whether replication between master and slave is functioning correctly.

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

In the next section, we will explore advanced configuration options for MySQL Replication, including differences between asynchronous and semi-synchronous replication and GTID-based setups.

4. Replication Types and Applications

MySQL Replication comes in two main types depending on the synchronization method: asynchronous replication and semi-synchronous replication. Understanding the differences and when to use each helps optimize performance and reliability. This section also covers the advantages of using GTID (Global Transaction Identifier) in replication setups.

Differences Between Asynchronous and Semi-Synchronous Replication

1. Asynchronous Replication

In asynchronous replication, the master server immediately responds to the client once a transaction is completed, without waiting for the slave to apply the update. This ensures excellent responsiveness, making it suitable for load-balancing systems. However, if a failure occurs, any transactions not yet applied to the slave may be lost.

2. Semi-Synchronous Replication

In semi-synchronous replication, the master server waits until at least one slave has received the data before responding to the client. This improves data consistency but increases transaction response time since the master must wait for confirmation. Semi-synchronous replication is ideal for environments where data consistency and reliability are prioritized.

Replication with GTID

GTID (Global Transaction Identifier) assigns a unique ID to each transaction, ensuring consistency between master and slave servers. Enabling GTID simplifies replication management compared to traditional binary log position-based replication.

Advantages of GTID

  • Improved Data Consistency: GTID allows the slave to automatically identify unapplied transactions, ensuring consistency.
  • Simplified Management: GTID eliminates the need to manually specify binary log positions, making failover and recovery operations easier.

GTID Replication Setup

To enable GTID, add the following options to the master and slave configuration files.

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

Once GTID is enabled, setting up replication on the slave with the CHANGE MASTER TO command will automatically handle GTID-based replication.

In the next section, we will explain MySQL Replication maintenance and monitoring practices.

5. Replication Maintenance and Monitoring

To operate MySQL Replication effectively, regular maintenance and monitoring are essential. This section explains how to verify replication status and how to handle common errors.

How to Check Replication Status

Use the following commands to monitor synchronization between master and slave servers.

Check Master Status

On the master server, run SHOW MASTER STATUS to view the current binary log file and position. This shows the latest updates to be sent to slaves.

SHOW MASTER STATUS;

Key fields include:

  • File: Current binary log file name
  • Position: Current position within the binary log
  • Binlog_Do_DB and Binlog_Ignore_DB: Databases included or excluded from replication

Check Slave Status

On the slave server, run SHOW SLAVE STATUS to check replication health.

SHOW SLAVE STATUSG;

Important fields include:

  • Slave_IO_Running and Slave_SQL_Running: Both should be Yes if replication is functioning normally.
  • Seconds_Behind_Master: Indicates how far behind the slave is in seconds. Ideally, this should be 0.

Troubleshooting Replication

Common issues in replication include connection errors and data inconsistencies. Below are typical error cases and solutions.

1. Connection Errors

If Slave_IO_Running is No, the slave cannot connect to the master. Possible solutions include:

  • Verify Master Host/IP: Ensure the correct address is set.
  • Firewall Settings: Confirm that port 3306 is open and accessible.

2. Data Inconsistency

If errors appear in Last_Error, the master and slave may have inconsistent data. To resolve:

STOP SLAVE;
# Fix the data manually
START SLAVE;

For major inconsistencies, resynchronize by restoring a full backup from the master.

3. Replication Lag

Replication delay may result from hardware limitations or network issues on the slave. Upgrading hardware or optimizing queries can improve performance.

The next section explains common replication problems and their detailed solutions.

6. Common Replication Issues and Solutions

Various issues may arise during MySQL Replication. This section details frequent problems and how to resolve them.

1. Slave_IO_Running is Stopped

Issue: If Slave_IO_Running is No, the slave cannot connect to the master.

Causes and Solutions:

  • Network Issues: Check firewall and connectivity to the master.
  • Incorrect Master Host/IP: Verify CHANGE MASTER TO configuration.
  • User Privileges: Ensure the replication user has correct permissions with GRANT REPLICATION SLAVE.

2. Slave Data Inconsistency

Issue: Data differs between master and slave.

Causes and Solutions:

  • Manual Fix: Stop the slave, correct the inconsistent data, then restart replication.
    STOP SLAVE; # Fix data START SLAVE;
  • Full Resync: For severe mismatches, re-import a backup from the master.

3. Replication Delay

Issue: Seconds_Behind_Master is greater than 0, meaning the slave is lagging behind.

Causes and Solutions:

  • Hardware Limitations: Upgrade the slave server’s specs.
  • Query Optimization: Improve indexes and queries to reduce processing time on the slave.

4. Replication User Privilege Errors

Issue: Errors in Last_Error indicate insufficient privileges.

Solution:

  • Grant Correct Privileges: Ensure proper replication rights.
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip'; FLUSH PRIVILEGES;

5. Binary Log Growth

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

Solution:

  • Log Rotation: Use expire_logs_days to auto-purge old logs.
    SET GLOBAL expire_logs_days = 7; # Purge logs older than 7 days

By understanding and preparing for these common problems, administrators can maintain stable replication operations.

7. Conclusion

MySQL Replication is an essential feature for ensuring data consistency and system reliability. This article covered the basics of replication, setup procedures, monitoring, and troubleshooting. Below is a recap of key points.

Key Takeaways

  1. Choose the Right Replication Type
  • Asynchronous replication offers speed and load balancing, while semi-synchronous replication provides stronger reliability. Choose based on system requirements.
  1. Leverage GTID
  • GTID simplifies replication by removing the need to specify binary log positions, making it valuable for large or critical environments.
  1. Monitor Status Regularly
  • Use SHOW MASTER STATUS and SHOW SLAVE STATUS frequently to detect anomalies early and minimize risks.
  1. Master Troubleshooting Skills
  • Be familiar with handling replication-specific issues such as connection errors, inconsistencies, and lag.
  1. Manage Binary Logs
  • Prevent disk usage issues by configuring expire_logs_days and rotating logs regularly.

MySQL Replication requires continuous monitoring and maintenance, not just initial setup. By checking status regularly and adjusting configurations as needed, you can build and maintain a highly reliable database system.

We hope this guide helps you understand and implement MySQL Replication effectively, ensuring smooth and stable operations.