What is the principle of master-slave replication in MySQL

Preface

In an actual production environment, if the reading and writing of the MySQL database are all operated on a database server, no matter in terms of security, high availability, or high concurrency, it will be If it cannot meet actual needs, it is generally necessary to synchronize data through master-slave replication, and then improve the concurrent load capacity of the database through read-write separation.

1. Master-slave replication concept

Master-slave replication is the basic technology provided by MySQL. The process of master-slave replication: binlog binary log (except for queries, other change-related operations will be recorded in binlog), relay log and three threads (one thread of master and two threads of slave).

The main library (master) provides external data addition, deletion, modification and query services. Modifications involving data in the main library will be written in the binlog The slave library (slave) is used for data synchronization And data backup, use a dedicated thread to synchronize the modifications related to the main library and data, permissions, and table structure to the slave library from the binlog in the main library. This is equivalent to all modifications made on the main library being passed. The master-slave replication mechanism is embodied in the slave database.

Benefits of master-slave replication: Data backup, we can even make a hot backup for it, that is, through the MySQL middleware mycat, capacity can be achieved Disaster , disaster recovery also reflects high availability.
Disaster recovery: If the main library hangs up, the middleware agent mycat automatically maps service requests to the slave library, and the slave library continues to provide services to the outside world, reflecting high availability (back-end services Certain exceptions are allowed to occur, but the back-end architecture service must be fault-tolerant, handle these abnormal errors, and re-provide normal services to the outside world)

2. The concept of read-write separation

Read-write separation is implemented based on master-slave replication. In an actual application environment, there must be a lot of read operations. Just like when we buy something on an e-commerce platform, we may only buy one or two after looking at 100 items. Therefore, there are always many more read operations than write update operations. Therefore, our read-write separation configuration based on master-slave replication means that a master database is dedicated to modifying data. When writing, it is written exclusively on the master database. The master database synchronizes data changes to the slave through master-slave replication through binlog. library, then other client query requests will eventually be mapped to the slave library, and we have one main library with two or three slave libraries. The main library is specially used for data updates (write operations), and the slave library The library is specially used for read operations. This way, the pressure of reading and writing can be well distributed. It does not need to be concentrated on the main library. It greatly improves the concurrent processing capabilities of back-end services. In addition, it has high availability capacity. In case of disaster, when the main database fails, the designated slave database can be turned into the main database.

The binlog in the above picture will be written to the binlog even if we do not have master-slave replication, but the master-slave replication is implemented through the binlog.

3. Main library and slave library

1. Main library

The main library master server creates a binlog dump thread, will The binary log content is sent to the slave server

2. The slave library

• has a dedicated I/O threadfor reading To receive the content sent by the main library, it will receive the binlog content sent from the main library and write it into a relay log. The relay log is equivalent to making a buffer, so that the master There is no need to wait for slave execution to complete before sending the next event . Instead of reading the binlog content in the main library and executing it directly, the disadvantage of direct execution is that the main library may have a lot of binlog content, and the execution of the binlog content received from the slave library may be very slow, resulting in updates from the slave library. The gap between the data and the main database will become wider and wider. Data replication may lag behind.

• The slave library will also start a SQL thread, which specifically reads corresponding operations from the relay log,all SQL Execute them all, thus realizing the synchronization of slave library content and master library content

4. Master-slave replication process

Master-slave replication process: Two logs (binlog binary log and relay log log) and three threads (one thread of master and two threads of slave).

• The update operation of the main library is written to the binlog binary log (the main library needs to turn on the binlog switch)

• master The server creates a binlog dump thread and sends the binary log content to the slave server

• The slave machine executes the START SLAVE command to create an IO thread on the slave server, which receives the master's binary log and copies it to its relay log (in memory , fast reading and writing). First, the slave starts a working thread (I/O thread). The I/O thread will actively connect to the master. Then the main library will open the dump thread. The dump thread reads events from the master's binlog and sends them to the slave. I/O thread, if the dump thread has caught up with the master (the dump thread on the main library has finished sending the contents of the binlog, and the binlog on the main library has not generated more content), the dump thread will sleep and wait for the binlog to generate new The event, the event received by the slave's I/O thread is written to the relay log

• slave'sSQL threadThe last step of processing the process, the SQL thread starts from relay Read events from the log and execute the events to update the slave's data so that it is synchronized with the master's data. As long as the SQL thread is consistent with the I/O thread, the relay log will usually be located in the os cache, so the overhead of the relay log is very small

5. Master-slave replication effect display

Centos on linux is used as the main library and mysql server on win10 is used as the slave library to demonstrate:

Master-slave replication is one-way synchronization , the master's changes are synchronized to the slave. When setting up master-slave replication, the data between the master and slave databases may be different. Once the configuration is complete, all changes to the master database will be synchronized to the slave database.

master creates mytest database:

Check slave and find that mytest has been synchronized:

The master created the user table, and the slave also synchronized the user table:

Now the MySQL (master) on the Linux side deletes the mytest library

At this time, the slave’s mytest no longer exists

show processlistYou can view the working threads in the current environment of the master

View the working threads in the current environment of the slave

The above is the detailed content of What is the principle of master-slave replication in MySQL. For more information, please follow other related articles on the PHP Chinese website!