m-s的半同步設定，m-s半同步設定

m-s的半同步設定，m-s半同步設定
master：安裝半同步外掛程式
(system@localhost) [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.83 sec)


(system@localhost) [(none)]> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
。。。。。。。。。。。。。
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)


啟用主庫半同步，並設定等待slave回應時間，單位秒
(system@localhost) [(none)]> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)


(system@localhost) [(none)]> set global rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)


slave:載入半同步外掛程式
(system@localhost) [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.30 sec)


(system@localhost) [(none)]> show plugins;
+----------------------------+----------+--------------------+-------------------+---------+
| Name                       | Status   | Type               | Library           | License |
+----------------------------+----------+--------------------+-------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
。。。。。。。。。。。
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |
+----------------------------+----------+--------------------+-------------------+---------+
43 rows in set (0.13 sec)


從庫開啟半同步
(system@localhost) [(none)]> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.03 sec)


這一步主要是為了讓slave重連master節點，註冊成為半同步身份，不重啟則一直保持非同步複製。
(system@localhost) [(none)]> stop slave io_thread;
Query OK, 0 rows affected (0.06 sec)


(system@localhost) [(none)]> start slave io_thread;
Query OK, 0 rows affected (0.02 sec)


(system@localhost) [(none)]> show status;
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| Aborted_clients                               | 0                   |
| Aborted_connects                              | 0                   |
。。。。。。。。。。。。。。
| Rpl_semi_sync_slave_status                    | ON                  |
。。。。。。。。。。


master：查看外掛程式是否載入，開啟半同步
(system@localhost) [(none)]> 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      | 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.04 sec)


測試同步：
(system@localhost) [test]> insert into t1 values('cc');
Query OK, 1 row affected (0.10 sec)


slave：
(system@localhost) [test]> select * from t1;
+------+
| name |
+------+
| dcw  |
| jjf  |
| jone |
| cc   |
+------+
4 rows in set (0.00 sec)


master：
(system@localhost) [test]> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 617   |
| Rpl_semi_sync_master_net_wait_time         | 617   |
| Rpl_semi_sync_master_net_waits             | 1     |
| 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      | 22958 |
| Rpl_semi_sync_master_tx_wait_time          | 22958 |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.02 sec)