MySQL 5.7 Enhancements to the new feature semi-synchronous replication

1. Background informationsemi-synchronous replication

The common replication, the asynchronous replication of MySQL, relies on the MySQL binary log, also known as binary logs, for data replication. For example, two machines, one host (master), the other one is slave (slave).

1. Normal replication is: Transaction one (T1) writes Binlog Buffer;dumper thread notifies slave that there is a new transaction T1;binlog buffer checkpoint;slave The IO thread receives T1 and writes to its own relay log The slave SQL thread is written to the local database. At this point, both master and slave can see the new transaction, even if master hangs, slave can be promoted to the new master.
2. The replication of the exception is: Transaction one (T1) writes binlog buffer;dumper thread notification Slave There is a new transaction T1;binlog buffer Checkpoint;slave because the network is not stable, has not received t1;master hang off, Slave promotion for new MASTER,T1 lost.
3. The big problem is that the host and slave transaction updates are out of sync, even if there is no network or other system exception, when the business comes up, slave because of the sequential execution of master bulk transactions, resulting in a large delay.

To compensate for the lack of these scenarios, MySQL has launched a half-sync from 5.5 onwards. That is, after the master's dumper thread notifies slave, an ACK is added, that is, the successful receipt of the T1 's flag code. That is dumper thread in addition to send T1 to Slave, also undertook to receive slave ack work. If an exception occurs and no ACK is received, it is automatically demoted to normal replication until the exception is repaired.

5.7 Semi-synchronous replication improvements, enhanced master-slave consistency, support waiting for ACK before transaction commit

One of the most critical new parameters for the 5.7 semi-synchronous is rpl_semi_sync_master_wait_point.

1) after_commit

Master writes each transaction to Binlog, passing it to slave to flush to disk (relay log), while the main library commits the transaction. Master waits for slave feedback to receive the relay log, and master only feeds the commit OK result to the client after receiving an ACK.

2) After_sync (default value of 5.7)

Master writes each transaction to Binlog, which is passed to slave flush to disk (relay log). Master waits for the slave feedback to receive the ACK of relay log before committing the transaction and returning a commit OK result to the client. All transactions committed on the main library are guaranteed to be synchronized to the slave relay log, even if the main library crash

2. The experimental processEnvironment Introduction

10.180.2.161 M1
10.180.2.162 M2

[Email protected] ~]# more/etc/issue
CentOS Release 6.9 (Final)

[[email protected] ~]# MySQL--version
MySQL Ver 14.14 distrib 5.7.18, for linux-glibc2.5 (x86_64) using Editline Wrapper

Two servers do keepalive double master, the process is described in the previous article

installing the semi-synchronous plug-in

Two servers install both master and slave plugins because they are two-master

Install plugin rpl_semi_sync_master soname ' semisync_master.so ';

Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';

Show plugins; # Verify that the plugin is installed

| Rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
| Rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |

Modifying a semi-synchronous parameter

Both servers execute

Set global Rpl_semi_sync_master_enabled=1;set global rpl_semi_sync_master_timeout=1000; # 1 seconds set global rpl_semi_sync_slave_enabled=1;

and persisted to the my.cnf file [mysqld]

Check parameter status

[Email protected]:mysql3306.sock [testha]>show global variables like '%semi% ';
+-------------------------------------------+------------+
| variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | On |
| Rpl_semi_sync_master_timeout | 1000 |
| 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_semi_sync_slave_enabled | On |
| Rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in Set (0.01 sec)

turn on half-sync

Since you are already running a dual-master, you only need to restart the io_thread thread at this point

Stop slave io_thread;start slave io_thread;Check if the semi-sync is executing

[Email Protected]:mysql3306.sock [testha]>show global status like '%semi% ';
+--------------------------------------------+-------+
| variable_name | Value |
+--------------------------------------------+-------+
| rpl_semi_sync_master_clients |   1 | # There is a client running in half-sync
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |# The number of transactions that the library does not respond to in a timely manner if the value is large there is a problem
| Rpl_semi_sync_master_status | On |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1309 | # semi-synchronous delay
| Rpl_semi_sync_master_tx_wait_time | 2618 | # semi-synchronous delay
| 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 | 2 | #number of successful transactions in Semi-sync mode
| Rpl_semi_sync_slave_status | On |
+--------------------------------------------+-------+

MySQL 5.7 Enhancements to the new feature semi-synchronous replication