Implementation principle of Mysql master-slave synchronization and mysql master-slave Synchronization

Implementation principle of Mysql master-slave synchronization and mysql master-slave Synchronization

1. What is mysql master-slave synchronization?

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

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

• Horizontally expand the load capacity of the database.
• Fault Tolerance and high availability. Failover (Failover failure)/High Availability
• Data backup.

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

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

For example:

No matter whether it is delete, update, insert, or create a function or stored procedure, all operations are performed on the master. When the master has operations, slave will quickly receive these operations for synchronization.

But how is this mechanism implemented?

On the master machine, the master-slave synchronization event is written to a special log file (binary-log). On the Server Load balancer machine, the Server Load balancer reads the master-slave synchronization event and varies according to the read event, make changes to the slave library.

In this way, the master-slave synchronization is realized!

The following is a detailed description.

3.1 What are the master-slave synchronization events?

As mentioned above:

On the master machine, the master-slave synchronization event is written to a special log file (binary-log );

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

1. Statement: writes SQL statements for database operations to binlog.
2. Row: writes changes in each piece of data to the binlog.
3. Mixed: the combination of statement and row. Mysql decides when to write the statement format and when to write the binlog in the row format.

3.2 operations on the master machine

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

Binlog dump thread

When the slave is connected to the master, the master machine will enable the binlog dump thread for the slave. When the binlog of the master node changes, the binlog dump thread notifies the slave and sends the corresponding binlog content to the slave.

3.3 operations on the slave machine

When the master-slave synchronization is enabled, two threads are created on the slave.

• I/O thread. This thread is connected to the master machine. The binlog dump thread on the master machine sends the binlog content to this I/O thread. The I/O thread receives the binlog content and then writes the content to the local relay log.
• SQL thread. This thread reads the relay log written by the I/O thread. Perform operations on the slave database based on the relay log Content.

3.4 how can I view the above threads on the master and slave?

Run the show processlist command.

To view the binlog dump thread on the master machine.

To view the I/O and SQL threads on the slave machine.

4. I have talked so much about it.

5. Practice

For more information, see my other article: Mysql master-slave synchronization practice.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.