您好,登錄后才能下訂單哦!
MySQL5.5之前,MySQL的復制是異步操作,主庫和從庫的數(shù)據(jù)之間存在一定的延遲。這樣存在一定的隱患:當主庫上寫入一個事務并
交成功,而從庫尚未得到主庫推送的Binlog日志時,主庫宕機了,例如主庫可能因磁盤損壞、內(nèi)存故障等造成主庫上該事務Binlog丟失,此時從庫就可能損失這個事務,從而造成主從不一致。
為了解決這個問題,MySQL5.5引入了半同步復制機制。在MySQL5.5之前的異步復制時,主庫完成了Commit提交操作之后,在主庫寫
Binlog日志后即可成功返回客戶端,無需等待Binlog日志傳送給從庫。
而半同步復制時,為了保證主庫上的每一個Binlog事務都能夠被可靠的復制到從庫上,主庫在每次事務提交時,并不及時反饋給前
臺應用用戶,而是等待其中一個從庫也接收到Binlog事務并成功寫入中繼日志后,主庫才返回Commit操作成功給客戶端。半同步復制保證
了事務成功提交后,至少有兩份日志記錄,一份在主庫的Binlog日志上,另一份在至少一臺從庫的中繼日志Relay Log上,從而更進一步
保證了數(shù)據(jù)的完整性。
半同步復制模式下,如果主庫在事務提交前的任何一個環(huán)節(jié)故障,則事務提交不成功,從庫上也沒收到事務對應的Binlog日志,所
以主從數(shù)據(jù)是一致的;假如在主庫傳送Binlog日志到從庫過程中,從庫宕機或網(wǎng)絡故障,導致Binlog并沒有及時地傳送到從庫上,此時
主庫的事務會等待一段時間(時間長短由rpl_semi_sync_master_timeout設置的秒數(shù)決定),如果Binlog在這段時間內(nèi)無法成功推送到從
庫上,則MySQL自動調(diào)整復制為異步模式,事務正常返回提交結(jié)果給客戶端
半同步很大程度上取決于主從庫之間的網(wǎng)絡狀況,往返時延RTT越小決定了從庫實時性越好。通俗的說主從之間的網(wǎng)絡越快從庫越實
時。
半同步模式是作為MySQL5.5的一個插件來實現(xiàn)的,主庫和重開使用不同的插件,安裝比較簡單,在上一節(jié)異步復制的基礎上,安裝
半同步復制插件即可。
(1)、首先,判斷MySQL服務器是否支持動態(tài)增加插件:
MariaDB [(none)]> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.01 sec)
(2)、確認支持增加插件后,檢查MySQL的安裝目錄下是否存在插件,一般默認在$MYSQL_HOME/lib/plugin目錄下存在主庫插件
semisync_master.so和從庫插件semisync_slave.so
[root@www ~]# ls /usr/local/mysql/lib/plugin/semisync_*
-rwxr-xr-x 1 root root 510355 Jun 13 09:44 /usr/local/mysql/lib/plugin/semisync_master.so
-rwxr-xr-x 1 root root 427031 Jun 13 09:45 /usr/local/mysql/lib/plugin/semisync_slave.so
在主庫上安裝插件semisync_master.so:
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)
從庫上則安裝semisync_slave.so:
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.08 sec)
安裝完成后,從plugin表中看到剛才安裝的插件:
MariaDB [(none)]> select * from mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select * from mysql.plugin;
+---------------------+-------------------+
| name | dl |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
1 row in set (0.00 sec)
也就是說,安裝完成后,MySQL會在系統(tǒng)表plugin中記錄剛才安裝的插件,下次重啟后會自動加載插件。
(3)、需要分別在主庫和從庫上配置參數(shù)打開半同步 semi-sync,默認半同步設置是不打開的,
主庫上配置全局參數(shù):
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> set global rpl_semi_sync_master_timeout=30000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [pancou]> show variables like '%rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 30000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
從庫上一樣的配置參數(shù):
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [pancou]> show variables like 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
注意,由于之前配置的復制是異步復制,所以要重啟一下從庫上的I/O線程(如果是全新配置的半同步復制則不需要):
MariaDB [(none)]> stop slave io_thread;start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
至此半同步配置完畢,下面可以來驗證一下。主庫上通過show status命令能夠看到當前半同步復制的一些狀態(tài)值:
MariaDB [(none)]> show status like '%semi_sync%';
+--------------------------------------------+-------+
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
著重關注以下3個狀態(tài)值:
Rpl_semi_sync_master_status:值為ON,表示半同步復制目前處于打開狀態(tài)。
Rpl_semi_sync_master_yes_tx:值為0,表示主庫當前尚未有任何一個事務時通過半同步復制到從庫。
Rpl_semi_sync_master_no_tx: 值為0,表示當前有0個事務不是通過半同步模式下從庫及時響應的(記住只給值,后面有對比)。
執(zhí)行一個事務,再檢查一下:
MariaDB [pancou]> create table emp(emp_id smallint,emp_name varchar(10));
Query OK, 0 rows affected (0.10 sec)
MariaDB [pancou]> insert into emp values(1,'tom'),(2,'jerry');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [pancou]> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1496 |
| Rpl_semi_sync_master_net_wait_time | 2992 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 1203 |
| Rpl_semi_sync_master_tx_wait_time | 2407 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
此時會發(fā)現(xiàn)Rpl_semi_sync_master_yes_tx值變?yōu)?,即剛才的create 和 insert事務通過半同步復制到從庫上了,
Rpl_semi_sync_master_yes_tx 基數(shù)增加到2,到從庫上確認一下:
MariaDB [pancou]> select * from emp;
+--------+----------+
| emp_id | emp_name |
+--------+----------+
| 1 | tom |
| 2 | jerry |
+--------+----------+
2 rows in set (0.00 sec)
再嘗試一下網(wǎng)絡異常的場景下,主庫等待rpl_semi_sync_master_timeout毫秒超時后,自動轉(zhuǎn)成異步復制的場景。
(1)、首先,在主庫上確認半同步會等待30秒超時;
MariaDB [pancou]> show variables like '%semi%time%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 30000 |
+------------------------------+-------+
1 row in set (0.00 sec)
(2)、在主庫上通過iptables命令模擬從庫宕機或網(wǎng)絡故障:
第二天
MariaDB [pancou]> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
show variables like 'rpl_semi_%';
+------------------------------------+-------+
| 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_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.01 sec)
MariaDB [pancou]> show status like '%semi_sync%';
+--------------------------------------------+-------+
| 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)
MariaDB [pancou]> delete from emp where emp_id=2;
Query OK, 1 row affected (0.01 sec)
MariaDB [pancou]> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1494 |
| Rpl_semi_sync_master_net_wait_time | 1494 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
在從庫上
iptables -I INPUT -s 192.168.110.128 -j DROP
(3)、在主庫上執(zhí)行一個事務并提交(默認提交即可),主庫上的提交操作會被阻塞30秒:
MariaDB [pancou]> insert into emp values(2,'jerry');
---等待
新打開一個主庫的線程
MariaDB [(none)]> show processlist\G
*************************** 1. row ***************************
Id: 7
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
Id: 10
User: root
Host: localhost
db: pancou
Command: Query
Time: 4
State: Waiting for semi-sync ACK from slave
Info: insert into emp values(2,'jerry')
Progress: 0.000
*************************** 3. row ***************************
Id: 11
User: repl_user
Host: 192.168.110.130:52165
db: NULL
Command: Binlog Dump
Time: 2037
State: Reading from net
Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
MariaDB [(none)]> show processlist\G
*************************** 1. row ***************************
Id: 6
User: root
Host: localhost
db: pancou
Command: Sleep
Time: 248
State:
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 7
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
2 rows in set (0.00 sec)
(4)、主庫上等待30秒后
Query OK, 1 row affected (10.00 sec)
MariaDB [pancou]> insert into emp values(2,'jerry');
Query OK, 1 row affected (0.01 sec)
檢查半同步的狀態(tài)值
MariaDB [pancou]> show status like '%semi_sync%';
+--------------------------------------------+----------+
| Variable_name | Value |
+--------------------------------------------+----------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 15016390 |
| Rpl_semi_sync_master_net_wait_time | 30032780 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 1 |
+--------------------------------------------+----------+
14 rows in set (0.00 sec)
Rpl_semi_sync_master_status | OFF
Rpl_semi_sync_master_yes_tx | 1 剛才并沒有通過半同步復制完成,所以半同步并沒有累加
Rpl_semi_sync_master_no_tx | 2
繼續(xù)之前的場景下從庫正常連接上主機之后,確認是否會自動切換回半同步復制模式。
iptables -D INPUT 1
檢查從庫上的狀態(tài):
MariaDB [pancou]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.128
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1940
Relay_Log_File: www-relay-bin.000011
Relay_Log_Pos: 1024
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
(2)、檢查半同步復制狀態(tài)值
MariaDB [pancou]> show status like '%semi_sync%';
+--------------------------------------------+----------+
| Variable_name | Value |
+--------------------------------------------+----------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 10019692 |
| Rpl_semi_sync_master_net_wait_time | 30059077 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| 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 | 1 |
+--------------------------------------------+----------+
14 rows in set (0.00 sec)
Rpl_semi_sync_master_status 值自動由OFF變?yōu)?| ON ,說明主庫檢測到正常以后主庫到從庫的復制方式自動切換為
半同步方式
(3)、在主庫上做一個測試
MariaDB [pancou]> insert into emp values(3,'bob');
Query OK, 1 row affected (0.01 sec)
MariaDB [pancou]> show status like '%semi_sync%';
+--------------------------------------------+----------+
| Variable_name | Value |
+--------------------------------------------+----------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 7515331 |
| Rpl_semi_sync_master_net_wait_time | 30061326 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 742 |
| Rpl_semi_sync_master_tx_wait_time | 742 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+----------+
14 rows in set (0.01 sec)
提交一個事務后,Rpl_semi_sync_master_yes_tx 由1變?yōu)?2,確認剛才事務是半同步復制。
從半同步復制的流程會發(fā)現(xiàn),半同步復制的 “半” 就體現(xiàn)在:雖然主庫和從庫的Binlog日志是同步的,但是主庫并不等待從庫應用這部分
日志就返回提交結(jié)果,這部分操作是異步的,從庫的數(shù)據(jù)并不是和主庫實時同步的,所以只能稱之為半同步而不是完全的實時同步。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。