Mysql读写分离

Mysql中间件(Maxscale)
前提:首先需要搭建好一主一从服务!!!
搭建过程参考:https://blog.csdn.net/qq_43062891/article/details/100572345
(1)安装中间件Maxscale软件:
rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
(2)修改主配置文件:
Vim /etc/maxscale.cnf
[maxscale]
threads=auto #自动获取
#添加两台ip为51,52的服务器
[server1]
type=server
address=192.168.4.51
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.4.52
port=3306
protocol=MySQLBackend
[MySQL Monitor] #监听数据库
type=monitor
module=mysqlmon
servers=server1,server2 #监听server1,server2服务
user=syn #用户
passwd=123qqq…A #密码
monitor_interval=10000

#[Read-Only Service] #取消只读
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#passwd=mypwd
#router_options=slave

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=llz
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016

passwd=123qqq…A
max_slave_connections=100%
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016

#在主服务器授权创建用户:
grant replication slave,replication client on . to syn@"%" identified by “123qqq…A”;
grant select on . to llz@"%" identified by “123qqq…A”;
#在有分离服务的主机上开启服务
maxscale -f /etc/maxscale.cnf
#停用服务
Killall -9 maxscale
#查看服务
Ss -ltnup | grep maxscale
测试配置:
在代理服务器本机访问管理
Maxadmin -uadmin -pmariadb -P端口号
客户端连接代理访问数据
Mysql -h服务器地址 -P端口 -u用户名 -p密码