mariadb参数调整 mariadb maxscale

近期项目组在使用mycat去做mysql的读写分离时有遇到一些问题，如在要求使用useCursorFetch=true时，后端路由转发可能会不正常，为此寻找替代mycat的中间件。项目只需求库级别（不涉及到分区表）的读写分离和读的负载均衡，maxscale较为符合，记录下在Centos7下安装maxscale以及简单的读写分离测试

一、环境准备

数据库是一主两从的机构（mysql的主从搭建不在此说明），maxscale安装在主库所在服务器上（也可以不与数据库安装在同一服务器上）

hostname	ip	status
mysql-master	192.168.2.18	主库 提供写服务
mysql-slave1	192.168.2.19	从库 提供读服务
mysql-slave2	192.168.2.20	从库 提供读服务

二、下载安装

Maxscale是由MariaDB官方提供的中间件，并负责升级和维护，可在官网中进行下载，另外可以查看官方提供的详细文档，本例中所有的安装方式都是选用maxscale-2.4版本。

1、以yum的方式安装

在网络条件运行的情况下，我会优先选用yum的方式进行安装部署，对此官网也提供了详细的说明文档：MariaDB Package Repository Setup and Usage，这里做简单的记录。

• 下载yum源文件

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup |
    sudo bash -s -- --mariadb-maxscale-version="2.4"  --os-type=rhel --os-version=7

正常完成会有如下提示：

• 确认下源文件中有所需的maxscale
  yum search maxscale
• 下载安装
  yum install maxscale
• 启动
  systemctl start maxscale

2、以rpm的方式安装

• 下载安装rpm包
  官网上下载自己所需版本的安装包。
• 安装
  rpm -ivh maxscale-2.4.13-1.rhel.7.x86_64.rpm

[NOTE]
我的环境是直接安装成功了，有些环境可能会报错提示需要安装一些依赖，这里也说明一下：yum install libcurl libaio OpenSSL gnutls

• 启动
  CentoOS7下使用rpm方式安装完成后会自己创建对应的maxscale.service文件，以及配置文件默认在/etc/maxscale.cnf，直接使用systemctl方式启动即可：
  systemctl start maxscale

3、以编译的方式安装

• 官网提供了编译源码的方式进行安装，具体安装过程可以参考官方文档：Building MariaDB MaxScale from Source Code

4、直接下载文件部署使用

• 另外还提供了压缩包，下载解压后可直接使用，当然也需要按照要求进行相应的配置，具体安装过程可以参考官方文档：Installing MariaDB MaxScale using a tarball

三、参数配置

• 以yum或rpm方式安装的maxscale，默认的参数文件是/etc/maxscale.cnf，也可以在启动的时候使用-f参数指定参数文件，如maxscale -f /u01/maxscale.cnf。每个版本的参数文件会所有不同，官网提供了详细的参数文件说明，本例记录了一些项目所需的参数说明。
• 先看一份配置好的参数文件

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-maxscale-configuration-guide/

[maxscale]
threads=auto
# log
syslog=0
# syslog=<0|1> 是否将log记录到/var/log/messages中       
maxlog=1
# maxlog=<0|1> 将日志记录到file，默认在/var/log/maxscale/maxscale.log
log_info=1
# <0|1> 设置日志的级别为info
# logdir=/tmp
# 日志的存储位置，不设置的话默认在/var/log/maxscale/，需要对目录有读写权限

# datadir=
# 数据文件存储目录，默认在/var/lib/maxscale     
# piddir=
# pid文件存储目录，默认在/var/run/maxscale     

# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#

[server1]
type=server
address=192.168.2.18
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.2.19
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=192.168.2.20
port=3306
protocol=MariaDBBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=scalemon
password=123456
monitor_interval=2000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24-readconnroute/

# [Read-Only-Service]
# type=service
# router=readconnroute
# servers=server1
# user=myuser
# password=mypwd
# router_options=slave

# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24-readwritesplit/

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

# connection_timeout=300
# max_connections=100

max_slave_replication_lag=60
# 允许主从最大延迟（s），超过此数值从库不接受读请求    

# master_reconnection=1
# master_failure_mode=error_on_write

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

# [Read-Only-Listener]
# type=listener
# service=Read-Only-Service
# protocol=MariaDBClient
# port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

• 参数文件中主要是5个模块，Global parameters、Server definitions、Monitor for the servers、Service definitions和Listener definitions for the services。
• Global parameter是全局参数，上述文件中主要对线程数和日志做了设置

• threads=auto表示跟CPU核数一样也可以自定义其他数值；
• 对于log日志，只需要记录在专门的日志文件即可，无需在syslog再记录一份，日志级别需要查看每条查询的路由情况可以把info级别开启，后续需要注意日志文件逐渐变大，可做转储；
• 其余参数不进行配置使用默认的即可。

• Server definitions配置了后端数据库，参考上述文件即可，其中protocol目前只有MariaDBBackend协议。
• Monitor for the servers是配置监控后端数据库的相关参数，其中有几种模式，这里采用的是mariadbmon

• servers的值对应上一个模块中后端数据库，用逗号隔开；
• monitor_interval设置的是监控频率，单位是ms，默认是2000ms；
• user和password是监控数据库时所用到的数据库用户和密码，此用户需要在数据库上创建并赋权，需要replication和client权限
  grant replication client on *.* to to scalemon@'%' identified by '123456';

• Service definitions是配置我们所用的服务策略，如readconnroute和readwritesplit等，项目主要是需要读写分离，即readwritesplit。

• router设置成readwritesplit；
• max_slave_connections设置成100%，表示读负载到所有的slave数据库
• max_slave_replication_lag设置的是从库最大延迟，单位是秒，即当从库的延迟大于所设置的值时，读请求不再路由到此从库上
• user和password是路由账号，maxscale使用该账号将不同的请求分发到不同的节点上。当客户端连接到maxscale这个节点上时，maxscale节点会使用该账号去查后端数据库，检查客户端登陆的用户是否有权限或密码是否正确等等；
  grant select on mysql.* to maxscale@'%' identified by '123456';grant show databases on *.* to maxscale@'%' identified by '123456';
• master_reconnection和master_failure_mode是设置主库宕机下的配置
  master_failure_mode有三个值，fail_instantly、fail_on_write和error_on_write，默认是fail_instantly即当主库宕机时，立即关闭连接并不再接受新连接；fail_on_write和error_on_write分别代表是主库宕机的情况下，当有写请求连接来时，分别断开连接和报错，即主库宕机时只接受只读连接；如果主库恢复了，只读连接在不断开的情况下变成读写连接的话，就需要master_reconnection设置成1，即enable。

• Listener definitions for the services用于配置上述服务策略的监听

• service设置成上述服务策略中的对应名称
• protocol目前只支持MariaDBClient
• port监听的端口，读写分离默认的是4006，只读默认的是4008。

四、命令说明

1、maxscale

• 用于启动maxscale，可以添加参数，maxscale --help
• 以yum和rpm方式安装，有些默认的参数如下：

config file       : /etc/maxscale.cnf
configdir         : /etc
logdir            : /var/log/maxscale
cachedir          : /var/cache/maxscale
libdir            : /usr/lib64/maxscale
datadir           : /var/lib/maxscale
execdir           : /usr/bin
language          : /var/lib/maxscale
piddir            : /var/run/maxscale
persistdir        : /var/lib/maxscale/maxscale.cnf.d
module configdir  : /etc/maxscale.modules.d
connector plugins : /usr/lib64/mysql/plugin

2、maxctrl

• 在老的版本中是maxadmin，2.4及之后的版本开始统一使用maxctrl命令来操作maxscale，先看下基本格式maxctrl --help

查看具体命令的使用方法，如查看list命令的使用方法：maxctrl help list

• 查看后端数据库
  maxctrl list servers
• 查看maxscale的服务策略
  maxctrl list services

[NOTE]
更多的命令可以自行结合help命令去尝试。

五、简单的测试

1、读写分离测试

• 创建一个用于测试的数据库用户：grant all on *.* to scott@'%' identified by 'tiger';
• 通过maxscale访问数据库：mysql -uscott -ptiger -h192.168.2.18 -P4006
• 验证读写分离

可以看到读的请求发送到了从库上，写的请求发送到的主库上，如果log日志开启了info级别，在日志中也可以清楚看到路由情况。

2、只读从库负载均衡测试

• 开启三个窗口执行：mysql -uscott -ptiger -h192.168.2.18 -P4006 -BNe "select @@hostname;select sleep(100);"

可以看到三次读请求分别路由到了slave1、slave2、slave1上，实现了负载均衡

[NOTE]
对于负载均衡还可以设置权重，具体的设置方法可以参考官方文档进程参数调整。

3、从库延迟配置测试

• 开启从库延迟限制，即设置max_slave_replication_lag=60
• 在slave1上执行：flush table with read lock;
• 在master上创建一个test数据库，并创建一张t1表，插入一条数据
• 通过maxscale连接数据库查询：mysql -uscott -ptiger -h192.168.2.18 -P4006 -BNe "select @@hostname;"

一分钟后再次执行上述语句：

可以看到slave1上的延迟超过60s，读请求不再发送到slave1，而且路由到了没有延迟的slave2上。在slave1上解除表锁unlock tables;后slave1重新接受读请求。

4、主库从库分别宕机测试

• 单独关闭slave1，模拟一台从库宕机
• 继续关闭slave2，模拟两台从库都宕机

读查询路由到了主库中，老的版本可能还需要配置detect_stale_master=true，2.4版本已经不再需要配置此参数。

• 开启两台从库，然后关闭主库

• 直接关闭主库，即默认master_failure_mode=fail_instantly
• 设置master_reconnection=1,master_failure_mode=error_on_wirte后关闭主库，然后再开启主库

可以看到通过maxscale还是可以连接数据库，并且可以执行只读查询，当要进行写操作时，有报错提示，说明此时数据库在只读模式下，然后重启主库之后，直接恢复，无需重新开个连接。

• 设置master_reconnection=1,master_failure_mode=fali_on_wirte后关闭主库，然后再开启主库

一样可以通过maxscal连接数据库，并且可以执行只读查询，当要进行写操作时，是fail的报错，然后重启主库之后，再进行写操作时，会自己重连主库，也是无需重新卡个新的连接。

六、其他

• maxscale配置简单，能实现读写分离和读的负载均衡，但是本身没有高可用的功能，不能提供数据库的高可用，本身节点也要配合类似keepalived才能实现高可用。
• 测试中发现只要开启事务，就会路由到主库上，据了解java中对事务本身可以定义只读事务、读写事务等，当然理想情况下希望只读事务也可以路由到从库中执行，这一点还需要再做测试