mysql的主從復(fù)制有以下三張方式
一、異步復(fù)制
在異步復(fù)制時(shí)，主庫執(zhí)行Commit提交操作并寫入BINLOG日志后即可成功返回客戶端，無需等待BINLOG日志傳送給從庫，如圖所示。
image.png
二、半同步復(fù)制
半同步復(fù)制時(shí)，為了保證主庫上的每一個(gè)BINLOG事務(wù)都能夠被可靠地復(fù)制到從庫上，主庫在每次事務(wù)成功提交時(shí)，并不及時(shí)反饋給前端應(yīng)用用戶，而是等待至少一個(gè)從庫(詳見參數(shù)rpl_semi_sync_master_wait_for_slave_count)也接收到BINLOG事務(wù)并成功寫入中繼日志后，主庫才返回Commit操作成功給客戶端(不管是傳統(tǒng)的半同步復(fù)制，還是增強(qiáng)的半同步復(fù)制，目的都是一樣的，只不過兩種方式有一個(gè)席位地方不同，將在下面說明)
半同步復(fù)制保證了事務(wù)成功提交后，至少有兩份日志記錄，一份在主庫的BINLOG日志上，另一份在至少一個(gè)從庫的中繼日志Relay Log上，從而更進(jìn)一步保證了數(shù)據(jù)的完整性。
半同步復(fù)制分為傳統(tǒng)的半同步復(fù)制和增強(qiáng)半同步復(fù)制（也稱無損復(fù)制）
在傳統(tǒng)的半同步復(fù)制中，主庫寫數(shù)據(jù)到BINLOG，且執(zhí)行Commit操作后，會(huì)一直等待從庫的ACK，即從庫寫入Relay Log后，并將數(shù)據(jù)落盤，返回給主庫消息，通知主庫可以返回前端應(yīng)用操作成功，這樣會(huì)出現(xiàn)一個(gè)問題，就是實(shí)際上主庫已經(jīng)將該事務(wù)Commit到了事務(wù)引擎層，應(yīng)用已經(jīng)可以看到數(shù)據(jù)發(fā)生了變化，只是在等待返回而已，如果此時(shí)主庫宕機(jī)，有可能從庫還沒能寫入Relay Log，就會(huì)發(fā)生主從庫不一致。增強(qiáng)半同步復(fù)制就是為了解決這個(gè)問題，做了微調(diào)，即主庫寫數(shù)據(jù)到BINLOG后，就開始等待從庫的應(yīng)答ACK，直到至少一個(gè)從庫寫入Relay Log后，并將數(shù)據(jù)落盤，然后返回給主庫消息，通知主庫可以執(zhí)行Commit操作，然后主庫開始提交到事務(wù)引擎層，應(yīng)用此時(shí)可以看到數(shù)據(jù)發(fā)生了變化。增強(qiáng)半同步復(fù)制的大致流程如下圖所示
image.png
master1和master2上安裝插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;//熱生效
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |//超時(shí)10秒，沒有收到slave的ack應(yīng)答則切換回異步同步
| 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 |//半同步模式
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
mysql> show status like 'rpl_semi_sync%'
-> ;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |//slave端未打開
| 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.01 sec)
masrer1,master2,slave上安裝從庫插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.19 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;//熱生效
Query OK, 0 rows affected (0.01 sec)
mysql> show status like 'rpl_semi_sync%';//查看狀態(tài)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.03 sec)
mysql> stop slave io_thread;//重啟io線程
Query OK, 0 rows affected (0.02 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.02 sec)
mysql> show status like 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
編輯配置文件使配置生效，vi /etc/my.cnf，增加rpl_semi_sync_slave_enabled=1
再次查看slave狀態(tài)
mysql> show status like 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
查看master上的狀態(tài)
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |//表示多少個(gè)從庫
| 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.01 sec)
master上編輯/etc/my.cnf增加rpl_semi_sync_master_enabled=1
測試
在master上新增數(shù)據(jù)，查看半同步情況
mysql> show status like 'rpl_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 | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |//未使用半同步成功的條數(shù)
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 2598 |
| Rpl_semi_sync_master_tx_wait_time | 2598 |
| 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 | 1 |//半同步成功條數(shù)
+--------------------------------------------+-------+
14 rows in set (0.02 sec)
測試半同步超時(shí)自動(dòng)切換為異步同步,在slave上停止io線程
mysql> stop slave io_thread;
master上新增數(shù)據(jù)
mysql>INSERT INTO `master`.`im_article`( `user_id`, `class_id`, `tags_id`, `title`, `abstract`, `image`, `is_asterisk`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES ( 1, 1, '1', '1', '1', '1', 1, 1, '2023-07-05 13:46:08', '2023-07-05 13:46:04', '2023-07-05 13:46:11');
Query OK, 1 rows affected (10.05 sec)//10秒后啟用異步復(fù)制，用時(shí)0.05秒進(jìn)行數(shù)據(jù)同步
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |//io關(guān)閉后，此時(shí)沒有關(guān)聯(lián)的從庫
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 1 |//此處變?yōu)?，未使用半同步成功的條數(shù)
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 2598 |
| Rpl_semi_sync_master_tx_wait_time | 2598 |
| 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 | 1 |//半同步成功條數(shù)
+--------------------------------------------+-------+
14 rows in set (0.02 sec)
