使用ProxySql實(shí)現(xiàn)MySQL的讀寫(xiě)分離

前言

ProxySQL 是一款高性能、開(kāi)源的 MySQL 數(shù)據(jù)庫(kù)中間件，主要作用是在應(yīng)用程序與 MySQL 數(shù)據(jù)庫(kù)之間搭建一層代理，實(shí)現(xiàn)數(shù)據(jù)庫(kù)的負(fù)載均衡、讀寫(xiě)分離、連接池管理、查詢路由、故障轉(zhuǎn)移等功能，從而提升數(shù)據(jù)庫(kù)集群的可用性、穩(wěn)定性和性能。

核心功能與作用

1. 讀寫(xiě)分離
   自動(dòng)將 SQL 查詢請(qǐng)求分發(fā)到不同的數(shù)據(jù)庫(kù)節(jié)點(diǎn)：
   • 寫(xiě)操作（如 INSERT、UPDATE）路由到主庫(kù)（Master）。
   • 讀操作（如 SELECT）分發(fā)到從庫(kù)（Slave），減輕主庫(kù)壓力，提高查詢效率。
     支持自定義規(guī)則（如基于 SQL 語(yǔ)句、用戶、表名等）靈活分配請(qǐng)求。
2. 負(fù)載均衡
   對(duì)于多個(gè)從庫(kù)（或主庫(kù)集群），ProxySQL 可通過(guò)輪詢、權(quán)重分配等策略，將讀請(qǐng)求均勻分發(fā)到各節(jié)點(diǎn)，避免單節(jié)點(diǎn)過(guò)載，提升集群整體吞吐量。
3. 連接池管理
   數(shù)據(jù)庫(kù)連接的創(chuàng)建和銷(xiāo)毀成本較高，ProxySQL 維護(hù)一個(gè)連接池，復(fù)用已建立的連接，減少連接開(kāi)銷(xiāo)，同時(shí)限制連接總數(shù)，防止數(shù)據(jù)庫(kù)被過(guò)多連接壓垮。
4. 故障檢測(cè)與自動(dòng)切換
   持續(xù)監(jiān)控?cái)?shù)據(jù)庫(kù)節(jié)點(diǎn)的健康狀態(tài)（如通過(guò)心跳檢測(cè)），當(dāng)主庫(kù)或從庫(kù)故障時(shí)，自動(dòng)將請(qǐng)求切換到正常節(jié)點(diǎn)，減少人工干預(yù)，提高系統(tǒng)可用性。
5. 查詢緩存與過(guò)濾
   緩存高頻查詢結(jié)果，減少重復(fù)計(jì)算；同時(shí)可過(guò)濾危險(xiǎn) SQL（如 DROP、TRUNCATE），增強(qiáng)數(shù)據(jù)庫(kù)安全性。
6. 動(dòng)態(tài)配置與無(wú)感知重啟
   支持在線修改配置（如路由規(guī)則、節(jié)點(diǎn)權(quán)重），無(wú)需重啟服務(wù)，避免業(yè)務(wù)中斷，適合高可用場(chǎng)景。

適用場(chǎng)景

• 中小型 MySQL 集群的讀寫(xiě)分離和負(fù)載均衡。
• 需要高可用、低延遲的在線業(yè)務(wù)（如電商、金融）。
• 希望簡(jiǎn)化數(shù)據(jù)庫(kù)集群管理，減少開(kāi)發(fā)層對(duì)數(shù)據(jù)庫(kù)架構(gòu)依賴的場(chǎng)景。

與同類工具對(duì)比

• MySQL Router：官方工具，配置簡(jiǎn)單，但功能較少（如無(wú)連接池、高級(jí)路由）。
• MaxScale：功能豐富，但性能略遜于 ProxySQL，配置相對(duì)復(fù)雜。
• MyCat：支持多數(shù)據(jù)庫(kù)類型，但更側(cè)重分庫(kù)分表，MySQL 適配性不如 ProxySQL。

ProxySQL 憑借輕量、高性能、靈活的特性，成為 MySQL 中間件的主流選擇之一。

安裝配置

安裝MySQL

懶得安裝 隨便寫(xiě)了個(gè)腳本

mysql.sh

下載Proxysql包

https://github.com/sysown/proxysql/releases
#我使用的是Centos7系統(tǒng) 并且架構(gòu)是x86虛擬機(jī) 可以通過(guò)uname -u 查看自己的系統(tǒng)架構(gòu)根據(jù)自己的系統(tǒng)和版本適配下載

安裝Proxysql

安裝依賴環(huán)境

yum install perl-DBD-mysql perl-DBI mysql-community-libs-compat bzip2 -y

• ??perl-DBD-mysql?：Perl Database Driver for MySQL，是 Perl 語(yǔ)言訪問(wèn) MySQL 數(shù)據(jù)庫(kù)的驅(qū)動(dòng)程序，ProxySQL 安裝和運(yùn)行過(guò)程中可能需要通過(guò) Perl 腳本與 MySQL 進(jìn)行交互
• ??perl-DBI? ：Database Interface（數(shù)據(jù)庫(kù)接口）模塊，為 Perl 程序提供了一個(gè)標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)訪問(wèn)接口。ProxySQL 的部分功能可能依賴于 Perl 的 DBI 模塊來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的操作
• ??mysql-community-libs-compat? ：當(dāng)使用 rpm 包方式安裝 ProxySQL 時(shí)，可能需要安裝mysql-community-libs-compat ，否則可能會(huì)報(bào)錯(cuò)。它提供了與 MySQL 相關(guān)的兼容庫(kù)文件，確保 ProxySQL 與 MySQL 之間的兼容性
• 此外，系統(tǒng)中還應(yīng)確保安裝了bzip2 ，雖然它不是 ProxySQL 直接的功能依賴，但在處理一些壓縮文件等操作時(shí)可能會(huì)用到。同時(shí)，也需要安裝一個(gè) MySQL 客戶端 ，方便后續(xù)連接 ProxySQL 進(jìn)行配置和測(cè)試等操作

• PS：安裝前創(chuàng)建一個(gè)Proxysql用戶和組

安裝Proxysql

rpm -ivh proxysql-2.6.3-1-centos7.x86_64.rpm

查看是否啟動(dòng)

netstat -anput | grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      7698/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      7698/proxysql       

啟動(dòng)proxysql服務(wù)并加入開(kāi)機(jī)自啟

systemctl start proxysql
systemctl enable proxysql

二、連接 ProxySQL 管理界面

mysql -uadmin -padmin -h127.0.0.1 -P6032

• 參數(shù)解釋：
  • ?-uadmin：使用用戶名admin登錄
  • ?-padmin：密碼是admin?
  • ?-h127.0.0.1：連接本地服務(wù)器（ProxySQL 管理界面默認(rèn)只允許本地訪問(wèn)）
  • ?-P6032：連接 ProxySQL 的管理端口（6032 是 ProxySQL 管理界面的默認(rèn)端口）

• 作用：登錄到 ProxySQL 的管理界面，后續(xù)所有配置都在這里完成。

三、ProxySQL 管理員賬戶配置

1. 查看當(dāng)前管理員賬戶

Admin> select @@admin-admin_credentials;

• 作用：查看 ProxySQL 當(dāng)前的管理員賬戶列表（默認(rèn)只有admin:admin）。

2. 添加新管理員賬戶

Admin> set admin-admin_credentials='admin:admin;root:root123';

• 作用：添加一個(gè)新的管理員賬戶root，密碼root123（分號(hào)分隔多個(gè)賬戶）。
• 用途：默認(rèn)賬戶只能本地登錄，新增賬戶可用于遠(yuǎn)程管理（如用 Navicat 連接）。

3. 生效并保存管理員賬戶配置

Admin> load admin variables to runtime;  # 讓配置立即生效（臨時(shí)生效）
Admin> save admin variables to disk;   # 保存到磁盤(pán)（永久生效，重啟不丟失）

• 作用：ProxySQL 的配置需要先加載到內(nèi)存（runtime）才能生效，再保存到磁盤(pán)（disk）才能永久保留。

四、配置監(jiān)控賬號(hào)

1. 在 MySQL 主庫(kù)創(chuàng)建監(jiān)控用戶

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

• 作用：創(chuàng)建一個(gè)專門(mén)用于監(jiān)控的 MySQL 用戶monitor，權(quán)限限制為：
  • ?USAGE：允許登錄但無(wú)實(shí)際操作權(quán)限
  • ?REPLICATION CLIENT：允許查詢主從復(fù)制狀態(tài)（用于 ProxySQL 檢測(cè)主從是否正常）

2. 在 ProxySQL 中配置監(jiān)控用戶

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

• 作用：告訴 ProxySQL“用monitor這個(gè)用戶去監(jiān)控后端 MySQL 服務(wù)器的健康狀態(tài)”。

五、添加 MySQL 服務(wù)器到 ProxySQL

1. 注冊(cè)主從服務(wù)器

-- 添加主服務(wù)器（寫(xiě)操作專用）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (1,'192.168.8.100',3306,1,'Write group');

-- 添加從服務(wù)器（讀操作專用）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (2,'192.168.8.101',3306,1,'Read group');

• 參數(shù)解釋：
  • ?hostgroup_id：服務(wù)器組 ID（1 = 寫(xiě)組，2 = 讀組，用于區(qū)分主從）
  • ?hostname：MySQL 服務(wù)器的 IP 地址
  • ?port：MySQL 端口（默認(rèn) 3306）
  • ?weight：權(quán)重（數(shù)值越大，分配的請(qǐng)求越多，默認(rèn) 1 即可）
• 作用：告訴 ProxySQL“有這兩臺(tái) MySQL 服務(wù)器可用，1 號(hào)是主庫(kù)（寫(xiě)），2 號(hào)是從庫(kù)（讀）”。

2. 生效并保存服務(wù)器配置

LOAD MYSQL SERVERS TO RUNTIME;  # 臨時(shí)生效
SAVE MYSQL SERVERS TO DISK;    # 永久保存

• 作用：讓 ProxySQL 加載剛才添加的 MySQL 服務(wù)器信息。

六、配置數(shù)據(jù)庫(kù)訪問(wèn)用戶

1. 在 MySQL 主從庫(kù)創(chuàng)建訪問(wèn)用戶

-- 創(chuàng)建管理員用戶（有寫(xiě)權(quán)限）
CREATE USER 'adm'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'adm'@'%';
-- 創(chuàng)建只讀用戶（只有讀權(quán)限）
CREATE USER 'read'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON *.* TO 'read'@'%';
FLUSH PRIVILEGES;  # 刷新權(quán)限使配置生效

• 作用：創(chuàng)建兩個(gè)用戶：
  • ?adm：有所有權(quán)限（用于執(zhí)行寫(xiě)操作，如新增 / 修改數(shù)據(jù)）
  • ?read：只有查詢權(quán)限（用于執(zhí)行讀操作，如查詢數(shù)據(jù)）
• 注意：主從庫(kù)都要?jiǎng)?chuàng)建這兩個(gè)用戶（因?yàn)?ProxySQL 可能會(huì)連接任意一臺(tái)）。

2. 在 ProxySQL 中注冊(cè)用戶

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('adm','123456',1);  -- adm用戶默認(rèn)用寫(xiě)組（1）
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('read','123456',2);  -- read用戶默認(rèn)用讀組（2）

• 作用：告訴 ProxySQL“有這兩個(gè)用戶會(huì)通過(guò)你訪問(wèn) MySQL，adm默認(rèn)走主庫(kù)，read默認(rèn)走從庫(kù)”。

3. 生效并保存用戶配置

LOAD MYSQL USERS TO RUNTIME;  # 臨時(shí)生效
SAVE MYSQL USERS TO DISK;    # 永久保存

• 作用：讓 ProxySQL 加載用戶信息，允許這些用戶通過(guò) ProxySQL 訪問(wèn) MySQL。

七、配置讀寫(xiě)分離規(guī)則

1. 添加路由規(guī)則

-- 規(guī)則1：特殊SELECT語(yǔ)句走主庫(kù)（例如包含UPDATE的查詢）
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FROM UPDATE$',1,1);
-- 規(guī)則2：普通SELECT語(yǔ)句走從庫(kù)
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^SELECT',2,1);
-- 規(guī)則3：SHOW語(yǔ)句走從庫(kù)
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (3,1,'^SHOW',2,1);

• 參數(shù)解釋：
  • ?rule_id：規(guī)則 ID（唯一，用于區(qū)分）
  • ?active=1：?jiǎn)⒂迷撘?guī)則
  • ?match_digest：匹配 SQL 語(yǔ)句的正則表達(dá)式（^SELECT表示以 SELECT 開(kāi)頭的語(yǔ)句）
  • ?destination_hostgroup：匹配后路由到的服務(wù)器組（1 = 主庫(kù)，2 = 從庫(kù)）
  • ?apply=1：匹配后立即應(yīng)用，不再檢查后續(xù)規(guī)則
• 作用：定義 “哪些 SQL 語(yǔ)句走主庫(kù)，哪些走從庫(kù)”，實(shí)現(xiàn)自動(dòng)路由。

2. 生效并保存規(guī)則

LOAD MYSQL QUERY RULES TO RUNTIME;  # 臨時(shí)生效
SAVE MYSQL QUERY RULES TO DISK;    # 永久保存

• 作用：讓 ProxySQL 加載剛才設(shè)置的路由規(guī)則。

八、測(cè)試讀寫(xiě)分離

1. 測(cè)試讀操作（通過(guò) read 用戶）

mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname,@@port"

• 參數(shù)解釋：
  • ?-uread：用只讀用戶read登錄
  • ?-P6033：連接 ProxySQL 的數(shù)據(jù)庫(kù)訪問(wèn)端口（6033 是 ProxySQL 對(duì)外提供數(shù)據(jù)庫(kù)服務(wù)的端口）
  • ?-e：直接執(zhí)行后面的 SQL 語(yǔ)句（查詢當(dāng)前連接的服務(wù)器主機(jī)名和端口）
• 預(yù)期結(jié)果：返回從庫(kù)（192.168.8.101）的信息，說(shuō)明讀操作走了從庫(kù)。

2. 測(cè)試寫(xiě)操作（通過(guò) adm 用戶）

mysql -uadm -p123456 -h 127.0.0.1 -P6033 -e "create database test2;"

• 作用：創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)（寫(xiě)操作），預(yù)期會(huì)路由到主庫(kù)（192.168.8.100）。

3. 查看 ProxySQL 的路由記錄

mysql -uadmin -padmin -h127.0.0.1 -P6032  # 登錄管理界面
select hostgroup,digest_text from stats_mysql_query_digest\G;  # 查看SQL語(yǔ)句的路由記錄

• 作用：檢查 ProxySQL 是否按規(guī)則路由：
  • ?hostgroup=1：表示語(yǔ)句走了主庫(kù)
  • ?hostgroup=2：表示語(yǔ)句走了從庫(kù)

九、保存所有配置（防止丟失）

-- 服務(wù)器配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- 查詢規(guī)則配置
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- 用戶配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
-- 變量配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

• 作用：ProxySQL 的配置默認(rèn)只在內(nèi)存中，重啟后會(huì)丟失。這組命令用于將所有配置永久保存到磁盤(pán)，確保服務(wù)器重啟后配置仍然有效。

總結(jié)

整個(gè)流程的核心是：

1. 安裝 ProxySQL 并啟動(dòng)
2. 告訴 ProxySQL“有哪些 MySQL 服務(wù)器（主從）”
3. 告訴 ProxySQL “用什么用戶監(jiān)控這些服務(wù)器”
4. 告訴 ProxySQL “允許哪些用戶通過(guò)你訪問(wèn) MySQL”
5. 告訴 ProxySQL“哪些 SQL 語(yǔ)句走主庫(kù)，哪些走從庫(kù)”
6. 測(cè)試并保存配置

通過(guò)這些步驟，ProxySQL 就能自動(dòng)實(shí)現(xiàn) “寫(xiě)操作走主庫(kù)，讀操作走從庫(kù)” 的讀寫(xiě)分離，減輕單臺(tái)數(shù)據(jù)庫(kù)的壓力。

到此這篇關(guān)于MySQL中使用ProxySql實(shí)現(xiàn)讀寫(xiě)分離的文章就介紹到這了,更多相關(guān)ProxySql mysql讀寫(xiě)分離內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論