How does MySQL achieve master-slave synchronization?

Master-slave synchronization, also called master-slave replication, is a high-availability solution provided by MySQL that ensures master-slave data consistency.

In a production environment, there will be many uncontrollable factors, such as database service failure. To ensure high availability of the application, the database must also be highly available.

Therefore, in production environments, master-slave synchronization is used. When the application is small in scale, one master and one backup are generally used.

In addition to the ability to quickly switch to the standby database when the database service fails and avoid application unavailability, the use of master-slave synchronization has the following benefits:

Improve the read concurrency of the database. Most applications require more reads than writes. Use a master-slave synchronization solution. When the scale of use increases, you can expand the slave database to improve the read capability.

Backup, master-slave synchronization can obtain a real-time and complete backup database.

Quick recovery: When an error occurs in the primary database (such as accidental deletion of a table), data can be quickly restored through the standby database. For large-scale applications with low tolerance for data recovery speed, a backup database can be configured with a data snapshot half an hour apart from the primary database. When a table is accidentally deleted from the primary database, it can be quickly restored through the backup database and binlog, with a maximum wait of half an hour.

Now that we have discussed what master-slave synchronization is and its benefits, let us now understand how master-slave synchronization is achieved.

Implementation principle of master-slave synchronization

Let's first understand the principle of master-slave synchronization. The following uses an update statement to introduce how the master and slave databases are synchronized.

The figure above is a complete flow chart of an update statement executed on node A and then synchronized to node B. The specific steps are:

1. The master database receives an update statement sent by the client, executes the internal transaction logic, and writes the binlog at the same time.
2. The standby database uses the change master command to set the IP, port, user name, and password of the master database, as well as the position from which to start requesting binlog. This location contains the file name and offset.
3. Execute the start slave command on the slave database to start two threads, io_thread and sql_thread. The io_thread is responsible for connecting to the host.
4. After the master database verifies the username and password, it reads the binlog according to the received location and sends it to the slave database.
5. After receiving the binlog, the standby database writes it to a local file (relay log, transfer file).
6. The standby database reads the transfer file, parses the command, and then executes it.
   

The working principle of master-slave synchronization is actually a full backup plus the restoration of the binary log backup. The difference is that the restore operation of this binary log is basically real-time.

The standby database uses two threads to achieve synchronization:

• One is the I/O thread, which is responsible for reading the binary log of the main library and saving it as a relay log.
• One is the SQL thread, which is responsible for executing the relay log.

From the above process, we can see that the key to master-slave synchronization is binlog

Two common active/standby switching processes

MS structure

In the MS structure, there are two nodes, one serving as the primary database and the other as the backup database. The two nodes are not allowed to exchange roles.

In state 1, the client's reads and writes directly access node A, and node B is the backup database of A. It just synchronizes all updates of A and executes them locally. This keeps the data on nodes B and A the same.

When switching is required, switch to state 2. At this time, the client reads and writes to node B, and node A is the backup database of B.

Double M structure

Dual M structure, two nodes, one as the primary database and one as the backup database, allowing the two nodes to exchange roles.

Comparing with the previous MS structure diagram, we can find that the only difference between the dual M structure and the MS structure is that there is one more line, that is, nodes A and B are always in a master-slave relationship with each other. This way, there is no need to modify the master-slave relationship during switching.

The Circular Copy Problem of Double M Structure

In actual production use, the double M structure is used in most cases. However, the double M structure still has a problem that needs to be solved.

When the business logic is updated on node A, a binlog is generated and synchronized to node B. After node B is synchronized, binlog is also generated. (log_slave_updates is set to on, indicating that the standby database will also generate binlogs).

When node A is also the backup database of node B, the binlog of node B will also be sent to node A, causing circular replication.

Solution:

1. Set the server-id of the node. It must be different. Otherwise, it is not allowed to set it as a master-slave structure.
2. When the backup database receives the binlog and replays it, it will record the same server-id as the original record, that is, it belongs to the person who generated it.
3. When receiving binlog, each node will determine the server-id and discard it if it is its own.

The process after solution:

1. The business logic executes updates on node A, and generates a binlog with the server-id of node A.
2. After node B receives the binlog sent by node A and completes the execution, it will generate a binlog with the server-id of node A.
3. After node A receives the binlog and finds that it is its own, it discards it. The endless loop is broken here.

The above is the details of how MySQL achieves master-slave synchronization. For more information about MySQL master-slave synchronization, please pay attention to other related articles on 123WORDPRESS.COM!