MySQL5.7: 半同步复制（Semisynchronous Replication）配置

关于异步复制、半同步复制、同步复制

1、异步复制：主库上的事务不会等待从库的确认即返回客户端提交成功！ 

With asynchronous replication, the master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.

2、同步复制：主库上提交的事务向客户端返回成功之前，需要收到所有从库提交事务的确认信息。

With fully synchronous replication, when a master commits a transaction, all slaves also will have committed the transaction before the master returns to the session that performed the transaction. The drawback of this is that there might be a lot of delay to complete a transaction.

3、半同步复制：异步复制和同步复制的折中，主库上提交事务时，需要等待至少一个从库发来的收到事件确认信息，才向客户端返回成功。

Semisynchronous replication falls between asynchronous and fully synchronous replication. The master waits only until at least one slave has received and logged the events. It does not wait for all slaves to acknowledge receipt, and it requires only receipt, not that the events have been fully executed and committed on the slave side.

接下来记录下 MySQL5.7 半同步复制的搭建。

--安装前提

1、MySQL5.5 版本或更高

2、主、备库的 have_dynamic_loading 系统变量值为 yes

3、主、备异步复制已部署

--主节点安装 rpl_semi_sync_master 插件

  
  

   
   root@localhost
   
   :
   
   mysql
   
   >
   
   INSTALL PLUGIN rpl_semi_sync_master SONAME 
   
   'semisync_master.so'
   
   ;
  
  
  
  

   
   Query
   
    OK
   
   ,
   
    
   
   0
   
    rows affected 
   
   (
   
   0.17
   
    sec
   
   )
  
  
  
  

   
   

  
  
  
  

   
   root@localhost
   
   :
   
   mysql
   
   >
   
   show variables like 
   
   '%rpl%'
   
   ;
  
  
  
  

   
   +-------------------------------------------+------------+
  
  
  
  

   
   |
   
    
   
   Variable_name
   
                                
   
   |
   
    
   
   Value
   
         
   
   |
  
  
  
  

   
   +-------------------------------------------+------------+
  
  
  
  

   
   |
   
    rpl_semi_sync_master_enabled              
   
   |
   
    OFF        
   
   |
  
  
  
  

   
   |
   
    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.03
   
    sec
   
   )
  
  

--备节点安装 rpl_semi_sync_slave 插件

  
  

   
   root@localhost
   
   :
   
   mysql
   
   >
   
   INSTALL PLUGIN rpl_semi_sync_slave SONAME 
   
   'semisync_slave.so'
   
   ;
  
  
  
  

   
   Query
   
    OK
   
   ,
   
    
   
   0
   
    rows affected 
   
   (
   
   0.15
   
    sec
   
   )
  
  
  
  

   
   

  
  
  
  

   
   root@localhost
   
   :
   
   mysql
   
   >
   
   show variables like 
   
   '%rpl%'
   
   ;
  
  
  
  

   
   +---------------------------------+----------+
  
  
  
  

   
   |
   
    
   
   Variable_name
   
                      
   
   |
   
    
   
   Value
   
       
   
   |
  
  
  
  

   
   +---------------------------------+----------+
  
  
  
  

   
   |
   
    rpl_semi_sync_slave_enabled     
   
   |
   
    OFF      
   
   |
  
  
  
  

   
   |
   
    rpl_semi_sync_slave_trace_level 
   
   |
   
    
   
   32
   
          
   
   |
  
  
  
  

   
   |
   
    rpl_stop_slave_timeout          
   
   |
   
    
   
   31536000
   
    
   
   |
  
  
  
  

   
   +---------------------------------+----------+
  
  
  
  

   
   3
   
    rows 
   
   in
   
    
   
   set
   
    
   
   (
   
   0.03
   
    sec
   
   )
  
  

--主节点配置增加以下

  
  

   
   [
   
   mysqld
   
   ]
  
  
  
  

   
   rpl_semi_sync_master_enabled
   
   =
   
   1
  
  
  
  

   
   rpl
   
   _semi_sync_master_timeout
   
   =
   
   10000
   
    
   
   # 10 second
  
  

备注： rpl_semi_sync_master_enabled 参数控制主节点是否开启半同步复制；rpl_semi_sync_master_timeout 参数控制主节点等待备节点返回确认信息的超时时间，单位为毫秒，超过这个时间后半同步复制转变成异步复制，这里设置成 10 秒。

--备节点配置增加以下

  
  

   
   [
   
   mysqld
   
   ]
  
  
  
  

   
   rpl_semi_sync_slave_enabled
   
   =
   
   1
  
  

备注：rpl_semi_sync_slave_enabled 参数控制备节点是否开启半同步复制； 之后重启主、备库。

--测试

1） 关闭从库

  
  

   
   [
   
   mysql@db2 data
   
   ]
   
   $ mysqladmin 
   
   -
   
   uroot 
   
   -
   
   p shutdown
  
  
  
  

   
   Enter password
   
   :
   
    
  
  
  
  

2） 主库测试

备注：关闭从库后，在主库上创建一张表延迟了 10 秒左右，刚好是参数 rpl_semi_sync_master_timeout 设置的值。