配置数据库读写分离服务器

配置一主一从 主从同步结构，并在客户端测试配置
master51> grant all on webdb.* to webuser@"%" identified by " 123456";

装包
rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

[root@host56 ~]# rpm -qa | grep maxscale
maxscale-2.1.2-1.x86_64

[root@host56 ~]# rpm -qc maxscale
/etc/maxscale.cnf.template

[root@host56 ~]#
修改配置文件
[root@host56 ~]# ls /etc/maxscale.cnf
/etc/maxscale.cnf

[root@host56 ~]# cp /etc/maxscale.cnf /etc/maxscale.cnf.bak

[root@host56 ~]# vim /etc/maxscale.cnf
[maxscale]
threads=1　　也可是是auto
[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
user=scalemon
passwd=123456
monitor_interval=10000

[Read-Write Service]
type=service
router=readwritesplit
servers=server1, server2
user=maxscale
passwd=123456
max_slave_connections=100%

[MaxAdmin Service]
type=service
router=cli

[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=4018

根据配置文件做相应的设置(在2台数据库服务器上添加用户)
监控数据库服务器时，连接数据库服务器的用户
mysql> grant replication slave, replication client on 星．星　to scalemon@'%' identified by "123456"；

验证 访问数据时，连接数据库服务器使用的用户，是否在数据库服务器上存在的，连接用户
mysql> grant select on mysql.* to maxscale@'%' identified by "123456";

查看授权用户
mysql> select user,host from mysql.user where user in ("scalemon","maxscale");
+----------+------+
| user | host |
+----------+------+
| maxscale | % |
| scalemon | % |
+----------+------+

启动服务
[root@host56 ~]# maxscale -f /etc/maxscale.cnf

查看服务进程和端口

查看端口
[root@host56 ~]# netstat -utnlp | grep :4006
[root@host56 ~]# netstat -utnlp | grep :4018
[root@host56 ~]# ps -C maxscale

停止服务
[root@host56 ~]# ps -C maxscale
PID TTY TIME CMD
29688 ? 00:00:00 maxscale
[root@host56 ~]# kill -9 29688
[root@host56 ~]#
[root@host56 ~]#
[root@host56 ~]# kill -9 29688
-bash: kill: (29688) - 进程不存在

2.2.3 测试配置
A 在本机访问管理管端口查看监控状态

[root@host56 ~]# maxadmin -P4018 -uadmin -pmariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.4.51 | 3306 | 0 | Master, Running
server2 | 192.168.4.52 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> exit
[root@host56 ~]#

b 客户端访问数据读写分离服务器
]#which mysql
]# mysql -h292.168.4.56 -P4006 -uwebuser -p123456
mysql> select @@hostname；
mysql> 执行插入或查询 （ 在51 和 52 本机查看记录）