MYSQL半同步配置思路

更新時間：2024年10月10日 16:18:55 作者：金金金丶

在默認(rèn)設(shè)置下,MySQL使用異步復(fù)制,主庫發(fā)送binlog后不等待從庫確認(rèn),可能導(dǎo)致數(shù)據(jù)不一致或丟失,半同步復(fù)制則在主庫更新數(shù)據(jù)后先等待從庫確認(rèn)同步完成,本文給大家介紹MYSQL半同步配置,感興趣的朋友一起看看吧

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

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

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

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

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

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

4.驗(yàn)證配置結(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é)點(diǎn)加載半同步主模塊，開啟半同步功能，設(shè)置超時時長為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é)點(diǎn)加載半同步從模塊，開啟半同步功能

[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.驗(yàn)證配置結(jié)果

主節(jié)點(diǎn)：

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é)點(diǎn)mysql服務(wù)停掉一個

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é)點(diǎn)mysql服務(wù)都停掉

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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MYSQL半同步配置思路

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具