最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MYSQL半同步配置思路

 更新時間：2024年10月10日 16:18:55   作者：金金金丶  
在默認設置下,MySQL使用異步復制,主庫發(fā)送binlog后不等待從庫確認,可能導致數(shù)據(jù)不一致或丟失,半同步復制則在主庫更新數(shù)據(jù)后先等待從庫確認同步完成,本文給大家介紹MYSQL半同步配置,感興趣的朋友一起看看吧

MYSQL默認情況是異步復制的，異步復制可以提供最佳的性能，主庫把binlog日志發(fā)送給從庫即結(jié)束，并不會驗證從庫是否接收完畢。這意味著當主服務器或從服務器發(fā)生故障時，有可能從服務器沒有接收到的主服務器發(fā)送過來的binlog日志，這就會造成主服務器和從服務器的數(shù)據(jù)不一致，甚至在恢復時造成數(shù)據(jù)的丟失。

半同步復制即主庫完成數(shù)據(jù)庫更新后不是先給修改數(shù)據(jù)的客戶端做響應，而是先與所以從庫同步binlog日志，一旦有從庫反饋給主庫同步完成則主庫立即響應客戶端，為了讓客戶端有更好的體驗，這種等待從庫反饋的時長不會可以設置的短一些，根據(jù)具體業(yè)務來設置主庫等待從庫的時長。半同步屬于一種降低性能選擇保證數(shù)據(jù)的折中方法。

配置思路：基于一主兩從實現(xiàn)（主從配置已經(jīng)配置完成）

1.查看主配置前的狀態(tài)

2.主節(jié)點加載半同步主模塊，開啟半同步功能，設置超時時長為3000ms

3.從節(jié)點加載半同步從模塊，開啟半同步功能

4.驗證配置結(jié)果

1.查看主配置前的狀態(tài)

MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------------+--------------+
| 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_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)
MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_get_ack | 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_request_ack | 0 |
| 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 | 0 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.000 sec)

2.主節(jié)點加載半同步主模塊，開啟半同步功能，設置超時時長為3000ms

[mysqld]
server-id=211
log-bin
plugin-load-add=semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000

3.從節(jié)點加載半同步從模塊，開啟半同步功能

[mysqld]
server-id=212
log-bin
plugin_load_add=semisync_slave
rpl_semi_sync_slave_enable=on
[mysqld]
server-id=213
log-bin
plugin_load_add=semisync_slave
rpl_semi_sync_slave_enable=on

4.驗證配置結(jié)果

主節(jié)點：

MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_get_ack | 3 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_request_ack | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 613 |
| Rpl_semi_sync_master_tx_wait_time | 613 |
| 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 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.000 sec)
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)

將從節(jié)點mysql服務停掉一個

MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |                     #半同步客戶端數(shù)量為0
| Rpl_semi_sync_master_get_ack | 3 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_request_ack | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 613 |
| Rpl_semi_sync_master_tx_wait_time | 613 |
| 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 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.000 sec)

創(chuàng)建一個數(shù)據(jù)庫還是能夠迅速同步完成

MariaDB [(none)]> create database db5;
Query OK, 1 row affected (0.001 sec)

將兩個從節(jié)點mysql服務都停掉

MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |                 #半同步客戶端數(shù)量為0
| Rpl_semi_sync_master_get_ack | 4 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 3 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_request_ack | 5 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 586 |
| Rpl_semi_sync_master_tx_wait_time | 1172 |
| 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 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.000 sec)

創(chuàng)建數(shù)據(jù)庫需要等待超時時間3秒后才能結(jié)束，且未數(shù)據(jù)庫同步完成

MariaDB [(none)]> create database db6;
Query OK, 1 row affected (3.001 sec)

到此這篇關(guān)于MYSQL半同步的文章就介紹到這了,更多相關(guān)mysql半同步內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論

高阳县| 界首市| 安丘市| 民乐县| 南昌县| 清镇市| 尖扎县| 逊克县| 大埔县| 巴中市| 突泉县| 碌曲县| 望都县| 葵青区| 龙山县| 昌黎县| 乌鲁木齐县| 耒阳市| 闻喜县| 桃园县| 长春市| 将乐县| 宜君县| 静安区| 屏东市| 刚察县| 温州市| 资中县| 武城县| 郓城县| 铁力市| 旌德县| 崇阳县| 渑池县| 宜章县| 利津县| 鹤壁市| 龙井市| 阿城市| 波密县| 九龙坡区|