MySQL 5.7semi-synchronous mechanism 02/24 Update SLTechnology News&Howtos

MySQL 5.7semi-synchronous mechanism

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

The so-called semi-synchronous mechanism, as long as the master node confirms that at least one slave node has received the thing, it can return the information of successful operation to the client. The master node does not even need to wait for the slave node to successfully execute the thing, as long as at least one slave node receives the thing and successfully writes it to the local relay log file.

First, semi-synchronous replication of MySQL is provided as a plug-in to check whether it is currently installed.

(root@localhost) [(none)] > show plugins

+-+

+-+

+-+

44 rows in set (0.00 sec) 2. This example is not installed yet. Check the plugin directory location.

(root@localhost) [(none)] > show variables like 'plugin_dir'

+-- +

| | Variable_name | Value |

+-- +

| | plugin_dir | / usr/local/mysql/lib/plugin/ |

+-- +

1 row in set (0.00 sec) 3, there are two plug-ins related to semi-synchronization in the plugin directory

[root@oracle11gtest plugin] # ls-al semisync*

-rwxr-xr-x 1 root root 688816 Feb 6 19:44 semisync_master.so

-rwxr-xr-x 1 root root 150555 Feb 6 19:44 semisync_ slave.so4, install semisync_master.so on the master side and semisync_slave.so on the slave side

(root@localhost) [(none)] > install plugin rpl_semi_sync_master SONAME 'semisync_master.so'

Query OK, 0 rows affected (0.04 sec)

(root@localhost) [(none)] > install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so'

Query OK, 0 rows affected (0. 01 sec) 5, after installation, use the show plugins command check

Sixth, modify the parameters related to the semi-synchronization of the main end.

(root@localhost) [(none)] > show variables like'% rpl_semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | OFF |

| | 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 |

+-+ +

6 rows in set (0.00 sec)

(root@localhost) [(none)] > set global rpl_semi_sync_master_enabled=1;-used to control whether semi-synchronous replication is enabled on the primary side

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)] > set global rpl_semi_sync_master_timeout=3000;-used to instruct the master to wait for the slave response (in milliseconds), which I set to 3 seconds

Query OK, 0 rows affected (0.00 sec) 7, modify the parameters related to the semi-synchronization of slave

(root@localhost) [(none)] > show variables like'% rpl_semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | OFF |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

2 rows in set (0.01sec)

(root@localhost) [(none)] > set global rpl_semi_sync_slave_enabled=1;-used to control whether semi-synchronous replication is enabled on the server

Query OK, 0 rows affected (0.00 sec) 8, restart the io_thread thread of the slave node in order to allow slave to reconnect to master and register as a semi-synchronous slave identity

(root@localhost) [(none)] > stop slave io_thread

Query OK, 0 rows affected (0.01 sec)

(root@localhost) [(none)] > start slave io_thread

Query OK, 0 rows affected (0.00 sec) nine, test

Insert data insert into oms3.gl values ('zg') on the masterside

Insert into oms3.gl values ('gl'); check whether slave is applied

(root@localhost) [(none)] > select * from oms3.gl

+-+

| | abcd |

+-+

| | test |

| | gl |

| | zg |

+-+ View master related variables (root@localhost) [mysql] > show status like 'rpl_semi_sync%'

+-+ +

| | 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 | 417 |

| | Rpl_semi_sync_master_tx_wait_time | 834 | |

| | Rpl_semi_sync_master_tx_waits | 2 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 1 | |

Mainly focus on four parameters:

Rpl_semi_sync_master_status-indicates whether the current master node has semi-synchronous mode enabled

Rpl_semi_sync_master_no_tx-indicates the number of things that have not been successfully sent to the slave node

Rpl_semi_sync_master_yes_tx-indicates the number of things that have been successfully sent to the slave node

Rpl_semi_sync_master_clients--- indicates the number of slave nodes currently in semi-synchronous mode

5.7 semi-synchronous mechanism change

1: set the number of slave for rpl_semi_sync_master_wait_slave_count parameter to receive the binlog before successfully returning the client request. The default is one, but you cannot specify which one.

Second: rpl_semi_sync_master_wait_point parameter, which has two options:

AFTER_SYNC (default value of 5.7, none of this option in 5.6)

The master database writes each transaction to the binary log and sends it to the slave database. The master database is waiting for the confirmation message written from the slave library to its own relay-log. After receiving the confirmation message, the master database writes the transaction to the storage engine and feeds back the corresponding results to the client.

AFTER_COMMIT (5.6 default)

The master library writes each transaction to the binary log and sends it to the slave database, and then writes the transaction to the storage engine immediately; after waiting for confirmation of the information written from the library to its own relay-log, the master library feeds back the corresponding results to the client.

The difference between them:

The drawback of the AFTER_COMMIT mode is that although the master library has not told the client that the transaction was successful (before the slave library successfully confirmed), it has actually been committed, and other clients can see the result of the transaction at this time.

That is, for the transaction committer itself, it is guaranteed that as long as it is received successfully, it will be successful, but other clients may find that the transaction is successful, but it is not successful in the end, because the data has reached the storage engine layer.

For example, A client performs a transaction to change the field id from 0 to 1.

1.A commit the transaction to the main library

two。 The main library writes binlog

3. The master library is sent to the slave library, and the master library commits the transaction to the storage engine

The confirmation from the library has not been received at this time, and An is still waiting for the result, but at this point client B can already see that the field id is 1.

If the master database is down at this time, and if the transaction actually received from the slave library is only that the master database has not received the confirmation, then the data from the slave library is still correct and id=1. After the client switches to the slave library, you will see id=1.

But if you don't actually receive the transaction from the library, then id=0 from the library at this time, and this is no problem for client A, because A did not receive any feedback on the transaction that was previously committed, so A needs to determine whether the previous transaction was successful in some other way, that is, A can accept id=0 or id=1.

But for client B, the change from id=1 to id=0 is probably unacceptable.

The AFTER_SYNC pattern can solve this problem, continuing with the above example:

1.A commit the transaction to the main library

two。 The main library writes binlog

3. Send the master library to the slave library

4. The master library waits for confirmation from the slave library. At this time, id=0, no client can see the result of id=1, because it is not submitted.

5. The master library received confirmation from the slave library, and the master library began to submit to the storage engine.

6. The main library returns the result to the client.

If the master library is down and the client switches to the slave library in step 4, if the transaction is actually received from the slave library, then id=1, if no transaction is received from the slave library, then id=0 at this time

No matter which state it is, it is consistent for all client databases, and the transaction is not lost. Similarly, client A needs to check the transaction status himself, because he has not received feedback, and he allows two possibilities (commit or rollback).

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.