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)文章希望大家以后多多支持腳本之家!
- MySQL中半同步模式的實(shí)現(xiàn)示例
- MySQL?半同步復(fù)制的實(shí)現(xiàn)
- MYSQL半同步配置思路
- Mysql主從三種復(fù)制模式(異步復(fù)制,半同步復(fù)制,組復(fù)制)
- MySQL主從復(fù)制之半同步semi-sync?replication
- 詳解MySQL的半同步
- MYSQL 完全備份、主從復(fù)制、級(jí)聯(lián)復(fù)制、半同步小結(jié)
- MySQL半同步復(fù)制原理配置與介紹詳解
- 簡(jiǎn)單談?wù)凪ySQL的半同步復(fù)制
- Mysql半同步復(fù)制原理及問(wèn)題排查
- 深入解析半同步與異步的MySQL主從復(fù)制配置
相關(guān)文章
MySQL5.73?root用戶密碼修改方法及ERROR?1193、ERROR1819與ERROR1290報(bào)錯(cuò)解決
這篇文章主要給大家介紹了關(guān)于MySQL5.73?root用戶密碼修改方法及ERROR?1193、ERROR1819與ERROR1290:...?running?with?--skip-...報(bào)錯(cuò)的解決方法,文中通過(guò)圖文將解決的步驟介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02MySQL 統(tǒng)計(jì)查詢實(shí)現(xiàn)代碼
MySQL 統(tǒng)計(jì)查詢其實(shí)就是通過(guò)SELECT COUNT() FROM 語(yǔ)法用于從數(shù)據(jù)表中統(tǒng)計(jì)數(shù)據(jù)行數(shù)2014-05-05MySQL報(bào)錯(cuò)Failed to open the referenced&nbs
在數(shù)據(jù)庫(kù)操作中,嘗試刪除外鍵約束表'master_role'時(shí)遇到錯(cuò)誤碼3730,因其被'user_role'表中的外鍵'fk_user_role'引用,解決方法包括關(guān)閉外鍵檢查和刪除外鍵,阿里巴巴開發(fā)手冊(cè)和知乎回答指出,外鍵雖能維護(hù)數(shù)據(jù)一致性2024-11-11MySQL中實(shí)現(xiàn)行列轉(zhuǎn)換的操作示例
在 MySQL 中進(jìn)行行列轉(zhuǎn)換(即,將某些列轉(zhuǎn)換為行或?qū)⒛承┬修D(zhuǎn)換為列)通常涉及使用條件邏輯和聚合函數(shù),本文給大家介紹了MySQL中實(shí)現(xiàn)行列轉(zhuǎn)換的操作示例,文中有詳細(xì)的代碼示例供大家參考,需要的朋友可以參考下2024-06-06