Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve semi-synchronous replication in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

With regard to the replication architecture of MySQL, there are generally three ways: asynchronous, fully synchronous replication and semi-synchronous replication.

Three modes of replication

The first is asynchronous replication, which is a classic master-slave replication. the default architecture of building master-slave is asynchronous, and its performance is relatively better. But there are still cases of data loss.

The second is full replication, such as MySQL Cluster, which belongs to full replication. in fact, MySQL Cluster does not develop smoothly and is more often a laboratory product, but the time frame is on December 12, 2016. the major feature of MySQL 5.7.17 GA group replication plug-in is launched, which enhances MySQL's original high availability scheme (original Replication scheme) and provides an important feature-multi-write to ensure high availability in the group. Ensure the final consistency of the data. It's kind of like RAC in Oracle.

The third option is between asynchronous and full replication, which is semi-synchronous semi-sync replication. Since the introduction of MySQL 5.5, it is a supplement to asynchronous and full replication, or rather, to the MySQL Cluster scheme.

If you associate this with Oracle, it is actually similar to the Oracle high availability mode. In Oracle, it is the maximum performance mode, the maximum protection mode, and the maximum high availability mode, in which the maximum performance mode is asynchronous, which is similar to the role of asynchronous replication, and the maximum protection mode is full data synchronization, which is somewhat similar to the full replication scheme, while the maximum availability mode is between the two, and can even achieve dynamic switching. Similar to the role of semi-synchronous replication.

Semi-synchronous replication

To turn on semi-synchronization, we need to install the plug-in, which is a simple process. The basic requirement is that in the case of asynchronous replication, the version is above 5.5 and the variable have_dynamic_loading is YES.

> show variables like'% have_dynamic_loading%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

1 row in set (0. 00 sec) in the base directory, you can easily find the plug-ins you need. The current base directory is / usr, and you can find the plug-in by keyword.

# find. -name "semisync_master.so"

. / lib64/mysql/plugin/semisync_master.so

. / lib64/mysql/plugin/debug/semisync_master.so

There are two simple commands to install plug-ins.

> install plugin rpl_semi_sync_master soname 'semisync_master.so'

Query OK, 0 rows affected (0.11 sec)

> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

Query OK, 0 rows affected (0 sec) after installation, check mysql.plugin to see if the plug-in record exists, or you can use show plugins.

> select * from mysql.plugin

+-+ +

| | name | dl |

+-+ +

| | rpl_semi_sync_master | semisync_master.so |

| | rpl_semi_sync_slave | semisync_slave.so |

+-+ +

2 rows in set (0.00 sec) of course the default semi-synchronous switch has not been turned on.

> show variables like 'rpl_semi_sync_master%'

+-+ +

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

+-+ +

4 rows in set (0.00 sec) two parameters rpl_semi_sync_master_enabled and rpl_semi_sync_slave_enabled are involved here, which is more intuitive.

Just open it. Set global rpl_semi_sync_master_enabled=1

Set global rpl_semi_sync_slave_enabled=1; can also use show status if it is simply verified on the master side

> show status like 'rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | ON |

Of course, the same operation needs to be done on the slave side, and then restart the IO_Thread on the slave side.

> STOP SLAVE IO_THREAD

Query OK, 0 rows affected (0.01 sec)

> START SLAVE IO_THREAD

Query OK, 0 rows affected (0.01sec) Master side check Rpl_semi_sync_master_statusSlave side check

Rpl_semi_sync_slave_status

Semi-synchronous changes in MySQL 5.6 and 5.7

A new parameter has been added in MySQL 5.7to control how the main library commits the transaction in semi-synchronous mode before returning to the session transaction successfully.

> show variables like 'rpl_semi_sync_master_wait_point'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_wait_point | AFTER_SYNC |

+-+ +

1 row in set (0.00 sec) and what is the setting in MySQL 5.6? it's AFTER_COMMIT.

How to understand these two parameters. I referred to the pictures in https://sanwen8.cn/p/105GRDe.html.

The data of master is written to binlog,slave and refreshed to disk (relay log), so in OLTP scenario, master needs to wait for slave feedback to receive relay log, and only after receiving ACK will master feed back the commit OK result to the client.

Semi-synchronous replication in MySQL 5.7is called Loss-Less semi-synchronous replication. There are some differences in the way it is implemented.

In this mode (AFTER_SYNC), the transaction is sent to the Slave before it is committed. When the Slave does not receive successfully, and if Master downtime occurs, it will not lead to master inconsistency, because the Master side has not yet committed, so the master and slave have no data.

Simple test semi-synchronous replication

Let's take a brief look at some small tests of semi-synchronous replication.

Create database testsync

Then create a table and insert a row of data. Obviously, the execution speed is very fast.

> create table testsync.test (id int)

Query OK, 0 rows affected (0.07 sec)

> insert into testsync.test values

Query OK, 1 row affected (0. 01 sec) We simulate the network delay and stop the slave directly.

It will be very slow for stop slave; to insert data on the Masterside at this time. The process lasted more than 10 seconds.

> insert into testsync.test values (101)

Query OK, 1 row affected (10.00 sec) Why it is 10 seconds here is related to a parameter that acts as synchronous replication. The unit is milliseconds, so that translates to 10 seconds.

> show variables like 'rpl_semi_sync_master_timeout'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_timeout | 10000 | |

+-+ +

Let's take a look at the switch that acts as synchronous replication.

> show status like 'Rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | OFF |

The +-+-+ OFF terminal is also the state of the server.

Let's get back to state and start slave. Then continue to insert a record on the Masterside, and the speed will be very fast.

> insert into testsync.test values (102)

Query OK, 1 row affected (0.00 sec) the switch is on at this time.

> show status like 'Rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | ON |

+-+-+ look at the database log, in fact, you can also see a very clear message.

2017-02-04T23:37:44.551667+08:00 2145633 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000017, pos: 1056976828), semi-sync up to file mysql-bin.000017, position 1056976573.

2017-02-04T23:37:44.551713+08:00 2145633 [Note] Semi-sync replication switched OFF.

2017-02-04T23:41:05.824146+08:00 2145900 [Note] Start binlog_dump to master_thread_id (2145900) slave_server (13058), pos (mysql-bin.000017, 1056976573)

2017-02-04T23:41:05.824194+08:00 2145900 [Note] Start semi-sync binlog_dump to slave (server_id: 13058), pos (mysql-bin.000017, 1056976573)

2017-02-04T23:41:05.835505+08:00 0 [Note] Semi-sync replication switched ON at (mysql-bin.000017, 1056977083)

On how to achieve semi-synchronous replication in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.