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的原理配置。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20625855/viewspace-1629109/，如需轉載，請註明出處，否則將追究法律責任。