Mysql学习之半同步复制

现有环境：
    Mysql 5.7.19+基于GTID复制

半同步复制环境搭建：

    1)主从库上分别安装半同步复制插件
        主：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.45 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

         从：

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.26 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.10 sec)

    2)配置参数(注意如果需要永久生效的话，记得要在主从的my.cnf中也把参数添加进去)
        主：

mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| 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 |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

        从： 

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

     3)从库上开启半同步

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.09 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.11 sec)

        主库同时查看半同步状态：

mysql> show global status like '%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     |
+--------------------------------------------+-------+
14 rows in set (0.15 sec)

半同步复制有关参数： 

rpl_semi_sync_master_enabled
    1)Dynamic：Yes、Scope：Global、Type：Boolean、Default Value：OFF
    2)Controls whether semisynchronous replication is enabled on the master. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. 

rpl_semi_sync_master_timeout
    1)Dynamic：Yes、Scope：Global、Type：Integer、Default Value：10000(ms)
    2)A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. 

rpl_semi_sync_master_trace_level
    1)Dynamic：Yes、Scope：Global、Type：Integer、Default Value：32
    2)The semisynchronous replication debug trace level on the master. Four levels are defined:
        1 = general level (for example, time function failures)
        16 = detail level (more verbose information)
        32 = net wait level (more information about network waits)
        64 = function level (information about function entry and exit)

rpl_semi_sync_master_wait_for_slave_count
    1)Introduced at 5.7.3、Dynamic：Yes、Scope：Global、Type：Integer、Default Value：1、Minimum Value 1、Maximum Value 65535
    2)The number of slave acknowledgments the master must receive per transaction before proceeding.
        If less slaves acknowledge receipt of the transaction during the timeout period, the master reverts to normal replication.

rpl_semi_sync_master_wait_no_slave
    1)Dynamic：Yes、Scope：Global、Type：Boolean、Default Value：ON
    2)Controls whether the master waits for the timeout period configured by rpl_semi_sync_master_timeout to expire, even if the slave count drops to less than the number of slaves configured by rpl_semi_sync_master_wait_for_slave_count during the timeout period.
        When the value of rpl_semi_sync_master_wait_no_slave is ON (the default), it is permissible for the slave count to drop to less than rpl_semi_sync_master_wait_for_slave_count during the timeout period. As long as enough slaves acknowledge the transaction before the timeout period expires, semisynchronous replication continues.
        When the value of rpl_semi_sync_master_wait_no_slave is OFF, if the slave count drops to less than the number configured in rpl_semi_sync_master_wait_for_slave_count at any time during the timeout period configured by rpl_semi_sync_master_timeout, the master reverts to normal replication.
        
rpl_semi_sync_master_wait_point
    1)Introduced at 5.7.2、Dynamic：Yes、Scope：Global、Type：Enumeration、Default Value：AFTER_SYNC、Valid Values：AFTER_SYNC\AFTER_COMMIT
    2)This variable controls the point at which a semisynchronous replication master waits for slave acknowledgment of transaction receipt before returning a status to the client that committed the transaction.
        AFTER_SYNC (the default): 
            1.The master writes each transaction to its binary log and the slave, and syncs the binary log to disk
            2.The master waits for slave acknowledgment of transaction receipt after the sync. 
            3.Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.
        AFTER_COMMIT: 
            1.The master writes each transaction to its binary log and the slave, syncs the binary log
            2.the master commits the transaction to the storage engine. 
            3.The master waits for slave acknowledgment of transaction receipt after the commit. 
            4.Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

rpl_semi_sync_slave_enabled
    1)Dynamic：Yes、Scope：Global、Type：Boolean、Default Value：OFF
    2)Controls whether semisynchronous replication is enabled on the slave. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. 

rpl_semi_sync_slave_trace_level
    1)Dynamic：Yes、Scope：Global、Type：Integer、Default Value：32
    2)The semisynchronous replication debug trace level on the slave. Four levels are defined:
        1 = general level (for example, time function failures)
        16 = detail level (more verbose information)
        32 = net wait level (more information about network waits)
        64 = function level (information about function entry and exit)

Rpl_semi_sync_master_clients
    The number of semisynchronous slaves.

Rpl_semi_sync_master_net_avg_wait_time
    The average time in microseconds the master waited for a slave reply. This variable is deprecated, always 0, and will be removed in a future version.
    
Rpl_semi_sync_master_net_wait_time
    The total time in microseconds the master waited for slave replies. This variable is deprecated, always 0, and will be removed in a future version.

Rpl_semi_sync_master_net_waits
    The total number of times the master waited for slave replies.

Rpl_semi_sync_master_no_times
    The number of times the master turned off semisynchronous replication.

Rpl_semi_sync_master_no_tx
    The number of commits that were not acknowledged successfully by a slave.

Rpl_semi_sync_master_status
    Whether semisynchronous replication currently is operational on the master. 
    The value is ON if the plugin has been enabled and a commit acknowledgment has occurred. 
    It is OFF if the plugin is not enabled or the master has fallen back to asynchronous replication due to commit acknowledgment timeout.

Rpl_semi_sync_master_timefunc_failures
    The number of times the master failed when calling time functions such as gettimeofday().

Rpl_semi_sync_master_tx_avg_wait_time
    The average time in microseconds the master waited for each transaction.

Rpl_semi_sync_master_tx_wait_time
    The total time in microseconds the master waited for transactions.

Rpl_semi_sync_master_tx_waits
    The total number of times the master waited for transactions.

Rpl_semi_sync_master_wait_pos_backtraverse
    The total number of times the master waited for an event with binary coordinates lower than events waited for previously. 
    This can occur when the order in which transactions start waiting for a reply is different from the order in which their binary log events are written.

Rpl_semi_sync_master_wait_sessions
    The number of sessions currently waiting for slave replies.

Rpl_semi_sync_master_yes_tx
    The number of commits that were acknowledged successfully by a slave.

Rpl_semi_sync_slave_status
    Whether semisynchronous replication currently is operational on the slave. 
    This is ON if the plugin has been enabled and the slave I/O thread is running, OFF otherwise.