CNDBA社区
1 MaxScale介绍
MaxScale是MariaDB开发的一个数据库智能代理服务，其允许根据数据库 SQL 语句将请求转向到一个或者多个服务器，可设定各种复杂程度的转向规则。MaxScale 设计用于透明的提供数据库的负载均衡和高可用性，同时提供高度可伸缩和灵活的架构，支持不同的协议和路由决策。使用MaxScale可以轻松解决mariaDB读写分离的问题。
2 环境说明
| 机IP | 角色 | Server-id | 作系统版本 |
|---|---|---|---|
| 192.168.56.2 | Master | 2 | Redhat6.7 |
| 192.168.56.3 | Slave | 3 | Redhat6.7 |
| 192.168.56.4 | Slave | 4 | Redhat6.7 |
| 192.168.56.5 | Maxscale | Redhat6.7 |
3 安装配置MaxScale
3.1 安装
下载地址：
https://mariadb.com/downloads/mariadb-tx/maxscale
一般建议将MaxScale安装到独立的服务器上。我们这里测试直接安装到Master库上：
[dave@www.cndba.cn_MaxScale data]# rpm -ivh maxscale-2.1.13-1.rhel.6.x86_64.rpm
warning: maxscale-2.1.13-1.rhel.6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 28c12247: NOKEY
Preparing... ########################################### [100%]
1:maxscale ########################################### [100%]
[root@Dave data]#
3.2 创建所需目录
mkdir -p /maxscale/cache
mkdir -p /maxscale/data
mkdir -p /maxscale/log
mkdir -p /maxscale/pid
mkdir -p /maxscale/tmp
3.3 创建用户
在master库执行：
create user 'maxscale'@'%' identified by 'maxscale';
grant select on mysql.user to 'maxscale'@'%';
grant select on mysql.db to 'maxscale'@'%';
grant all on *.* to 'maxscale'@'%';
grant show databases on *.* to 'maxscale'@'%';
grant replication client on *.* to 'maxscale'@'%';
grant replication slave, replication client,select on *.* to maxscale@'%';
3.4 配置MaxScale
详细参数说明参考官方文档：
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-mariadb-maxscale-configuration-usage-scenarios/
编辑配置文件/etc/maxscale.cnf
[root@MariaDB-Master data]# cat /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=auto
ms_timestamp=1 #timestamp精度
syslog=1 #将日志写入到syslog中
maxlog=1 #将日志写入到maxscale的日志文件中
log_to_shm=0 #不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_warning=1 #记录告警信息
log_notice=1 #记录notice
log_info=1 #记录info
log_debug=0 #不打开debug模式
log_augmentation=1 #日志递增
logdir=/maxscale/log/
datadir=/maxscale/data/
libdir=/usr/lib64/maxscale/
cachedir=/maxscale/cache/
piddir=/maxscale/pid/
execdir=/usr/bin/
# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=192.168.56.2
port=3306
protocol=MySQLBackend
#serv_weight=1
[server2]
type=server
address=192.168.56.3
port=3306
protocol=MySQLBackend
serv_weight=2
[server3]
type=server
address=192.168.56.4
port=3306
protocol=MySQLBackend
serv_weight=8
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md
#相关的监控信息，监控的用户需要对后端数据库有访问replication client的权限：grant replication client
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale
passwd=maxscale
monitor_interval=1000 #监控心跳为1秒
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md
#read-only的只读节点slave分离
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale
passwd=maxscale
router_options=slave #查询负载会在slave类型的节点上根据权重分配的比率进行分配。 如果没有指定权重参数，就按照负载均衡的方式进行。
enable_root_user=1
weightby=serv_weight
# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=maxscale
use_sql_variables_in=master
enable_root_user=1
max_slave_replication_lag=3600
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md
[MaxAdmin Service]
type=service
router=cli
# 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=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
3.5 加密密码
配置文件中的密码都是经过maxscale进行加密后的，可以防止密码泄露，具体的操作步骤为
在刚才配置文件中的datadir目录下创建加密文件
[dave@www.cndba.cn_MaxScale data]# maxkeys /maxscale/data
生成加密后的密码
[dave@www.cndba.cn_MaxScale data]# maxpasswd /maxscale/data/ maxscale
2096D75BA8575B4FA2336CF848581FA4
这个就是对maxscale加密后的密码， 将这段加密后的密码添加到/etc/maxscale.cnf文件的passwd选项中：
passwd=2096D75BA8575B4FA2336CF848581FA4
3.6 启动MaxScale
maxscale -f /etc/maxscale.cnf
如果启动失败，查看log文件：
[dave@www.cndba.cn_MaxScale log]# pwd
/maxscale/log
[dave@www.cndba.cn_MaxScale log]# ls
maxscale.log
[dave@www.cndba.cn_MaxScale log]#
3.7 查看MaxScale 状态
[dave@www.cndba.cn_MaxScale log]# maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.56.2 | 3306 | 0 | Master, Running
server2 | 192.168.56.3 | 3306 | 0 | Slave, Running
server3 | 192.168.56.4 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
[dave@www.cndba.cn_MaxScale log]#
3.8 应用
MaxScale本质上是一个中转服务器，配置之后，所有的web都需要连接到MaxScale服务器，而不是具体的DB服务器。 这里连接还是通过mysql命令进行连接。 但需要指定对应的端口和IP地址。 比如我们读写分离的端口是4006，我们MaxScale服务器的IP地址是：192.168.56.5。
那么对应的连接方式就是：
mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;"
4 验证读写分离
4.1 查看Read-Only Service
MaxScale> show service "Read-Only Service"
Service: Read-Only Service
Router: readconnroute
State: Started
Number of router sessions: 0
Current no. of router sessions: 1
Number of queries forwarded: 0
Connection distribution based on serv_weight server parameter.
Server Target % Connections
server1 100.0% 0
server2 20.0% 0
server3 80.0% 0
Started: Wed Feb 21 20:35:19 2018
Root user access: Enabled
Backend databases:
[192.168.56.2]:3306 Protocol: MySQLBackend Name: server1
[192.168.56.3]:3306 Protocol: MySQLBackend Name: server2
[192.168.56.4]:3306 Protocol: MySQLBackend Name: server3
Routing weight parameter: serv_weight
Total connections: 1
Currently connected: 1
MaxScale>
4.2 验证
读写分离的验证不太好测试，所以我们这里直接进行插入和查询，然后查看MaxScale的日志进行对比。
首先连接到MaxScale服务器，然后进行操作，注意这里的端口和服务器IP地址：
[dave@www.cndba.cn_3 ~]# mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5
Welcome to the MariaDB monitor. Commands end with ; or /g.
Your MySQL connection id is 27593
Server version: 10.0.0 2.1.13-maxscale MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
MySQL [(none)]> use dave
Database changed
MySQL [dave]> create table cndba(id int,content char(100));
Query OK, 0 rows affected (0.03 sec)
MySQL [dave]> insert into cndba values(2,'http://www.cndba.cn/dave');
Query OK, 1 row affected (0.00 sec)
MySQL [dave]> select * from cndba;
+------+--------------------------+
| id | content |
+------+--------------------------+
| 1 | www.cndba.cn |
| 2 | http://www.cndba.cn/dave |
+------+--------------------------+
2 rows in set (0.00 sec)
[dave@www.cndba.cn_MaxScale log]# tail -10 maxscale.log
2018-02-21 21:03:53.912 info : (42) [readwritesplit] (log_transaction_status): > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from cndba
2018-02-21 21:03:53.912 info : (42) [readwritesplit] (handle_got_target): Route query to slave [192.168.56.3]:3306 <
2018-02-21 21:04:00.361 info : (42) [readwritesplit] (log_transaction_status): > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@hostname
2018-02-21 21:04:00.361 info : (42) [readwritesplit] (handle_got_target): Route query to slave [192.168.56.3]:3306 <
2018-02-21 21:04:09.976 info : (31) [cli] (execute): MaxAdmin: show service "Read-Only Service"
2018-02-21 21:05:52.880 info : (session_free): Stopped MaxAdmin Service client session [31]
2018-02-21 21:07:55.993 info : (42) [readwritesplit] (log_transaction_status): > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_WRITE, stmt: insert into cndba values(2,'http://www.cndba.cn/dave')
2018-02-21 21:07:55.993 info : (42) [readwritesplit] (handle_got_target): Route query to master [192.168.56.2]:3306 <
2018-02-21 21:08:00.283 info : (42) [readwritesplit] (log_transaction_status): > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from cndba
2018-02-21 21:08:00.283 info : (42) [readwritesplit] (handle_got_target): Route query to slave [192.168.56.3]:3306 <
[dave@www.cndba.cn_MaxScale log]#
5 验证负载均衡
5.1 查看Read-Write Service
[dave@www.cndba.cn_MaxScale log]# maxadmin --help
maxadmin Version 2.1.13
The MaxScale administrative and monitor client.
Usage: maxadmin [-S socket] <command>
maxadmin [-u user] [-p password] [-h hostname] [-P port] <command>
-S|--socket=... The UNIX domain socket to connect to, The default is
/tmp/maxadmin.sock
-u|--user=... The user name to use for the connection, default
is admin.
-p|--password=... The user password, if not given the password will
be prompted for interactively
-h|--host=... The maxscale host to connecto to. The default is
localhost
-P|--port=... The port to use for the connection, the default
port is 6603.
-v|--version Print version information and exit
-?|--help Print this help text.
Any remaining arguments are treated as MaxScale commands or a file
containing commands to execute.
Either a socket or a hostname/port combination should be provided.
If a port or hostname is provided, but not the other, then the default
value is used.
[dave@www.cndba.cn_MaxScale log]# maxadmin
MaxScale> show service "Read-Write Service"
Service: Read-Write Service
Router: readwritesplit
State: Started
use_sql_variables_in: master
slave_selection_criteria: LEAST_CURRENT_OPERATIONS
master_failure_mode: fail_instantly
max_slave_replication_lag: 3600
retry_failed_reads: true
strict_multi_stmt: true
strict_sp_calls: false
disable_sescmd_history: true
max_sescmd_history: 0
master_accept_reads: false
Number of router sessions: 0
Current no. of router sessions: 1
Number of queries forwarded: 0
Number of queries forwarded to master: 0 (0.00%)
Number of queries forwarded to slave: 0 (0.00%)
Number of queries forwarded to all: 0 (0.00%)
Started: Wed Feb 21 20:35:20 2018
Root user access: Enabled
Backend databases:
[192.168.56.2]:3306 Protocol: MySQLBackend Name: server1
[192.168.56.3]:3306 Protocol: MySQLBackend Name: server2
[192.168.56.4]:3306 Protocol: MySQLBackend Name: server3
Total connections: 1
Currently connected: 1
MaxScale>
5.2 验证
MaxScale的负载均衡是通过连接到MaxScale服务器，在中转到对应的Slave节点来实现的。 所以只需要找一台有mysql命令的机器，执行一下命令，创建连接，就可以知道对应的分发情况：
[dave@www.cndba.cn_1 ~]# for i in `seq 1 10`; do mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2>/dev/null & done
[1] 27797
[2] 27798
[3] 27799
[4] 27800
[5] 27801
[6] 27802
[7] 27803
[8] 27804
[9] 27805
[10] 27806
[dave@www.cndba.cn_1 ~]# +------------+
| @@hostname |
+------------+
| MariaDB_3 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_2 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_3 |
+------------+
+------------+
| @@hostname |
+------------+
+------------+
| @@hostname |
| MariaDB_2 |
+------------+
+------------+
| MariaDB_3 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_2 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_2 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_3 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_3 |
+------------+
+------------+
| @@hostname |
+------------+
| MariaDB_2 |
+------------+
[1] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[2] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[3] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[4] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[5] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[6] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[7] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[8] Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[9]- Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[10]+ Done mysql -P4006 -umaxscale -pmaxscale -h192.168.56.5 -e "select @@hostname;" 2> /dev/null
[dave@www.cndba.cn_1 ~]#
这里可以看到会话被平均分配到两个节点上，当然这里还需要根据连接的数量，才能观察到结果。 连接越多，效果越明显，如果这里只有5个连接，可能都会在一节节点上。
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：物质幸福时代已经结束，新时代来临
- 下一篇：MariaDB 入门系列 实验小结
