MySQL database master-slave replication and read-write separation

1. Master-slave replication

Master-slave replication: In actual production, in order to solve the single point failure of MySQL and improve the overall service performance of MySQL, master-slave replication is generally used. That is: back up the data and statements in the database.

Master-slave replication three threads

There are three main threads in MySQL's master-slave replication: master (binlog dump thread), slave (I/O thread, SQL thread), one thread in the Master and two threads in the Slave.

Master-slave replication process

The process of master-slave replication:

The master is mainly responsible for writing the updated event type into the binlog file of the master library in the binlog format when there is data update in the master library. The Master will create a log dump thread to notify the Slave that there is data update in the main database.

The I/O thread is created in the Slave and is used to request the Master. The Master returns the name of the binlog, the location of the current data update, and a copy of the binlog file location. Then, the binlog is saved in the relay log, which also records information about data updates.

The SQL thread is also created in the Slave. When the Slave detects that the relay log is updated, it will synchronize the updated content to the Slave database, ensuring the synchronization of the master and slave data.

Master-slave replication strategy

Master-slave replication strategy:

Synchronization strategy: The Master will wait for all Slaves to respond before committing, which will seriously affect the performance of master-slave synchronization.

Semi-synchronous strategy: The Master will wait for at least one Slave to respond before committing.

Asynchronous strategy: The Master can submit without waiting for the Slave's response.

Delay strategy: The Slave must lag behind the time specified by the Master.
There are different strategies for different business needs, but generally, eventual consistency is adopted, and strong consistency is not required, as strong consistency will seriously affect performance.

High latency in master-slave replication

Reasons for high latency in master-slave replication

The master server has high concurrency, resulting in a large number of transactions

Network latency

Caused by master and slave hardware devices (CPU main frequency, memory io, hard disk io)

It is not synchronous replication, but asynchronous replication.

Solution to high latency of master-slave replication

Optimize Mysql parameters from the database. For example, increase innodb_buffer_pool_size to allow more operations to be completed in mysql memory and reduce disk operations

Use high-performance hosts (high-performance CPU, large memory) from the database, avoid using virtual cloud hosts, use physical hosts to improve I/O throughput

Use SSD disks from the library

Network optimization to avoid synchronization across computer rooms

2. Read-write separation

Read-write separation concept

Read-write separation: Read-write separation allows the primary database to handle transactional addition, deletion, and modification operations (INSERT, UPDATE, DELETE), while the secondary database handles query (SELECT) operations. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.

Reasons and scenarios for read-write separation

Reasons for read-write separation: The amount of reads and writes is large. In order to improve the database read-write performance, reads and writes are separated. If there are more reads than writes in multiple computer rooms, and based on data consistency considerations, only one master database stores all data writes, and the local slave database provides reads to reduce the latency caused by direct reads between multiple computer rooms.

Read-write separation usage scenario: It is considered when the program uses the database more frequently, but updates are less and queries are more. By using database master-slave synchronization and read-write separation, database pressure can be shared and performance can be improved.

Summarize

This concludes this article about MySQL database master-slave replication and read-write separation. For more information about MySQL master-slave replication and read-write separation, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!