高级DBA带你学习Mysql代理Proxysql主从读写分离安装配置例子全网最详细

       Proxysql安装配置  

1.ProxySQL 介绍
ProxySQL 是基于 MySQL 的一款开源的中间件的产品（类似MYCAT），是一个灵活的 MySQL 代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载（无需重启 ProxySQL 服务），故障切换和一些 SQL 的过滤功能。

1.安装架构
IP Server-id 数据库版本 备注
192.168.2.153 1 MySQL 客户端 ProxySQL2.0.15
192.168.2.103 2 MySQL 8.0.18服务 Master（主库）
192.168.2.169 3 MySQL 8.0.18服务 Slave1(从库)
192.168.2.251 4 MySQL 8.0.18服务 Slave1(从库)

2.安装部署方法
步骤1：192.168.2.153上安装依赖包
操作系统环境为centos 6.5

为下载好的依赖包要依次按顺序安装，用root用户执行
rpm -ivh perl-DBI-1.616-1.el6.rfx.x86_64.rpm
rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpm
rpm -ivh proxysql-2.0.15-1-centos67.x86_64.rpm

2.验证安装是否正常
执行命令rpm -ql proxysql

图上属于安装正常
vi /etc/proxysql.cnf

修改这2个值，第一个线程数32，第二个是MYSQL的版本这个要对应你服务器安装MYSQL的版本。

3.启动代理服务
service proxysql start
验证启动成功

4.所有数据库建立代理MYSQL账号
192.168.2.103，192.168.2.169，192.168.2.251分离建立代理用户

用户名：proxysql
密码设置为：pwproxysql
赋予全部权限（因为监管程序有一些建索引需求）
建完用户之后
ALTER USER ‘proxysql’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘pwproxysql’;
FLUSH PRIVILEGES;
然后再其他电脑上能通过账号联入即可，用Navicat for MySQL在其他电脑上验证，能查询！
或者用mysql -u -p -h 命令其他服务器上测试！
然后每个MYSQL服务器执行
set global max_connect_errors = 900000000000;
show variables like ‘max_connect_errors’;
flush hosts;

5.建立监控账号
192.168.2.103，192.168.2.169，192.168.2.251分离建立监控用户

用户名：monitor
密码设置为：monitor
赋予全部权限建完用户之后
ALTER USER monitor@‘%’ IDENTIFIED WITH mysql_native_password BY ‘monitor’;
FLUSH PRIVILEGES;
然后再其他电脑上能通过账号联入即可，用Navicat for MySQL在其他电脑上验证，能查询！
或者用mysql -u -p -h 命令其他服务器上测试！

6.进入到192.168.2.153（6032端口跟6033端口需要打开防火墙）
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

如果该服务器没有安装过MYSQL，则需要安装MYSQL客户端

rpm -Uvh mysql-community-common-8.0.16-2.el6.x86_64.rpm
rpm -Uvh mysql-community-libs-8.0.16-2.el6.x86_64.rpm
rpm -Uvh mysql-community-libs-compat-8.0.16-2.el6.x86_64.rpm
rpm -Uvh mysql-community-client-8.0.16-2.el6.x86_64.rpm

安装完成之后，成功登录代理管理端端口6032

执行下面：
set mysql-default_charset=‘utf8mb4’;
set mysql-default_sql_mode=‘NO_ENGINE_SUBSTITUTION’;
set mysql-mysql-max_allowed_packet=67108864;
set mysql-ping_interval_server_msec=300000; #sleep会话保持时长300s
set mysql-max_connections=4000; #ProxySQL能处理的和前端客户端的最大连接
set mysql-server_version=‘8.0.16’; #proxysql响应给客户端的MySQL版本号

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

执行
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(1,2,‘高可用’);

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,‘192.168.2.103’,3306,1,‘Write Group’);
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,‘192.168.2.169’,3306,1,‘Read Group’);
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,‘192.168.2.251’,3306,1,‘Read Group2’);

Insert into mysql_users (username,password,default_hostgroup) values (‘proxysql’,‘pwproxysql’,1);

Insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,‘^select.*for update$’,1,1);

Insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,‘^select’,2,1);

验证是不是各个MYSQL是不是正常通的
select * from mysql_server_ping_log;

null就代表是正常的，如果不正常会有异常报错信息！

//加载到内存
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;
//永久生效

save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;

验证成功
mysql -uproxysql -ppwproxysql -P6033 -h127.0.0.1 -e “select @@datadir”;

mysql -uproxysql -ppwproxysql -P 6033 -h 127.0.0.1 -e “select @@server_id;”
每个SELECT每次执行都分开查不同的数据库实例！

笔者简介
国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！