企业级数据库（2） mysql的半同步复制

半同步复制：一主多从模式下，有一个从节点返回成功，即成功，不必等待多个节点全部返回

在server1（master节点）上安装半同步复制的插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%'; ##查看是否安装完成
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;##激活插件

在server2（slave节点）上安装半同步复制的插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
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.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1 ;
mysql> STOP SLAVE IO_THREAD; ##重启IO线程
mysql> START SLAVE IO_THREAD;

设置完成后在server1上查看变量

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 |
+-------------------------------------------+------------+

mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| 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     |
+--------------------------------------------+-------+

server2上关闭IO线程测试

mysql> STOP SLAVE IO_THREAD;

在server1上写入数据

mysql> insert into aaa values('user4','123');
Query OK, 1 row affected (10.00 sec)  ##等待十秒没有接到slave的请求，自动转换为异步复制

再次写入数据发现写入很快

mysql> insert into aaa values('user5','123');
Query OK, 1 row affected (0.00 sec)

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                 | 2     |
| 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     |
+--------------------------------------------+-------+

这时再将server2的IO线程打开数据就会传过来了

mysql> select * from aaa;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
| user5    | 123      |
+----------+----------+

只要从库的io进程恢复工作就会立即同步没有同步的数据
半同步复制失败后会自动切换成异步复制，从库进程起来之后会检测主从库数据是否同步；若不同步，将会采用异步复制的方式同步数据
将插件安装在数据库中是临时的，退出重新登陆会失效，永久的可以将配置写在配置文件中