MySQL讀寫分離部署實踐

更新時間：2026年01月27日 10:30:15 作者：楊了個楊8982

文章介紹了如何配置和使用ProxySQL進行MySQL主從復制集群的部署,包括環(huán)境準備、安裝ProxySQL、配置主從服務器、設置讀寫分離、用戶認證、監(jiān)控和性能優(yōu)化等步驟

1.環(huán)境準備

配置主從復制的 MySQL 集群（1 主多從）Mysql主從復制部署

開放必要的網(wǎng)絡端口：

ProxySQL 默認監(jiān)聽端口：6032（管理端口）、6033（客戶端連接端口）
MySQL 主從服務器端口（默認 3306）

2. 安裝 ProxySQL

# 添加YUM倉庫配置
wget https://repo.proxysql.com/ProxySQL/repo_pub_key -O /etc/pki/rpm-gpg/RPM-GPG-KEY-ProxySQL
rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-ProxySQL

sudo tee /etc/yum.repos.d/proxysql.repo <<EOF
[proxysql]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
enabled=1
EOF

sudo yum install proxysql -y

3. 配置 ProxySQL

通過管理接口連接到 ProxySQL

# 啟動 ProxySQL 服務
systemctl start proxysql
systemctl enable proxysql

yum install mysql -y

mysql -u admin -padmin -h 127.0.0.1 -P6032

3.1配置 MySQL 主從服務器

use main;

-- 添加主庫
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '主庫IP', 3306);

-- 添加從庫（多個從庫重復此步驟）
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '從庫1IP', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '從庫2IP', 3306);

-- 檢查添加結果
select * from main.mysql_servers;

-- 應用配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

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

-- 寫操作路由到主庫（hostgroup_id=10）
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^INSERT|^UPDATE|^DELETE|^REPLACE|^CREATE|^ALTER|^DROP', 10, 1);

-- 讀操作路由到從庫（hostgroup_id=20）
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 20, 1);

-- 應用配置
load mysql query rules to runtime;
load admin variables to runtime;
save mysql query rules to disk;
save admin variables to disk;

3.3 配置 MySQL 用戶認證

-- 添加MySQL客戶端用戶（與MySQL實際用戶保持一致）
INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('業(yè)務用戶名', '業(yè)務用戶密碼', 10);

-- 查詢賬戶添加結果
select * from mysql_users;

-- 應用配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

3.4 配置監(jiān)控用戶（可選但推薦）

-- 在MySQL主從服務器上創(chuàng)建監(jiān)控用戶
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'proxysql'@'%' IDENTIFIED BY '監(jiān)控用戶密碼';
flush privileges;


-- 在ProxySQL中配置監(jiān)控用戶
set mysql-monitor_username='proxysql';
set mysql-monitor_password='監(jiān)控用戶密碼';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

4.驗證配置

檢查服務器狀態(tài)：

SELECT * FROM mysql_servers;
SELECT * FROM runtime_mysql_servers;

檢查查詢規(guī)則：

SELECT * FROM mysql_query_rules;
SELECT * FROM runtime_mysql_query_rules;

5.客戶端連接 ProxySQL

主機：ProxySQL服務器IP
端口：6033
用戶名：業(yè)務用戶名
密碼：業(yè)務用戶密碼

mysql -uroot -p'Asdf#1234' -P6033 -h 127.0.0.1

6.性能優(yōu)化建議

-- 調整連接池大小（根據(jù)實際情況調整）
UPDATE global_variables SET variable_value=1000 WHERE variable_name='mysql-threads';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 啟用壓縮（減少網(wǎng)絡傳輸）
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-connections_compress';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

7.監(jiān)控與維護

查看服務器狀態(tài)：

-- 查看主從服務器狀態(tài)
SELECT hostgroup_id, hostname, port, status FROM runtime_mysql_servers;

-- 查看查詢統(tǒng)計
SELECT hostgroup, count_star, sum_time, first_seen, last_seen FROM stats_mysql_query_digest;

監(jiān)控 ProxySQL 性能：

# 使用內置監(jiān)控命令
proxysql-admin --help

# 查看系統(tǒng)狀態(tài)
top -p $(pgrep proxysql)

8.故障處理

-- 將某個從庫提升為主庫（假設從庫IP為192.168.1.101）
UPDATE mysql_servers SET hostgroup_id=10 WHERE hostname='192.168.1.101';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;


-- 配置主從自動切換
-- 定義主從復制組（hostgroup_id 10=主庫，20=從庫）
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup)
VALUES (10, 20);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;