一.maxscale简介
1.MaxScale是maridb开发的一个mysql数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。
二.安装
1.安装可以通过rpm安装
2.可以直接下载文件放入指定目录，本次采用第二种方法
#tar zxvf maxscale-2.2.0.rhel.6.tar.gz
#mkdir /usr/local/maxscale
#mv ./maxscale-2.2.0/* /usr/local/maxscale/
#cp /usr/local/maxscale/etc/maxscale.cnf.template /etc/maxscale.cnf
三.账户配置
1.在主从库上授权两个账户
a.监视账户
create user maxscale_monitor@'100.100.100.%' identified by "123456";
grant replication slave, replication client on *.* to maxscale_monitor@'100.100.100.%';
b.路由账户
create user maxscale_route@'100.100.100.%' identified by "123456";
GRANT SELECT ON mysql.user TO maxscale_route@'100.100.100.%';
GRANT SELECT ON mysql.db TO maxscale_route@'100.100.100.%';
GRANT SELECT ON mysql.tables_priv TO maxscale_route@'100.100.100.%';
GRANT SHOW DATABASES ON *.* TO maxscale_route@'100.100.100.%';
四.读写分离配置
vi /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=1
log_info=1
logdir=/tmp/
# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=100.100.100.20
port=3306
protocol=MySQLBackend
[server2]
type=server
address=100.100.100.21
port=3306
protocol=MySQLBackend
#[server3]
#type=server
#address=
#port=3306
#protocol=MySQLBackend
# 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/master/Documentation/Monitors/MySQL-Monitor.md
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=maxscale_monitor
passwd=123456
monitor_interval=10000
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
# [Read-Only Service]
# type=service
# router=readconnroute
# servers=server1
# user=myuser
# passwd=mypwd
# router_options=slave
# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale_route
passwd=123456
max_slave_connections=100%
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/master/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
五.启动
1./usr/local/maxscale/bin/maxscale start
2.查看状态(以下的user和password均为默认账号密码):
/usr/local/maxscale/bin/maxadmin -S /tmp/maxadmin.sock
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 100.100.100.20 | 3306 | 0 | Master, Running
server2 | 100.100.100.21 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
测试：
mysql -h 100.100.100.22 -P 4006 -usystem -poralinux
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql02 |
+------------+
1 row in set (0.00 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql02 |
+------------+
1 row in set (0.00 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql02 |
+------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql01 |
+------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql02 |
+------------+
1 row in set (0.00 sec)
mysql>
更多帮助可以使用 help命令来获得
如果想要更改maxadmin的密码在配置文件中加上
[MaxAdmin Service]
type=service
router=cli
user=maxscale
passwd=123456
3.设置服务器维护状态
MaxScale> set server server2 maintenance
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 100.100.100.20 | 3306 | 0 | Master, Running
server2 | 100.100.100.21 | 3306 | 0 | Maintenance, Slave, Running
-------------------+-----------------+-------+-------------+--------------------
4.清除维护状态
MaxScale> clear server server2 maintenance
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 100.100.100.20 | 3306 | 0 | Master, Running
server2 | 100.100.100.21 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
六.只读,按一定比例分摊读
1.配置只读服务
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2
user=maxscale_route
passwd=123456
weightby=serv_weight
server1和server2增加分摊比例
[server1]
type=server
address=100.100.100.20
port=3306
protocol=MySQLBackend
serv_weight=3
[server2]
type=server
address=100.100.100.21
port=3306
protocol=MySQLBackend
serv_weight=2
2.配置端口
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
3.重新启动
七.filter
1.读写分离,如果slave没有问题,默认读全部走slave,但有时候我们需要能够读走master,这时候就需要配置filter
在读写分离项中配置,多个filter之前用'|'号分开
filters=Hint
[Hint]
type=filter
module=hintfilter
重启maxscale
应用在sql查询中:
select * from table where id=10; -- maxscale route to master
将使读走master
2.有时候我们希望一个数据表能到达指定服务器,则可以这样配置
[tableFilter]
type=filter
module=namedserverfilter
match=table_name1|table_name2|table_name3
options=ignorecase,extended #忽略大小写,并启用扩展配置,这将使用正则表达式
server=server1
ps:以上options中按文档应该ignorecase,extended两者都应该生效,但实际测试是发现前面的生效,后面的就不生效
读写分离路由中配置
filters=tableFilter
这将使带有table_name1,table_name2,table_name3的查询或更新,全部到达server1