使用ProxySql實現MySQL的讀寫分離

前言

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

核心功能與作用

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

適用場景

• 中小型 MySQL 集群的讀寫分離和負載均衡。
• 需要高可用、低延遲的在線業(yè)務（如電商、金融）。
• 希望簡化數據庫集群管理，減少開發(fā)層對數據庫架構依賴的場景。

與同類工具對比

• MySQL Router：官方工具，配置簡單，但功能較少（如無連接池、高級路由）。
• MaxScale：功能豐富，但性能略遜于 ProxySQL，配置相對復雜。
• MyCat：支持多數據庫類型，但更側重分庫分表，MySQL 適配性不如 ProxySQL。

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

安裝配置

安裝MySQL

懶得安裝 隨便寫了個腳本

mysql.sh

下載Proxysql包

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

安裝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 語言訪問 MySQL 數據庫的驅動程序，ProxySQL 安裝和運行過程中可能需要通過 Perl 腳本與 MySQL 進行交互
• ??perl-DBI? ：Database Interface（數據庫接口）模塊，為 Perl 程序提供了一個標準的數據庫訪問接口。ProxySQL 的部分功能可能依賴于 Perl 的 DBI 模塊來實現對數據庫的操作
• ??mysql-community-libs-compat? ：當使用 rpm 包方式安裝 ProxySQL 時，可能需要安裝mysql-community-libs-compat ，否則可能會報錯。它提供了與 MySQL 相關的兼容庫文件，確保 ProxySQL 與 MySQL 之間的兼容性
• 此外，系統中還應確保安裝了bzip2 ，雖然它不是 ProxySQL 直接的功能依賴，但在處理一些壓縮文件等操作時可能會用到。同時，也需要安裝一個 MySQL 客戶端 ，方便后續(xù)連接 ProxySQL 進行配置和測試等操作

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

安裝Proxysql

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

查看是否啟動

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       

啟動proxysql服務并加入開機自啟

systemctl start proxysql
systemctl enable proxysql

二、連接 ProxySQL 管理界面

mysql -uadmin -padmin -h127.0.0.1 -P6032

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

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

三、ProxySQL 管理員賬戶配置

1. 查看當前管理員賬戶

Admin> select @@admin-admin_credentials;

• 作用：查看 ProxySQL 當前的管理員賬戶列表（默認只有admin:admin）。

2. 添加新管理員賬戶

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

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

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

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

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

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

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

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

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

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這個用戶去監(jiān)控后端 MySQL 服務器的健康狀態(tài)”。

五、添加 MySQL 服務器到 ProxySQL

1. 注冊主從服務器

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

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

• 參數解釋：
  • ?hostgroup_id：服務器組 ID（1 = 寫組，2 = 讀組，用于區(qū)分主從）
  • ?hostname：MySQL 服務器的 IP 地址
  • ?port：MySQL 端口（默認 3306）
  • ?weight：權重（數值越大，分配的請求越多，默認 1 即可）
• 作用：告訴 ProxySQL“有這兩臺 MySQL 服務器可用，1 號是主庫（寫），2 號是從庫（讀）”。

2. 生效并保存服務器配置

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

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

六、配置數據庫訪問用戶

1. 在 MySQL 主從庫創(chuàng)建訪問用戶

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

• 作用：創(chuàng)建兩個用戶：
  • ?adm：有所有權限（用于執(zhí)行寫操作，如新增 / 修改數據）
  • ?read：只有查詢權限（用于執(zhí)行讀操作，如查詢數據）
• 注意：主從庫都要創(chuàng)建這兩個用戶（因為 ProxySQL 可能會連接任意一臺）。

2. 在 ProxySQL 中注冊用戶

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

• 作用：告訴 ProxySQL“有這兩個用戶會通過你訪問 MySQL，adm默認走主庫，read默認走從庫”。

3. 生效并保存用戶配置

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

• 作用：讓 ProxySQL 加載用戶信息，允許這些用戶通過 ProxySQL 訪問 MySQL。

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

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

-- 規(guī)則1：特殊SELECT語句走主庫（例如包含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語句走從庫
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^SELECT',2,1);
-- 規(guī)則3：SHOW語句走從庫
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (3,1,'^SHOW',2,1);

• 參數解釋：
  • ?rule_id：規(guī)則 ID（唯一，用于區(qū)分）
  • ?active=1：啟用該規(guī)則
  • ?match_digest：匹配 SQL 語句的正則表達式（^SELECT表示以 SELECT 開頭的語句）
  • ?destination_hostgroup：匹配后路由到的服務器組（1 = 主庫，2 = 從庫）
  • ?apply=1：匹配后立即應用，不再檢查后續(xù)規(guī)則
• 作用：定義 “哪些 SQL 語句走主庫，哪些走從庫”，實現自動路由。

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

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

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

八、測試讀寫分離

1. 測試讀操作（通過 read 用戶）

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

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

2. 測試寫操作（通過 adm 用戶）

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

• 作用：創(chuàng)建一個數據庫（寫操作），預期會路由到主庫（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語句的路由記錄

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

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

-- 服務器配置
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 的配置默認只在內存中，重啟后會丟失。這組命令用于將所有配置永久保存到磁盤，確保服務器重啟后配置仍然有效。

總結

整個流程的核心是：

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

通過這些步驟，ProxySQL 就能自動實現 “寫操作走主庫，讀操作走從庫” 的讀寫分離，減輕單臺數據庫的壓力。

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

最新評論