maxscale实现读写分离

一、           maxscale简述

1.   maxscale简述

MaxScale是maridb开发的一个mysql数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。在MHA架构上，主库宕掉后，其中从库提升为主，maxscale可以自动判断新主库，而使新主库承担写服务，也可实现从库的宕机自动离线恢复后自动上线功能。

2.   目录结构说明

目录描述	目录名
Maxscale配置文件	/etc/maxscale.cnf
Maxscale日志文件	/var/log/maxscale/ maxscale1.log

3.   MySQL启动用户

root

二、           安装maxscale

1.   将安装包和安装脚本上传到服务器/opt/software下,或者直接下载

# wget https://downloads.mariadb.com/enterprise/t6c0-wrnh/mariadb-maxscale/1.3.0/rhel/7/x86_64/maxscale-1.3.0-1.rhel7.x86_64.rpm

# ls -l

-rw-r--r-- 1 root root  39413772 Feb 8 22:11 maxscale-1.3.0-1.rhel7.x86_64.rpm

2.   执行安装命令

# rpm-ivhmaxscale-1.3.0-1.rhel7.x86_64.rpm

   1:maxscale-1.3.0-1                ################################# [100%]

三、           修改配置文件并启动

1.   在数据库中创建用户并授予相关权限

mysql>grant select,update,delete,insert on db.* to ‘user’@’%’ identified by‘password’;

mysql>flush privileges;

2.   修改配置文件

/etc/maxscale.cnf

[maxscale]

threads=8

 

[server1]#MySQL服务器配置

type=server

address=10.11.98.1

port=3306

protocol=MySQLBackend

 

[server2]

type=server

address=10.11.98.2

port=3306

protocol=MySQLBackend

 

[server3]

type=server

address=10.11.98.3

port=3306

protocol=MySQLBackend

 

 

[MySQLMonitor] #监控配置

type=monitor

module=mysqlmon

servers=server1,server2,server3

user=repl

passwd=repl4slave

monitor_interval=10000

 

#[Read-OnlyService]

#type=service

#router=readconnroute

#servers=server2,server3

#user=

#passwd=

#router_options=slave

 

[Read-WriteService] #读写分离配置

type=service

router=readwritesplit

servers=server1,server2,server3

user=masterha

passwd=masterha

max_slave_connections=100%

max_slave_replication_lag=10

 

[MaxAdminService]

type=service

router=cli

 

#[Read-OnlyListener]

#type=listener

#service=Read-OnlyService

#protocol=MySQLClient

#port=4008

 

[Read-WriteListener] #读写分离服务监听配置，端口可改

type=listener

service=Read-WriteService

protocol=MySQLClient

port=4005

 

[MaxAdminListener]

type=listener

service=MaxAdminService

protocol=maxscaled

port=6603

3.   启动服务

# systemctl start maxscale.service

#检查端口监听状态

# netstat-lntp |grep maxscale

tcp        0     0 0.0.0.0:6603            0.0.0.0:*               LISTEN      28352/maxscale     

tcp        0     0 0.0.0.0:4005           0.0.0.0:*               LISTEN      28352/maxscale     

 

四、           日常维护命令

1.   查看当前服务器

#maxadmin list servers #默认密码为mariadb

Password:

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server             | Address         | Port | Connections | Status             

-------------------+-----------------+-------+-------------+--------------------

server1            | 10.11.98.1      | 3306 |           0 | Master,Running

server2            | 10.11.98.2      | 3306 |           0 | Slave,Running

server3            | 10.11.98.3      | 3306 |           0 | Slave,Running

-------------------+-----------------+-------+-------------+--------------------

2.   查看当前服务

#maxadmin list services

Password:

Services.

--------------------------+----------------------+--------+---------------

ServiceName              | Router Module        | #Users | Total Sessions

--------------------------+----------------------+--------+---------------

Read-WriteService        | readwritesplit       |     1 |  8751

MaxAdminService          | cli                  |      2 |    5

--------------------------+----------------------+--------+---------------

3.   查看当前监听端口

#maxadmin list listeners

Password:

Listeners.

---------------------+--------------------+-----------------+-------+--------

ServiceName         | Protocol Module    | Address         | Port | State

---------------------+--------------------+-----------------+-------+--------

Read-WriteService   | MySQLClient        | *               |  4005 | Running

MaxAdminService     | maxscaled          | *               |  6603 | Running

---------------------+--------------------+-----------------+-------+--------

4.   剔除与添加服务器

MaxScale>set server server3 maintenance #设置服务器为维护状态

MaxScale>clear server server3 maintenance #设置服务器上线