快捷導(dǎo)航

MySQL讀寫(xiě)分離部署實(shí)踐

更新時(shí)間：2026年01月27日 10:30:15 作者：楊了個(gè)楊8982

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

1.環(huán)境準(zhǔn)備

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

開(kāi)放必要的網(wǎng)絡(luò)端口：

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

2. 安裝 ProxySQL

# 添加YUM倉(cāng)庫(kù)配置
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

通過(guò)管理接口連接到 ProxySQL

# 啟動(dòng) ProxySQL 服務(wù)
systemctl start proxysql
systemctl enable proxysql

yum install mysql -y

mysql -u admin -padmin -h 127.0.0.1 -P6032

3.1配置 MySQL 主從服務(wù)器

use main;

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

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

-- 檢查添加結(jié)果
select * from main.mysql_servers;

-- 應(yīng)用配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

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

-- 寫(xiě)操作路由到主庫(kù)（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);

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

-- 應(yīng)用配置
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 用戶認(rèn)證

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

-- 查詢賬戶添加結(jié)果
select * from mysql_users;

-- 應(yīng)用配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

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

-- 在MySQL主從服務(wù)器上創(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.驗(yàn)證配置

檢查服務(wù)器狀態(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

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

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

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

-- 調(diào)整連接池大?。ǜ鶕?jù)實(shí)際情況調(diào)整）
UPDATE global_variables SET variable_value=1000 WHERE variable_name='mysql-threads';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 啟用壓縮（減少網(wǎng)絡(luò)傳輸）
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)控與維護(hù)

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

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

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

監(jiān)控 ProxySQL 性能：

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

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

8.故障處理

-- 將某個(gè)從庫(kù)提升為主庫(kù)（假設(shè)從庫(kù)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;


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