MySQL5.5半同步复制探究

一、原理：
  mysql的默认复制是异步,主库把events写入到二进制日志文件，但是它不知道从库是否接到是否执行了这些
events。如果主库crash掉，提交的事务可能还没有被发送到从库。所以这种情况下，发生failover的时候
可能会导致事务丢失。
   mysql5.5以后新增了半同步复制功能，可以作为一种新的可选择的同步方式：
    1、当连接到一个主库的时候，从库会标示自己是否打开了半同步
    2、如果半同步复制在主库和至少一个从库上打开，主库上提交的的一个事务将会处于阻塞状态直到最少一个半
       同步状态的从库反馈“我已得到这个事务的所有events” 或者timeout发生（参数配置）
    3、从库在事务的所有的events写到自己relay log并且刷到磁盘后，会反馈确认信息
    4、如果没有收到从库的确认信息并且发生了超时，主库会转变成异步同步，当至少一个从库赶上主库的时候，主
       库重新切换到半同步复制
    5、半同步必须在从库和主库同时配置， 如果主库禁止，或者主库打开从库禁止，都会使用异步的方式。

   主库处于阻塞状态（提交后等待slave确认）时，它不会返回到执行这个事务的会话，阻塞结束，主库返回到执行
事务的会话。这时候，提交的事务至少已经被一个slave接受并确认了
   阻塞在回滚（已经写入到二进制日志日志，通常指修改了非事务表的事务做了回滚）也会发生。 回滚的事务也会被
记录到日志，虽然它对事务表没有影响，因为非事务表不能被回滚。

    在没有使用start transaction或者 set autocommit=0的场景下，启用自动提交，每个语句都默认提交，在半同步
复制下，主库会在每一个语句提交的时候发生阻塞（跟显示提交一样等待确认）。

    跟异步复制相比，半同步复制提高了数据完整性。当一个提交成功完成，就说明数据至少已经存在在两个地方了（
主库和至少一个从库）。但是如果主库提交并且正处于等待从库确认阶段，发生了主库crash，这个事务可能还没有被任何的
从库接受到。
  
    半同步复制也在繁忙会话上通过强制二进制日志events传输到从库的速度设置了一个比率限制。当一个用户特别忙的时候
这个功能将会使它慢下来，在一些调度场景这个会些用处

   半同步复制在性能上会受到一些影响（提交需要等待从库确认），但是它提升了数据完整性。  slowdown的时间最少是TCP/IP
在主从直接的往返时间和slave确认接受时间之和。这意味这半同步复制在网络交互快的colse servers上会获得高性能。网络交互
慢的distant servers上表现会很糟糕。

二：安装
安装超级简单：
主库：
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;

从库：
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;

配置文件别忘了修改:
主库：
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
从库：
rpl_semi_sync_slave_enabled=1

重启一下IO_thread就OK了：
STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

如果是MM架构，两边同时配置即可,两边的IO_thread都要重启一下

现在查看一下运行状态（我的是双主架构）：
root:58885:(none)>show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           |
| Rpl_semi_sync_master_net_avg_wait_time     | 353         |
| Rpl_semi_sync_master_net_wait_time         | 4181496     |
| Rpl_semi_sync_master_net_waits             | 11829       |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 49          |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 371         |
| Rpl_semi_sync_master_tx_wait_time          | 4399700     |
| Rpl_semi_sync_master_tx_waits              | 11829       |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 11828       |
| Rpl_semi_sync_slave_status                 | ON          |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

可以看到半同步已经正常运行了。

三、相关参数和状态：
相关参数：
rpl_semi_sync_master_enabled： 控制主库端是否开启半同步
rpl_semi_sync_master_timeout： 控制master端等待slave确认的超时时间，单位毫秒，默认值是10000（10s）
rpl_semi_sync_slave_enabled ： 控制从库端是否开启半同步

rpl_semi_sync_master_trace_level（默认32） ：
The semisynchronous replication debug trace level on the master. Currently, four levels are defined:
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
This variable is available only if the master-side semisynchronous replication plugin is installed

rpl_semi_sync_master_wait_no_slave（默认ON）：
With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response

occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to

normal replication even if the slave count drops to zero during the timeout period.
If the value is ON (the default), it is permissible for the slave count to drop to zero during the timeout period (for example, if slaves disconnect). The master still

waits for the timeout, so as long as some slave reconnects and acknowledges the transaction within the timeout interval, semisynchronous replication continues.
If the value is OFF, the master reverts to normal replication if the slave count drops to zero during the timeout period.
This variable is available only if the master-side semisynchronous replication plugin is installed.

rpl_semi_sync_slave_trace_level(默认32):
The semisynchronous replication debug trace level on the slave. See rpl_semi_sync_master_trace_level for the permissible values.
This variable is available only if the slave-side semisynchronous replication plugin is installed.

相关状态：
Rpl_semi_sync_master_clients: 开启半同步的从库的数目
The number of semisynchronous slaves.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_net_avg_wait_time：主库等待从库回应的平均时间（单位微秒）
The average time in microseconds the master waited for a slave reply.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_net_wait_time :主库等待从库回应的总时间（单位微秒）
The total time in microseconds the master waited for slave replies.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_net_waits :主库等待从库回应的总次数
The total number of times the master waited for slave replies.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_no_times : 主库关闭半同步复制的次数
The number of times the master turned off semisynchronous replication.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_no_tx : 没有成功收到从库确认信息的提交的次数。
The number of commits that were not acknowledged successfully by a slave.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_status ： 主库的半同步是否运行状态：ON正常。
Whether semisynchronous replication currently is operational on the master. The value is ON if the plugin has been enabled and a commit acknowledgment has occurred. It

is OFF if the plugin is not enabled or the master has fallen back to asynchronous replication due to commit acknowledgment timeout.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_timefunc_failures ：
The number of times the master failed when calling time functions such as gettimeofday().
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_tx_avg_wait_time ：master等待每个事务的平均时间（微秒）
The average time in microseconds the master waited for each transaction.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_tx_wait_time :master等待事务的总时间（微秒）
The total time in microseconds the master waited for transactions.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_tx_waits:master等待事务的总次数
The total number of times the master waited for transactions.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_wait_pos_backtraverse : 主库等待的event比前面的events更靠前？
The total number of times the master waited for an event with binary coordinates lower than events waited for previously. This can occur when the order in which

transactions start waiting for a reply is different from the order in which their binary log events are written.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_wait_sessions :当前等待从库回应的会话数
The number of sessions currently waiting for slave replies.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_master_yes_tx : 从库成功确认的提交数目
The number of commits that were acknowledged successfully by a slave.
This variable is available only if the master-side semisynchronous replication plugin is installed.
Rpl_semi_sync_slave_status :从库的半同步状态是否正常
Whether semisynchronous replication currently is operational on the slave. This is ON if the plugin has been enabled and the slave I/O thread is running, OFF

otherwise.
This variable is available only if the slave-side semisynchronous replication plugin is installed.
Rpl_status
The status of fail-safe replication (not implemented). This variable is unused and is removed in MySQL 5.6.

四、扩展篇：
    MHA是目前比较流行的开源的mysql高可用方案，MHA+半同步复制接合堪称完美mysql高可用+数据安全方案，下篇介绍MHA的原理配置。