MySQL5.7 Semi-synchronous replication

I. Overview

5.5 and 5.7 semi-synchronous replication may differ, starting with MySQL5.5, MySQL supports semi-synchronous replication in the form of plugins

Async : By default, MySQL replication is asynchronous. The main library immediately returns the result to the client after executing a client-submitted transaction, and does not care if it has been received and processed from the library. At this point, if the master crashes, the committed transaction may not have been uploaded to the top, resulting in inconsistent data.

Full synchronization : When the main library executes the accepted transaction, it waits for all slave machines to execute the return value of the transaction, which is returned to the client when it receives the return value of all the slave machines. So the impact on performance is serious.

semi-synchronous : Between the two, the main library waits for at least one receive from the machine and writes to the relay log after executing the client's transaction to return to the client. It improves the security of the data and also causes a certain amount of delay, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in low latency networks.

relay Log Relay logs : In salve, the IO thread pulls master's binary to relay log, and the SQL thread reads the contents of the Relay-log log and applies it to the slave server.

Two plug-ins for semi-synchronous functions. The main side has a plug-in that has a plug-in from the end.
Semi-synchronous replication must be enabled in both primary and slave, otherwise asynchronous replication is used.
Only events that receive transactions are acknowledged from the device after the event is written to the relay log and flushed to disk.
If a time-out occurs without any acknowledgment of a transaction from the server, the primary server resumes asynchronous replication. When at least one semi-synchronous plex machine catches up, the host returns to semi-synchronous replication.

After_commit (5.6 Default value)
Master writes the transaction into the Binlog log, passes the slave flush to Relay_log, and the master commits the transaction. Master waits for slave feedback to write to relay log, and master does not feed the commit OK result back to the client until the SCK packet is received

After_sync (5.7 default value)
Master writes the transaction into the Binlog and uploads the slave to disk relay log. Master waits for Slave to receive the ACK packet, commits the transaction and returns a commit OK result to the client, so that even if the main library goes down, all the libraries on the main library will be synchronized to the slave relay log.

Second, semi-synchronous replication configuration

1.) Install related plugins

Master:--Install the semisync_master.so plugin
mysql> Install plugin rpl_semi_sync_master soname ' semisync_master.so ';

Slave:--install semisync_slave.so plug-in
mysql> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';

To see if a plugin is installed:
Mysql> SELECT plugin_name, plugin_status from INFORMATION_SCHEMA. PLUGINS WHERE plugin_name like '%semi% ';
+------------------------------+-------------------------+
| Plugin_name | Plugin_status |
+------------------------------+-------------------------+
| Rpl_semi_sync_master | ACTIVE |
+------------------------------+------------------------ +

2. It is disabled by default. Both the primary and the slave must have plug-ins enabled to enable semi-synchronous replication

Open mode:

1.) Change the variable:
Master
SET GLOBAL rpl_semi_sync_master_enabled = {0|1}; #1来启用半同步复制, the% disable it
SET GLOBAL rpl_semi_sync_master_timeout = N; #该值N以毫秒为单位给出. The default value is 10000 (10 seconds)
Slave
SET GLOBAL rpl_semi_sync_slave_enabled = {0|1};

2.) Modify the configuration file:
[Mysqld]
rpl_semi_sync_master_enabled = 1
...
......
If you are enabling semi-synchronous replication on a running slave, you will need to restart the I/O thread to reconnect the slave to the master and register as a semi-synchronous plex station.
STOP SLAVE Io_thread;
START SLAVE Io_thread;

3. Monitor semi-synchronous replication
mysql> SHOW VARIABLES like ' rpl_semi_sync% ';

+-------------------------------------------+------------+| Variable_name                             | Value      |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled              | On         | | rpl_semi_sync_master_timeout                     | | rpl_semi_sync_master_trace_level                   1          | | Rpl_semi_sync_master_wait_no_slave        | On         | | rpl_semi_sync_master_wait_point           | After_sync |+-------------------------------------------+------------+6 in Set (0.00 sec)

mysql> SHOW STATUS like ' rpl_semi_sync% ';

+--------------------------------------------+-------+| variable_name | Value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients |1| How many semi-are there?Sync's backup library| Rpl_semi_sync_master_net_avg_wait_time |0|average time to wait for a standby response after a transaction has been committed| Rpl_semi_sync_master_net_wait_time |0|total number of waiting network responses| Rpl_semi_sync_master_net_waits |7|Total network wait time| Rpl_semi_sync_master_no_times |0| A total of several times from semi-sync falls back to normal state| 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 .| Rpl_semi_sync_master_status | On | semi-on the main librarydoes sync open properly ?| Rpl_semi_sync_master_timefunc_failures |0|number of times the time function is not working correctly| Rpl_semi_sync_master_tx_avg_wait_time |410| Open semi-Sync, the average time to wait for a transaction to return| Rpl_semi_sync_master_tx_wait_time |2876|total time the transaction waits for a standby response| Rpl_semi_sync_master_tx_waits |7|total number of transactions awaiting standby response| Rpl_semi_sync_master_wait_pos_backtraverse |0|change the current number of times to wait for the minimum binary log| rpl_semi_sync_master_wait_sessions |0|There are currently several line thread is waiting, such as standby library response| Rpl_semi_sync_master_yes_tx |7| semi-number of successful transactions in sync mode+--------------------------------------------+-------+ theRowsinchSet (0.00Sec

MySQL5.7 Semi-synchronous replication