Why do master-slave replication?

After experiencing a MySQL master-slave delay, I started thinking about what master-slave replication is? How is it achieved? How does it work? So I started to look up information and articles, and now I summarize what I understood here to deepen my impression.

Why do we need to do master-slave replication?

1. In a system with complex business, there is a situation where a SQL statement needs to lock the table, causing the read service to be temporarily unavailable, which will greatly affect the running business. Use master-slave replication. Let the main library be responsible for writing and the slave library be responsible for reading. In this way, even if the main library locks the table, the normal operation of the business can be ensured by reading from the slave library.

2. Hot backup of data

3. Architecture expansion. The business volume is getting larger and larger, and the frequency of I/O access is too high, which cannot be satisfied by a single machine. At this time, multi-database storage is used to reduce the frequency of disk I/O access and improve the I/O performance of a single machine.

What is the principle of mysql master-slave replication?

binlog: binary log, the binary file that saves all update event logs in the main library.

The basis of master-slave replication is that the master database records all changes in the database to the binlog. Binlog is a file that saves all modifications to the database structure or content from the moment the database server is started.

Mysql master-slave replication is an asynchronous replication process. The master library sends update events to the slave library, and the slave library reads the update records and executes the update records so that the contents of the slave library are consistent with the master library.

In the main library, as long as an update event occurs, it will be written to the binlog in sequence, and then pushed to the slave library as the data source for replication from the slave library.

binlog output thread. Whenever a slave library connects to the main library, the main library will create a thread and send the binlog content to the slave library.
For each SQL event that is about to be sent to the slave library, the binlog output thread will lock it. Once the event has been read by the thread, the lock will be released, even when the event is completely sent to the slave library, the lock will be released.

In the slave library, when copying starts, the slave library will create two threads for processing:

The slave library I/O thread. When the START SLAVE statement starts executing from the slave library, the slave library creates an I/O thread, which connects to the main library and requests the main library to send the update records in the binlog to the slave library.
The slave library I/O thread reads the updates sent by the binlog output thread of the main library and copies these updates to local files, including relay log files.

SQL thread from the database. Create a SQL thread from the library. This thread reads the update events written to the relay log by the I/O thread of the library and executes them.

It can be known that for each master-slave replication connection, there are three threads. A main library with multiple slave libraries creates a binlog output thread for each slave library connected to the main library. Each slave library has its own I/O thread and SQL thread.

By creating two independent threads, the slave library separates the reading and writing of the slave library during replication. Therefore, even if the thread responsible for execution runs slower, the thread responsible for reading the update statement does not become slower. For example, if the slave library has not been running for a while, when it is started here, although its SQL thread executes slowly, its I/O thread can quickly read all binlog contents from the main library. In this way, even if the slave library stops running before the SQL thread finishes executing all the read statements, the I/O thread has at least fully read all the contents and safely backed up them in the local relay log of the slave library. , ready to execute statements the next time the slave library is started.

View the status of master-slave replication

When master-slave replication is in progress, if you want to check the running status of the two threads in the slave library, you can execute "show slave status\" in the slave library G" statement, the following fields can give you the information you want:

Master_Log_File — 上一个从主库拷贝过来的binlog文件
Read_Master_Log_Pos — 主库的binlog文件被拷贝到从库的relay log中的位置
Relay_Master_Log_File — SQL线程当前处理中的relay log文件
Exec_Master_Log_Pos — 当前binlog文件正在被执行的语句的位置

The entire master-slave replication process can be understood through the following diagram:

• Step 1: The update events (update, insert, delete) of the main database db are written to the binlog

• Step 2: Initiate a connection from the database, connect To the main library

• Step 3: At this time, the main library creates a binlog dump thread and sends the binlog content to the slave library

• Step 4 : After starting from the library, create an I/O thread, read the binlog content passed from the main library and write it to the relay log

• Step 5: A SQL thread will also be created. Read the content from the relay log, execute the read update event starting from the Exec_Master_Log_Pos position, and write the updated content to the slave's db

注：上面的解释是解释每一步做了什么，整个mysql主从复制是异步的，不是按照上面的步骤执行的。

The above is the detailed content of Why do master-slave replication?. For more information, please follow other related articles on the PHP Chinese website!