MySQL读写分离部署实践_Mysql

1.环境准备

配置主从复制的 mysql 集群（1 主多从）mysql主从复制部署

开放必要的网络端口：

proxysql 默认监听端口：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配置读写分离规则

-- 写操作路由到主库（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 ('业务用户名', '业务用户密码', 10);

-- 查询账户添加结果
select * from mysql_users;

-- 应用配置
load mysql users to runtime;
save mysql users to disk;

3.4 配置监控用户（可选但推荐）

-- 在mysql主从服务器上创建监控用户
grant process, replication client on *.* to 'proxysql'@'%' identified by '监控用户密码';
flush privileges;


-- 在proxysql中配置监控用户
set mysql-monitor_username='proxysql';
set mysql-monitor_password='监控用户密码';
load mysql variables to runtime;
save mysql variables to disk;

4.验证配置

检查服务器状态：

select * from mysql_servers;
select * from runtime_mysql_servers;

检查查询规则：

select * from mysql_query_rules;
select * from runtime_mysql_query_rules;

5.客户端连接 proxysql

主机：proxysql服务器ip
端口：6033
用户名：业务用户名
密码：业务用户密码

mysql -uroot -p'asdf#1234' -p6033 -h 127.0.0.1

6.性能优化建议

-- 调整连接池大小（根据实际情况调整）
update global_variables set variable_value=1000 where variable_name='mysql-threads';
load mysql variables to runtime;
save mysql variables to disk;

-- 启用压缩（减少网络传输）
update global_variables set variable_value='true' where variable_name='mysql-connections_compress';
load mysql variables to runtime;
save mysql variables to disk;

7.监控与维护

查看服务器状态：

-- 查看主从服务器状态
select hostgroup_id, hostname, port, status from runtime_mysql_servers;

-- 查看查询统计
select hostgroup, count_star, sum_time, first_seen, last_seen from stats_mysql_query_digest;

监控 proxysql 性能：

# 使用内置监控命令
proxysql-admin --help

# 查看系统状态
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;