MYSQL半同步配置思路

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

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

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

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

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

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

2.主節(jié)點(diǎn)加載半同步主模塊，開啟半同步功能，設(shè)置超時(shí)時(shí)長(zhǎng)為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è)置超時(shí)時(shí)長(zhǎng)為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ù)停掉一個(gè)

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)建一個(gè)數(shù)據(jù)庫(kù)還是能夠迅速同步完成

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

將兩個(gè)從節(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ù)庫(kù)需要等待超時(shí)時(shí)間3秒后才能結(jié)束，且未數(shù)據(jù)庫(kù)同步完成

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

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

您可能感興趣的文章:

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

MYSQL半同步配置思路

相關(guān)文章

最新評(píng)論

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

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

常用在線小工具