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)