Detailed introduction to the implementation of Mysql master-slave synchronization principle (pictures and texts)

This article mainly introduces MysqlThe implementation principle of master-slave synchronization. The editor thinks it is quite good. Now I will share it with you and give you a reference. Let’s follow the editor and take a look

1. What is mysql master-slave synchronization?

When the data in the master database changes, the changes will be synchronized to the slave database in real time.

2. What are the benefits of master-slave synchronization?

• # Horizontally expand the load capacity of the database.

• Fault tolerance, high availability. Failover/High Availability

• Data backup.

#3. What is the principle of master-slave synchronization?

First let’s understand the architecture of master-slave.

As shown below:

Whether it is delete, update, insert, or create function,Stored procedure, all operations are on the master. When the master has operations, the slave will quickly receive these operations and perform synchronization.

But how is this mechanism implemented?

On the master machine, the master-slave synchronization events will be written to a special log file (binary-log); on the slave machine, the slave reads the master-slave synchronization events and reads them based on the If the event changes, make corresponding changes in the slave library.

In this way, master-slave synchronization is achieved!

Let’s learn more about it in detail.

3.1 What are the master-slave synchronization events?

As mentioned above:

On the master machine, master-slave synchronization events will be written To a special log file (binary-log);

Master-slave synchronization events come in three forms: statement, row, and mixed.

1. statement: SQL statements for database operations will be written to the binlog.

2. row: Each data change will be written to the binlog.

3. mixed: a mixture of statement and row. Mysql decides when to write statement format and when to write binlog in row format.

3.2 Operation on the master machine

When the data on the master changes, the event (insert, update, delete) will change according to Write to binlog sequentially.

binlog dump thread

When the slave is connected to the master, the master machine will open the binlog dump thread for the slave. When the master's binlog changes, the binlog dump thread will notify the slave and send the corresponding binlog content to the slave.

3.3 Operation on the slave machine

When master-slave synchronization is turned on, 2 threads will be created on the slave.

• I/O thread. This thread is connected to the master machine, and the binlog dump thread on the master machine will send the contents of the binlog to the I/O thread. After receiving the binlog content, the I/O thread writes the content to the local relay log.

• SQL thread. This thread reads the relay log written by the I/O thread. And perform corresponding operations on the slave database according to the contents of the relay log.

3.4 How to view the above threads on master and slave?

Use the SHOW PROCESSLIST command to view.

As shown in the figure, view the binlog dump thread on the master machine.

As shown in the figure, check the I/O and SQL threads on the slave machine.

#4. Having said so much, I can summarize it in one picture

The above is the detailed content of Detailed introduction to the implementation of Mysql master-slave synchronization principle (pictures and texts). For more information, please follow other related articles on the PHP Chinese website!