MySQL 主從讀寫分離入門——基本原理以及ProxySQL的簡單使用

一、讀寫分離工作原理

讀寫分離的工作原理：在大型網站業務中，當單臺數據庫無法滿足并發需求時，通過主從同步方式同步數據。設置一臺主服務器負責增、刪、改，多臺從服務器負責查詢，從服務器從主服務器同步數據以保持一致性，從而提高數據庫的并發和負載能力。

簡單來說，讀寫分離就是將數據庫操作分為“讀”和“寫”兩部分，分別由不同的服務器處理。主服務器（通常是單臺）主要負責處理寫操作（如插入、更新、刪除），而從服務器（通常是多臺）則主要負責處理讀操作（如查詢）。主從服務器之間通過主從同步機制保持數據的一致性。通過這種方式，可以顯著提高數據庫的并發處理能力和負載能力，從而減輕單臺服務器的壓力。

二、通過ProxySQL對讀寫分離進行淺層面的理解和運用

1、實驗環境：

機器名稱IP配置服務角色備注
proxy192.168.20.149proxysql控制器用于監控管理
master192.168.20.150數據庫主服務器
slave1192.168.20.146數據庫從服務器
slave2192.168.20.148數據庫從服務器

?2、實現數據庫主從復制

基于GTID實現mysql8.0主從同步，配置過程略。

基本命令：

開啟gtid，并設置server_id值
gtid_mode=ON
enforce-gtid-consistency=ON

建立主從同步

mysql> CHANGE MASTER TO
? ? ?> ? ? MASTER_HOST = host,
? ? ?> ? ? MASTER_PORT = port,
? ? ?> ? ? MASTER_USER = user,
? ? ?> ? ? MASTER_PASSWORD = password,
? ? ?> ? ? MASTER_AUTO_POSITION = 1;

mysql> START SLAVE;

mysql> show slave status \G

........
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes

..........
?

查看slave，雙yes就代表成功

3、安裝ProxySQL

本人博客另外一篇文章可以直接拿

yum install -y proxysql

啟動 ProxySQL

[root@proxy ~]# systemctl enable --now proxysql
#先啟服務，只需要mysql客戶端，直接下mariadb就行了
# 管理員登錄
[root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032

?成功登錄后可以查看一下庫看看是否正常

4、配置 ProxySQL 所需賬戶

在 Master (192.168.20.150) 的MySQL 上創建 ProxySQL 的監控賬戶和對外訪問賬戶

create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;#proxysql 的對外訪問賬戶
create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by '123456';
grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;

5、配置proxySQL

創建組：(定義寫為1，讀為0)

MySQL [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
Query OK, 1 row affected (0.00 sec)MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

注意：ProxySQL會根據server的read_only的取值將服務器進行分組。read_only=0的server，master被分到編號為1的寫組，read_only=1的server，slave則分到編號為0的讀組

所以創建完成之后需要在兩個從服務器配置文件（/etc/my.cnf）添加read_noly=1。

MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 1                | 0                | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

通過查詢我們可以清晰的看到我們所分的組

添加主從服務器節點：

在proxySQL端添加主從服務器的節點，并保存

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.20.150',3306);
Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.146',3306);
Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.148',3306);
Query OK, 1 row affected (0.00 sec)MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)

?重要的信息是要保證主從服務器都是online狀態

為ProxySQL監控MySQL后端節點

MySQL [(none)]> use monitor
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MySQL [monitor]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';
Query OK, 1 row affected (0.00 sec)修改后，保存到runtime和disk
MySQL [monitor]> load mysql variables to runtime;
MySQL [monitor]> save mysql variables to disk;查看監控賬號【ProxySQL】
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
//也可以這樣快速定位
MySQL [(none)]> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| Monitor@123.com          |
+--------------------------+
1 row in set (0.00 sec)

驗證監控信息

ProxySQL 監控模塊的指標都保存在monitor庫的log表中?以下是連接是否正常的監控，對connect指標的監控 ，在前面可能會有很多connect_error，這是因為沒有配置監控信息時的錯誤，配置后如果connect_error的結果為NULL則表示正常

心跳信息的監控

?

?查看read_only日志監控：

MySQL [(none)]> select * from mysql_server_read_only_log;

Monitor 模塊就會開始監控后端的read_only值，當監控到read_only值，就會按照read_only的值將某些節點自動移到讀寫組?
一些監控的狀態斗志在log相關，都在monitor庫下面的 global_variables 變量。?

?ProxySQL配置對外訪問賬號

前面已經配置：配置ProxySQL 賬戶，我創建的對外訪問賬戶是：用戶：proxysql，密碼：123456

將對外訪問賬號添加到mysql_users表中：

MySQL [monitor]> insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
Query OK, 1 row affected (0.000 sec)MySQL [monitor]> load mysql users to runtime;
Query OK, 0 rows affected (0.000 sec)MySQL [monitor]> save mysql users to disk;
Query OK, 0 rows affected (0.007 sec)MySQL [monitor]> select * from mysql_users\G
*************************** 1. row ***************************username: proxysqlpassword: 123456active: 1use_ssl: 0default_hostgroup: 1default_schema: NULLschema_locked: 0
transaction_persistent: 1fast_forward: 0backend: 1frontend: 1max_connections: 10000attributes: comment: 
1 row in set (0.000 sec)

注：transaction_persistent 如果為1，則一個完整的SQL只可能路由到一個節點；這點非常重要，主要解決這種情況：一個事務有混合的讀操作和寫操作組成，事務未提交前，如果事務中的讀操作和寫操作路由到不同節點，那么讀取到的結果必然是臟數據。所以一般情況下，該值應該設置為1，尤其是業務中使用到事務機制的情況（默認為0）

6、測試主從同步

[root@slave1 ~]# mysql -h192.168.20.149 -uproxysql -p'123456' -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)#通過proxysql用戶，創建一個keme庫
mysql> create database keme;
Query OK, 1 row affected (0.00 sec)

在slave2：192.168.20.148上去驗證一下，是否同步過去keme這個庫

7、添加簡單的讀寫分離規則

MySQL [monitor]> insertintomysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);
l query rules to runtime;
save mysql query rulQuery OK, 1 row affected (0.000 sec)es to disk;MySQL 
[monitor]> 
MySQL [monitor]> insert into  values(2,1,'^select',0,1);mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
Query OK, 1 row affected (0.000 sec)MySQL [monitor]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)MySQL [monitor]> save mysql query rules to disk;
Query OK, 0 rows affected (0.006 sec)

8.測試讀寫分離

讀操作：

?寫操作：

簡單的讀寫分離實驗就結束了。?

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/714633.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/714633.shtml
英文地址，請注明出處：http://en.pswp.cn/news/714633.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

C語言數據結構——隊列

C語言數據結構——隊列

目錄 0.前言 1.隊列的基本概念 2.隊列的實現 2.1實現方式 2.2具體實現 3.隊列的應用場景 4.一道隊列的算法題（LeetCode225. 用隊列實現棧） 5.結語 （圖像由AI生成） 0.前言 在計算機科學領域，數據結構是組織和…
閱讀更多...
Linux篇: 進程控制

Linux篇: 進程控制

一、進程創建 1.1 fork函數初識 在Linux中，fork函數是非常重要的函數，它從已存在進程中創建一個新進程。新進程為子進程，而原進程為父進程。 返回值： 在子進程中返回0，父進程中返回子進程的PID，子進程創…
閱讀更多...
OSI七層模型/TCP四層模型

OSI七層模型/TCP四層模型

協議： 協議是雙方共同指定的一組規則，在網絡通信中表示通信雙方傳遞數據和解釋數據的一組規則。 從A上傳文件到服務器B,需要在A和B之間制定一個雙方都認可的規則，這個規則就叫文件傳輸協議，該協議是ftp協議的一個初級版本&#…
閱讀更多...
LeetCode 刷題 [C++] 第226題.翻轉二叉樹

LeetCode 刷題 [C++] 第226題.翻轉二叉樹

題目描述 給你一棵二叉樹的根節點 root ，翻轉這棵二叉樹，并返回其根節點。 題目分析 深度優先搜索（DFS）- 遞歸方式 對于二叉樹的鏡像問題，很容易想到的就是使用遞歸來解決，自底向上依次翻轉每一個節點…
閱讀更多...
2024年騰訊云優惠券領取頁面_代金券使用方法_新老用戶均可

2024年騰訊云優惠券領取頁面_代金券使用方法_新老用戶均可

騰訊云代金券領取渠道有哪些？騰訊云官網可以領取、官方媒體賬號可以領取代金券、完成任務可以領取代金券，大家也可以在騰訊云百科蹲守代金券，因為騰訊云代金券領取渠道比較分散，騰訊云百科txybk.com專注匯總優惠代金券領取頁面&am…
閱讀更多...
『大模型筆記』Sora：探索大型視覺模型的前世今生、技術內核及未來趨勢

『大模型筆記』Sora：探索大型視覺模型的前世今生、技術內核及未來趨勢

Sora：探索大型視覺模型的前世今生、技術內核及未來趨勢 文章目錄 一. 摘要二. 引言楊立昆推薦的關于世界模型的真正含義（或應該是什么）的好文章。原文：Sora: A Review on Background, Technology, Limitations, and Opportunities of Large Vision Models譯文：Sora探索大型…
閱讀更多...
百度SEO快排原理是什么？如何快速排名方法？

百度SEO快排原理是什么？如何快速排名方法？

前言：我之前說過我不打算寫這個快速排序。 首先，我從來沒有在自己的網站上操作過所謂的快速排序。 其次，我不能像網上很多人寫的那樣透露百度快速排序的秘密（說實話，你可以透露秘密）。 方法是有了&#xff…
閱讀更多...
Linux系統運維腳本：編寫bash腳本程序監控服務器的磁盤空間，在磁盤使用率超過閾值時發送警告郵件

Linux系統運維腳本：編寫bash腳本程序監控服務器的磁盤空間，在磁盤使用率超過閾值時發送警告郵件

目 錄 一、要求 二、解決方案 （一）解決思路 （二）方案 三、腳本程序實現 （一）腳本代碼和解釋 1、腳本代碼 2、代碼解釋 （二）腳本驗證 1、腳本編輯 2、給予執…
閱讀更多...
使用遞歸求解數組最大值(c++題解)

使用遞歸求解數組最大值(c++題解)

題目描述 輸入一個整數n（n不大于1000），接下來分別為n個整數，請使用遞歸求取最大值。 輸入格式 第一行：正整數n。 第二行：n個整數。 輸出格式 輸出最大值 樣例 樣例輸入 復制2 1 2樣例輸出 復制2 …
閱讀更多...
Postman： 前端必備工具還是后端獨享利器

Postman： 前端必備工具還是后端獨享利器

Postman 的使用場景：適用于前端和后端 Postman 是一個流行的 API 測試與開發工具。它被廣泛地應用在前后端開發的過程中，但是很多人對于它的使用場景存在疑惑。那么，到底是前端用還是后端用呢？本文將從多個角度詳細解答這個問題。…
閱讀更多...
Node.js_基礎知識（CommonJS模塊化）

Node.js_基礎知識（CommonJS模塊化）

CommonJS模塊化規范 加載時機： 服務器端: 模塊的加載是運行時同步加載的，node.js實現了模塊化規范瀏覽器端: 模塊需要提前編譯打包處理，需使用Browserify編譯打包，推薦使用ESM 暴露模塊：module.exports、exports導入模…
閱讀更多...
“а”搭配使用更地道,柯橋外貿俄語培訓

“а”搭配使用更地道,柯橋外貿俄語培訓

1、а именно 就是說，就是，正是 例： в то время, а именно год назад. 那時, 也就是一年前。 не кто иной, а именно г-н Ван. 不是別人，就是王先生 2、а наоборот …
閱讀更多...
【嵌入式——QT】QListWidget

【嵌入式——QT】QListWidget

QListWidget類提供了一個基于項的列表小部件，QListWidgetItem是列表中的項，該篇文章中涉及到的功能有添加列表項，插入列表項，刪除列表項，清空列表，向上移動列表項，向下移動列表項。 常用API a…
閱讀更多...
C語言數據結構基礎——雙鏈表專題

C語言數據結構基礎——雙鏈表專題

前言 書接上回，雙鏈表便是集齊帶頭、雙向、循環等幾乎所有元素的單鏈表PLUS. 1.初始化、創建雙鏈表 typedef int LTDataType; typedef struct LTNode {LTDataType data;struct LTNode* next;struct LTNode* prev; }LTNode; 不同于單鏈表，此時每個節點應…
閱讀更多...
selenium初始學習--打開新標簽操作

selenium初始學習--打開新標簽操作

selenium 打開新標簽操作 簡單說一下使用 環境 ：python 3.9 selenium 4,18 初始化操作 目的 打開bilibilie網站并搜索視頻（電影） 并點擊觀看 操作 打開應用并搜索網址 from selenium import webdriver import timefrom selenium.webdr…
閱讀更多...
PySide6+VSCode Python可視化環境搭建

PySide6+VSCode Python可視化環境搭建

#記住在cmd中運行，不要在vscode里運行，否則env會裝到工程目錄下 python -m venv env #env\Scripts\activate.bat pip install pyside6 下載本期源碼 vscode裝一個PYQT Integration插件，設置好兩個路徑（下面有個腳本用于獲取路徑&…
閱讀更多...
MySQL 數據庫表設計和優化

MySQL 數據庫表設計和優化

一、數據結構設計 正確的數據結構設計對數據庫的性能是非常重要的。 在設計數據表時，盡量遵循一下幾點： 將數據分解為合適的表，每個表都應該有清晰定義的目的，避免將過多的數據存儲在單個表中。使用適當的數據類型來存儲數據&…
閱讀更多...
2020小學甲組--恢復數組

2020小學甲組--恢復數組

題目描述 有一個數組a[1..n]&#xff0c;但是這個數組的內容丟失了&#xff0c;你要嘗試恢復它。已知以下的三個事實&#xff1a; 1、對于1<i<n&#xff0c;都有a[i]>0&#xff0c;且所有的a[i]互不相同。即a數組保存的全部都是正整數&#xff0c;且互不相同。 2、…
閱讀更多...
挑戰杯 基于機器視覺的車道線檢測

挑戰杯 基于機器視覺的車道線檢測

文章目錄 1 前言2 先上成果3 車道線4 問題抽象(建立模型)5 幀掩碼(Frame Mask)6 車道檢測的圖像預處理7 圖像閾值化8 霍夫線變換9 實現車道檢測9.1 幀掩碼創建9.2 圖像預處理9.2.1 圖像閾值化9.2.2 霍夫線變換 最后 1 前言 &#x1f525; 優質競賽項目系列&#xff0c;今天要分…
閱讀更多...
范偉：你們怎么老提1,200呢,有什么典故啊?趙本山：沒有啊！

范偉：你們怎么老提1,200呢,有什么典故啊?趙本山：沒有啊！

范偉&#xff1a;你們怎么老提1,200呢,有什么典故啊?趙本山&#xff1a;沒有啊&#xff01; --小品《面子》&#xff08;中3&#xff09;的臺詞 表演者&#xff1a;趙本山 高秀敏 范偉 &#xff08;接上&#xff09; 范偉&#xff1a;哎吃啊 趙&#xff1a;哎呀這電視看的挺…
閱讀更多...
最新文章

Copyright @ 2022~2023