SoFunction
Updated on 2025-05-14

Detailed explanation of the principles and application scenarios of MySQL replication and master-slave architecture (Master-Slave)

MySQL replication and master-slave architecture (Master-Slave)

MySQL replication and master-slave architecture are important means of high database availability and load balancing. By copying data to multiple slave servers, redundant data backup can be achieved, and query pressure can be shared, improving the overall performance and fault tolerance of the system. This article will introduce in detail the basic principles, configuration methods, replication types and practical application scenarios in the master-slave architecture.

1. MySQL Replication Overview

1.1 The basic principles of copying

MySQL replication refers to the automatic transmission of data changes on the master server (Master) to one or more slave servers (Slaves). The core process is as follows:

  • Binlog log record: Every modification of the data by the master server will be recorded in the binary log.
  • Log transfer: The slave server connects to the master server through an I/O thread and obtains the contents of the binary log.
  • SQL thread execution: The slave server's SQL thread parses and performs operations in the binary log to keep the slave database data consistent with the master database.

1.2 Copy Advantages

  • High availability: When a master library fails, you can continue to provide services by switching to the slave library.
  • Load balancing: Query requests can be distributed to multiple slave libraries, reducing the pressure on the main library.
  • Data backup: Remote backup and disaster recovery of data through replication.

2. Master-slave replication configuration

2.1 Master Server Configuration

On the main server, the binary logging function needs to be enabled and a unique server-id is set. The example configuration is as follows (part of the contents in the file):

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
  • server-id: Each MySQL instance must have a unique identity.
  • log-bin: Turn on binary logging.
  • binlog_format: It is usually recommended to use ROW format, which can record data changes more accurately.

2.2 Configuring from the server

The server also needs to set a unique server-id and configure the relay log parameters. Example:

[mysqld]
server-id = 2
relay-log = mysql-relay-bin

In addition, you need to specify the connection information of the master server and tell the slave server which log location to start copying:

CHANGE MASTER TO
  MASTER_HOST = 'master_ip',
  MASTER_USER = 'replication_user',
  MASTER_PASSWORD = 'replication_pass',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 107;

After execution, start the copy process:

START SLAVE;

useSHOW SLAVE STATUS\GYou can check the replication status to ensureSlave_IO_RunningandSlave_SQL_RunningAll areYes。

3. Copy Types and Features

3.1 Asynchronous replication

  • How it works: After the main library submits the transaction, it does not wait for the slave library to confirm and returns to the client directly; the slave library receives and executes changes asynchronously with a certain delay.
  • Pros and cons: Advantages: Small performance overhead and low write operation delay. Disadvantages: There is a risk of data delay, which may lead to temporary inconsistency between master and slave data.

3.2 Semi-synchronous replication

  • How it works: The main library waits for at least one slave library to confirm the receipt of the binary log when submitting the transaction, but does not require it to complete execution.

Pros and cons：

  • Advantages: Reduce the risk of data loss and is more stable than asynchronous replication.
  • Disadvantages: slightly affected in performance, especially when the slave network is delayed by high.

3.3 Multi-source replication

  • How it works: The slave server can copy data from multiple master servers at the same time, suitable for data integration and distributed environments.
  • Application scenarios: Data summary across data centers and data from multiple business systems.

4. Application of master-slave architecture in practice

4.1 Read and write separation

  • Strategy: Concentrate write operations in the master library and spread read operations into multiple slave libraries. Dynamic routing can be implemented at the application layer or using middleware.
  • Advantages: Effectively reduce the pressure of the main library and improve the overall query performance.

4.2 High availability and failover

  • Strategy: When the main library fails, upgrade one of the slave libraries to the new main library by automatically or manually switching.
  • tool: It can combine automated failover tools such as MHA (MySQL High Availability), Orchestrator and other automation failover tools to improve system reliability.

4.3 Data backup and disaster recovery

  • Strategy: Use real-time data updates of slave libraries to perform regular backups, and deploy slave libraries in remote locations to achieve disaster recovery.
  • Advantages: Even if the master library has hardware failure or data corruption, the slave library can serve as a data source for rapid recovery.

5. Precautions and optimization suggestions

  • Network stability: Ensure the stability and low latency of the network between the master and slave, and reduce the risk of replication delay and disconnection.
  • Regular monitoring:useSHOW SLAVE STATUS\Gand third-party monitoring tools to promptly detect replication errors or delay problems.
  • Data consistency: In high concurrent writing scenarios, pay attention to the data inconsistency problems caused by the separation of read and write by master-slave delay, and use semi-synchronous replication or other consistency measures if necessary.
  • Security configuration: Set minimum permissions for replication users, and use SSL encrypted replication channels to prevent data transmission from being stolen or tampered with.

6. Summary

The MySQL master-slave replication architecture enables high availability, read-write separation and data backup by automatically synchronizing data. Whether it is pursuing performance in asynchronous replication or ensuring data security in semi-synchronous replication, trade-offs and configurations are required based on specific business needs. Combining suitable monitoring and failover solutions, the master-slave architecture can provide stable and reliable data support for large-scale distributed systems. Hopefully this article provides you with comprehensive reference and practical guidance when designing and optimizing your MySQL replication architecture.

This is the article about the detailed explanation of the principles and application scenarios of MySQL replication and master-slave architecture (Master-Slave) in detail. For more related contents of MySQL replication and master-slave architecture, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!