The implementation principle of Mysql master-slave synchronization

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 scale the database load capacity.
• Fault tolerance and high availability. Failover/High Availability
• Data backup.

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

First, let's understand the master-slave architecture.

As shown below:

Whether it is delete, update, insert, or creating functions and stored procedures, all operations are performed on the master. When the master has operations, the slave will quickly receive these operations and synchronize them.

But how is this mechanism implemented?

On the master machine, master-slave synchronization events are written to a special log file (binary-log); on the slave machine, the slave reads the master-slave synchronization events and makes corresponding changes on the slave library based on the changes in the read events.

In this way, master-slave synchronization is achieved!

Let’s take a closer look below.

3.1 What are the master-slave synchronization events?

It is mentioned above:

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

There are three forms of master-slave synchronization events: statement, row, and mixed.

1. statement: The SQL statements for database operations will be written into 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 row format binlog.

3.2 Operations on the master machine

When the data on the master changes, the event (insert, update, delete) changes will be written to the binlog in sequence.

binlog dump thread

When the slave connects to the master, the master machine will start 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 Operations on the slave machine

When master-slave synchronization is turned on, two 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 sends 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 the master and slave?

You can view it using the SHOW PROCESSLIST command.

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, let me summarize it with a picture

5. About actual combat

For actual combat, please refer to my other article: MySQL master-slave synchronization actual combat

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.