Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to achieve semi-synchronous semi-sync replication in MySQL. It is very detailed and has a certain reference value. Interested friends must read it!

A brief introduction to semi-synchronization

After executing the transaction submitted by the client, the MASTER node does not immediately return the result to the client, but waits for at least one SLAVE node to receive and write to the relay log before returning to the client.

Compared with asynchronous replication, semi-synchronous not only improves the security of data, but also causes a certain degree of delay, which is at least one TCP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

MySQL has supported semi-synchronous replication since 5.5.2. semi-synchronous replication has been improved in version 5.7.2; the original semi-synchronous strategy is AFTER_COMMIT and the improved strategy is AFTER_SYNC. The difference between the two is that the SLAVE node ACK responds to MASTER at different times.

2. Introduction of the two modes

Introduction to AFTER_COMMIT mode

MASTER writes each transaction to the binary log and saves it by brushing the disk, sends the transaction to SLAVE, then commits the transaction to the storage engine for processing and commits, and then waits for SLAVE to return a confirmation message. After receiving the confirmation message, MASTER returns the result to the client, and then the current client can continue to work.

Introduction to AFTER_SYNC mode

MASTER writes each transaction to the binary log and saves it by brushing the disk, and sends the transaction to SLAVE, then waits for SLAVE to return a confirmation message. After receiving the confirmation message, the transaction is submitted to the storage engine and committed, and the result is returned to the client, and then the current client can continue to work.

3. Comparison of the two ways

For the first AFTER_COMMIT method, the current client will receive the return result of the transaction only after the server submits the data to the storage engine and receives the confirmation returned by SLAVE. Before the SLAVE return confirmation message is received after the transaction is committed, other clients can see the transaction information currently committed by the client.

If the SLAVE node does not receive the transaction passed by the MASTER node due to network and other reasons, and the MASTER node is now crash. The HA fails over and the clients are connected to the SLAVE node. When the transaction previously seen in the MASTER node is not seen in the SLAVE node, the transaction loss occurs.

For the second AFTER_SYNC mode, when the transaction is confirmed by SLAVE, MASTER commits the transaction at the storage engine level, and all clients can see the data changes caused by the transaction. Therefore, all clients see the same data at the same time on the MASTER.

In the case of the MASTER node crash, all transactions committed on the MASTER are copied to SLAVE (saved to the relay log). MASTER server unexpected crash. At this point, after HA fails over to SALVE, the data seen by the client is lossless because SLAVE is up to date.

Note, however, that in this case, MASTER cannot be restored directly because its binary log may contain uncommitted transactions, which may cause conflicts with SLAVE when the binary log is restored and used for business requirements.

4. How to turn on semi-synchronization

Mode 1: semi-synchronization exists in the form of plug-ins, which can be turned on directly online (this time)

The primary node is enabled:

[root@GreatSQL] [(none)] > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';Query OK, 0 rows affected (0.02 sec)

Open from the node:

[root@GreatSQL] [(none)] > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Query OK, 0 rows affected (0.02 sec)

PS: in general, all nodes deploy master and slave plug-ins synchronously, which makes it easier to handle failover

Method 2: enable it in my.cnf configuration

Synchronous configuration of both master and slave nodes is enabled:

Plugin-load= "rpl_semi_sync_master=semisync_master.so;rpl_sync_slave=semisync_slave.so" rpl_semi_sync_master_enabled=1rpl_semi_sync_slave_enabled= 1 5. Check the status of the plugin.

Method 1: query plugin

Master node view:

[root@GreatSQL] [test] > show plugins; | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |

View from the node:

[root@GreatSQL] [(none)] > show plugins; | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |

Method 2: query information_schema.plugins information more comprehensively

Master node information:

(Thu Feb 17 03:03:12 2022) [root@GreatSQL] [(none)] > select * from information_schema.plugins where plugin_name like "% semi%"\ G * * 1. Row * * PLUGIN_NAME: rpl_semi_sync_master PLUGIN_VERSION: 1.0PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: REPLICATION PLUGIN_TYPE_VERSION: 4.0PLUGIN_LIBRARY: semisync _ master.soPLUGIN_LIBRARY_VERSION: 1.10 PLUGIN_AUTHOR: Oracle Corporation PLUGIN_DESCRIPTION: Semi-synchronous replication master PLUGIN_LICENSE: GPL LOAD_OPTION: ON1 row in set (0.00 sec) ERROR:No query specified# Slave Node Information (Thu Feb 17 16:05:19 2022) [root@GreatSQL] [(none)] > select * from information_schema.plugins where plugin_name like "% semi%"\ G * * 1. Row * * PLUGIN_NAME: rpl_semi_sync_slave PLUGIN_VERSION: 1.0PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: REPLICATION PLUGIN_TYPE_VERSION: 4.0PLUGIN_LIBRARY: semisync _ slave.soPLUGIN_LIBRARY_VERSION: 1.10 PLUGIN_AUTHOR: Oracle Corporation PLUGIN_DESCRIPTION: Semi-synchronous replication slave PLUGIN_LICENSE: GPL LOAD_OPTION: ON1 row in set (0.00 sec6, Turn on the semi-synchronous function

Since the plug-in is installed online, the service needs to be started after the plug-in installation is complete.

The primary node enables semi-synchronous replication:

[root@GreatSQL] [test] > SET GLOBAL rpl_semi_sync_master_enabled = on;Query OK, 0 rows affected (0.00 sec)

Enable semi-synchronous replication from the node:

T@GreatSQL] [(none)] > SET GLOBAL rpl_semi_sync_slave_enabled = on;Query OK, 0 rows affected (0.00 sec)

After the above settings are completed, restart the IO thread from the node

(Mon Feb 14 15:19:58 2022) [root@GreatSQL] [(none)] > (Mon Feb 14 15:19:58 2022) [root@GreatSQL] [(none)] > STOP SLAVE IO_THREAD;Query OK, 0 rows affected, 1 warning (0.01 sec) (Mon Feb 14 15:21:41 2022) [root@GreatSQL] [(none)] > START SLAVE IO_THREAD;Query OK, 0 rows affected, 1 warning (0.01 sec) 7. Check whether semi-synchronization is running.

Primary node:

[root@GreatSQL] [test] > show status like 'Rpl_semi_sync_master_status' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_master_status | ON | +- -+-+ 1 row in set (0.00 sec)

Slave node:

[root@GreatSQL] [(none)] > show status like 'Rpl_semi_sync_slave_status' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | +- -+-+ 1 row in set (0.01 sec)

Looking at the master node error.log, you can see that the slave node has enabled semi-synchronous replication

# key information Start semi-sync binlog_dump to slave (server_id: 3306) 2022-02-14T02:16:35.411061-05:00 13 [Note] [MY-010014] [Repl] While initializing dump thread for slave with UUID, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread (12). 2022-02-14T02:16:35.411236-05:00 13 [Note] [MY-010462] [Repl] Start binlog_dump to master_thread_id (13) slave_server (3306), pos (, 4) 2022-02-14T02:16:35.411263-05:00 13 [Note] [MY-011170] [Repl] Start asynchronous binlog_dump to slave (server_id: 3306), pos 4). 2022-02-14T02:16:35.411419-05:00 12 [Note] [MY-011171] [Repl] Stop asynchronous binlog_dump to slave (server_id: 3306). 2022-02-14T02:19:33.913084-05:00 9 [Note] [MY-011130] [Repl] Semi-sync replication initialized for transactions.2022-02-14T02:19:33.913133-05:00 9 [Note] [MY-011142] [Repl] Semi-sync replication enabled on the Master.2022-02-14T02:19:33.913638-05:00 0 [Note] [MY-011166] [Repl] Starting ack receiver thread.2022-02-14T02:21:46.899725-05:00 14 [Note] [MY-010014] [Repl] While initializing dump thread for slave with UUID Found a zombie dump thread with the same UUID. Master is killing the zombie dump thread (13). 2022-02-14T02:21:46.899894-05:00 14 [Note] [MY-010462] [Repl] Start binlog_dump to master_thread_id (14) slave_server (3306), pos (, 4) 2022-02-14T02:21:46.899953-05:00 14 [Note] [MY-011170] [Repl] Start semi-sync binlog_dump to slave (server_id: 3306), pos (4).

Above, MySQL semi-synchronous replication has been built!

VIII. Semi-synchronous parameter information

Master node parameter information:

[root@GreatSQL] [test] > show variables like'% Rpl%' +-+-+ | Variable_name | Value | +-- -+-+ | rpl_read_size | 8192 | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_ Count | 1 | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_stop_slave_timeout | 31536000 | + -+ 8 rows in set (0.00 sec)

Some of the parameters are simply described:

Slave node parameter information:

[root@GreatSQL] [test] > show variables like'% Rpl%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_read_size | | 8192 | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | | rpl_stop_slave_timeout | 31536000 | +-+-+ 4 rows in set (sec) |

Some of the parameters are simply described:

9. Semi-synchronous status information

Master node view:

[root@GreatSQL] [test] > show status like'% Rpl_semi%' +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 1 | Rpl_semi_sync_master_net_avg_wait_time | 0 | Rpl_semi_sync_master_net_wait_time | 0 | Rpl_semi_sync_master_net_waits | 0 | Rpl_semi_sync_master_no_times | 0 | Rpl_semi_ Sync_master_no_tx | 0 | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | Rpl_semi_sync_master_tx_avg_wait_time | 0 | Rpl_semi_sync_master_tx_wait_time | 0 | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | 0 | +-+-+ 14 rows in set (0.00 sec)

Brief description of the use of some parameters:

Slave node transition information:

Show global status like'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | +- -+-+ 1 row in set (0.00 sec)

Simple description of the parameters:

10. Test the synchronization of semi-synchronization

Will semi-synchronous be degraded to asynchronous replication? Yes, it will.

When a timeout occurs for semi-synchronous replication (controlled by the rpl_semi_sync_master_timeout parameter in milliseconds, the default is 10000, or 10s), semi-synchronous replication is temporarily turned off and asynchronous replication is used instead.

After the MASTER DUMP thread has sent all the events of a transaction, if a response from the slave library is received within the rpl_semi_sync_master_timeout, the master-slave reverts back to semi-synchronous replication.

1. Temporarily turn off the I / O thread [root@GreatSQL] [(none)] > STOP SLAVE IO_THREAD;Query OK, 0 rows affected, 1 warning (0.02 sec) 2. The master node writes several pieces of test data [root@GreatSQL] [test] > insert into ptype values, (5), (6), (6), Query OK, 3 rows affected (0.02 sec) 3. Check the replication status after waiting for 10 seconds. Semi-sync has turned off [root@GreatSQL] [test] > show status like 'Rpl_semi_sync_slave_status'. +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | OFF | +- -+-+ 1 row in set (0.00 sec) 4. Start the IO thread [root@GreatSQL] [(none)] > START SLAVE IO_THREAD;Query OK, 0 rows affected, 1 warning (0.02 sec) 5 from the node. Check the replication status again. Semi-synchronous replication automatically turns on t@GreatSQL] [test] > show status like 'Rpl_semi_sync_slave_status'. +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | +- -+-+ 1 row in set (0.00 sec) above are all the contents of the article "how to implement semi-synchronous semi-sync replication in MySQL" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.