用Maxscale做mysql的读写分离

hello11

MaxScale是什么？   
    MaxScale是一个支持高可用，负载均衡，具有良好的可扩展性，高性能的基于事件驱动的同时具有代理和管理功能的中间件。之前也尝试过mycat,相比mycat,maxscale是mariadb官方提供的，配置起来简单，配置文件也是类似mysql，不像mycat的配置文件，看的我眼花缭乱，当然这个也是各有所好。虽然mycat社区很活跃，但我更看好maxscale的发展前景。

MYSQL主从配置就略过了，这边主要讲下maxscale的配置

实验环境

192.168.137.11 (Maxscale)
192.168.137.21:3306 (Master)
192.168.137.22:3306 (Slave)
192.168.137.23:3306 (Slave)

在开始配置之前，需要在 master 库中为MaxScale 创建两个用户，用于监控模块和路由模块。（也可以用一个用户同时监控和路由）

创建监控用户，用于[MySQL Monitor]段的配置中：
mysql> create database maxscale_schema ;    # maxscale监控用的心跳信息会写到这个库里面
mysql> create user scalemon@‘%‘ identified by"111111";
mysql> grant replication slave, replication client on*.* to scalemon@‘%‘;
mysql> grant all on maxscale_schema.* to scalemon@‘%‘;

创建路由用户 ，用于[Read-Write Service]和[Read-Only Service]段的配置中：
mysql> create user scaleroute@'%' identified by "123456";
mysql> grant select on mysql.* to scaleroute@'%';


安装 Maxscale
rpm -ivh maxscale-2.1.4-1.rhel_6.x86_64.rpm
mkdir -p /maxscale/cache   
mkdir -p /maxscale/data   
mkdir -p /maxscale/log   
mkdir -p /maxscale/pid   
mkdir -p /maxscale/tmp
chown -R maxscale.maxscale /maxscale


配置 Maxscale ，vim  /etc/maxscale.cnf

[maxscale]
# 开启线程个数，默认为1.设置为auto会同cpu核数相同
threads=auto
# timestamp精度
ms_timestamp=1
# 将日志写入到syslog中
syslog=1
# 将日志写入到maxscale的日志文件中
maxlog=1
# 不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_to_shm=0
# 记录告警信息
log_warning=1
# 记录notice
log_notice=1
# 记录info
log_info=1
# 不打开debug模式
log_debug=0
# 日志递增
log_augmentation=1

# 相关目录设置
logdir=/maxscale/log/
datadir=/maxscale/data/
libdir=/usr/lib64/maxscale/
cachedir=/maxscale/cache/
piddir=/maxscale/pid/
execdir=/usr/bin/

[server1]
type=server
address=192.168.137.21
port=3306
protocol=MySQLBackend
serv_weight=1

[server2]
type=server
address=192.168.137.22
port=3306
protocol=MySQLBackend
serv_weight=3

[server3]
type=server
address=192.168.137.23
port=3306
protocol=MySQLBackend
serv_weight=3



#相关的监控信息，监控的用户需要对后端数据库有访问replication client的权限：grant replication client
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=scalemon
passwd=mypasswd
# 监控心态为 1s
monitor_interval=1000
# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true

#读写分离，用户需要有SELECT ON mysql.db；SELECT ON mysql.tables_priv；SHOW DATABASES ON *.*的权限  
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=scaleroute
passwd=mypasswd
max_slave_connections=100%
# sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
# 允许root用户登录执行
enable_root_user=1
# 允许主从最大间隔(s),必须比monitor的interval大
max_slave_replication_lag=5
#master节点也可以转发读请求
master_accept_reads=true


[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=/u01/maxscale/tmp/maxadmin.sock
port=6603

[server1], [server2], [server3]  配置了三个Maxscale需要连接的MySQL服务
[MySQL Monitor] 配置一个监听服务, 同时监听着 [server1], [server2], [server3] 的状态
[Read-Only Service] 配置了只读服务, 只在[server2], [server3]中执行
注意: 虽然是只读服务但是同样可以执行 DML DDL, 说以要限制好用户的权限.
[Read-Write Listener] 配置了读写分离的服务
[MaxAdmin Listener] 配置了用户管理Maxscale的服务



1.启动 Maxscale
maxscale -f /etc/maxscale.cnf

2.使用 maxadmin 查看服务
maxadmin -S /u01/maxscale/tmp/maxadmin.soc

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.137.21  |  3306 |           0 | Master, Running
server2            | 192.168.137.22  |  3306 |           0 | Slave, Running
server3            | 192.168.137.23  |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> list services
Services.
--------------------------+----------------------+--------+---------------
Service Name              | Router Module        | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Only Service         | readconnroute        |      1 |     1
Read-Write Service        | readwritesplit       |      1 |     1
MaxAdmin Service          | cli                  |      3 |     3
--------------------------+----------------------+--------+---------------




验证读写分离

通过登录Maxscale的读写分离服务, 来执行sql并且查看日志，查看日志路由情况。

[iyunv@normal_11 opt]# mysql -uHH -p -h192.168.137.11 -P4006
Logging to file '/u01/mysql_history/query.log'
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5524
Server version: 5.5.5-10.0.0 2.0.1-maxscale MariaDB Server

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


HH@192.168.137.11 11:13:46 [(none)]>SELECT * FROM test.t1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | HH1  |   1 |
|  2 | HH2  |   2 |
|  3 | HH3  |   3 |
+----+------+-----+
3 rows in set (0.01 sec)

HH@192.168.137.11 11:15:03 [(none)]>INSERT INTO test.t1 VALUES(NULL, 'HH4', 4);
Query OK, 1 row affected (0.01 sec)

# 使用 HH 登录成的日志
2016-11-03 23:13:46.907   info   : (log_server_connections): Servers and router connection counts:
2016-11-03 23:13:46.907   info   : (log_server_connections): current operations : 0 in  192.168.137.21:3306 RUNNING MASTER
2016-11-03 23:13:46.907   info   : (log_server_connections): current operations : 0 in  192.168.137.22:3306 RUNNING SLAVE
2016-11-03 23:13:46.907   info   : (log_server_connections): current operations : 0 in  192.168.137.23:3306 RUNNING SLAVE
2016-11-03 23:13:46.908   info   : (select_connect_backend_servers): Selected RUNNING MASTER in         192.168.137.21:3306
2016-11-03 23:13:46.908   info   : (select_connect_backend_servers): Selected RUNNING SLAVE in  192.168.137.22:3306
2016-11-03 23:13:46.908   info   : (select_connect_backend_servers): Selected RUNNING SLAVE in  192.168.137.23:3306
2016-11-03 23:13:46.908   info   : (session_alloc): Started Read-Write Service client session [0] for 'HH' from 192.168.137.11
2016-11-03 23:13:46.909   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1
2016-11-03 23:13:46.909   [9]  info   : (route_single_stmt): Route query to master      192.168.137.21:3306 <
2016-11-03 23:13:46.922   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select USER()
2016-11-03 23:13:46.922   [9]  info   : (route_single_stmt): Route query to slave       192.168.137.22:3306 <

# 执行 SELECT * FROM test.t1 语句被路由到 192.168.137.22:3306[server2]中的日志
2016-11-03 23:15:02.618   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT * FROM test.t1
2016-11-03 23:15:02.618   [9]  info   : (route_single_stmt): Route query to slave       192.168.137.22:3306 <

# 执行 INSERT INTO test.t1 VALUES(NULL, 'HH4', 4) 语句被路由到 192.168.137.21:3306[server1]中的日志
2016-11-03 23:17:02.716   [9]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test.t1 VALUES(NULL, 'HH4', 4)
2016-11-03 23:17:02.716   [9]  info   : (route_single_stmt): Route query to master      192.168.137.21:3306 <

ysbxm

非常的好，好到非常啊

ysbxm

看起来不错啊