MySQL-13-读写分离

MySQL读写分离

一、MySQL读写分离中间件

• mysql-proxy
• mycat
• maxscale

二、maxscale读写分离配置

2.1、配置主从+主从

• 配置主从服务
• 安装maxscale

rpm -ivh maxscale..rpm

2.2、改配置

# 开启多少个线程
[maxscale]
threads=1/auto

# 提供服务的主机，有几个写几个
[server1]
type=server
address=192.168.168.60
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.168.61
port=3306
protocol=MySQLBackend

#监控服务，配置一个监控用户，监控谁是主，谁是从
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1, server2
user=scalemon  
passwd=123456
monitor_interval=10000
#每10s连接一次查看状态

#只读服务，默认开启，这个用不到
#[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=maxscale
passwd=123456
max_slave_connections=100%
# 此用户名是检查客户端使用a用户连接MySQL时，授权表中有没有a用户

#定义管理服务
[MaxAdmin Service]
type=service
router=cli

# 关闭只读监听端口
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008

#定义读写分离监听的端口
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

#定义管理服务的监听端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

2.3、MySQL主库中添加授权用户

# 添加监控用户：监控那台是主
mysql> grant replication slave,replication client on *.* \
to  scalemon@'%' identified by "123456";

添加路由用户：监控登录MySQL的用户和密码是否正确
mysql> grant select on mysql.* to maxscale@"%" identified by "123456"; 

2.4、启动maxscale服务

[root@maxscale ~]# maxscale -f  /etc/maxscale.cnf
[root@maxscale ~]# ps -C  maxscale        //查看进程
  PID TTY          TIME CMD
17930 ?        00:00:00 maxscale   

[root@maxscale ~]# netstat  -antup | grep maxscale      //查看端口
tcp6       0      0 :::4099                 :::*                    LISTEN      17930/maxscale      
tcp6       0      0 :::4006                 :::*                    LISTEN      17930/maxscale

2.5、测试登录并查看监控状态

[root@62 ~]# maxadmin -uadmin -pmariadb -P4099
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.168.60  |  3306 |           0 | Master, Running
server2            | 192.168.168.61  |  3306 |           0 | Slave, Running

2.6、客户端登录验证

client ~]# mysql -h maxscale主机IP -u -p -P4006
MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| 61         |
+------------+