异步复制的缺点在于主库只负责将数据库的增,改,查操作记录到 binary log中,而从库在通过日志对操作进行回放同步之后,主库对从库的同步内容不检测,不知道是否同步,这里引入半同步复制解决这一问题.
半同步复制在协议中添加了一个同步步骤。 这意味着主节点在提交时需要等待从
节点确认它已经接收到事务。只有这样,主节点才能继续提交操作。
这种模式下主节点只需要接收到其中一台从节点的返回信息,就会commit;否则需要等待直到超时时间然后切换成异步模式再提交;这样做的目的可以使主从数据库的数据延迟缩小,可以提高数据安全性,确保了事务提交后,binlog至少传输到了一个从节点上,不能保证从节点将此事务更新到db中。性能上会有一定的降低,响应时间会变长。
如下图所示:
1.在主库安装插件并激活
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; ##安装插件
Query OK, 0 rows affected (0.04 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> SET GLOBAL rpl_semi_sync_master_enabled =1; ##激活
Query OK, 0 rows affected (0.00 sec)
2.在从库安装插件并激活
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
3.在主库查看信息
mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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.00 sec)
mysql> show variables like '%rpl%';
+-------------------------------------------+------------+
| 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 |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
4.在从库查看信息
mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
5.测试
1.将从库的IO线程关闭,在主库添加信息,模拟网络卡顿
slave:
mysql> STOP SLAVE IO_THREAD; #此时主库收不到从库发送的ack,模仿网络卡顿
Query OK, 0 rows affected (0.00 sec)
master:
mysql> use redhat;
Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.01 sec)
mysql> insert into usertb values ('user3','123');
Query OK, 1 row affected (10.05 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
3 rows in set (0.00 sec)
在slaver上查看,可以看到数据不能同步
mysql> SELECT * FROM redhat.usertb; +----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
2.在主库查看状态,打开从库IO线程,模拟网络通畅
master:
mysql> show status like '%rpl%'; #此时半同步已经关闭,变成了异步复制
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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 | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| 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.00 sec)
slave:
mysql> show status like '%rpl%'; #半同步关闭,异步开启
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> START SLAVE IO_THREAD; #打开io,模拟网络恢复通畅
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM redhat.usertb; ##查看数据,已经同步
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
3 rows in set (0.00 sec)