Maxscale 的允许对router规则施加hint,强制sql重定向到某台指定server。
这对于某些不能容忍主从延时的操作,比如创建商品订单,紧接着要查看订单内容,非常有用,可以直接hint到master进行查询,可以保持很好的用户体验。
1.准备工作
1.1开启hint模块
要使用它routing hints,需要启用 hintfilter 模块,
[Read Service]
type=service
router=readconnroute
router_options=master
servers=server1
user=maxuser
passwd=maxpwd
filters=Hint #在服务中调用此模块
[Hint]
type=filter
module=hintfilter #启动模块
为了便于观察,我们还要设置参数 log_info=1 ,方便日志输出。
1.2 测试脚本准备
这个功能在CLi看不到,需要写到程序中,我们用python演示。需要的大体环境在此简单罗列,
yum install python-devel mysql-devel zlib-devel openssl-devel gcc
mysql-connector-python-2.1.6-1.el6.i686.rpm
MySQL-python-1.2.5.zip
SQLAlchemy-1.1.9.tar.gz
测试脚本:
[root@s3 opt]# cat msHint.py
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
conf = {
'host': '192.168.1.210',
'port': 4006,
'user': 'msrw',
'passwd': '123456',
'db': 't1',
'charset': 'utf8'
}
str = ('mysql+mysqldb://{username}:{password}@{host}:{port}/{database}''?charset=utf8'.format(username = conf.get('user', ''),password = conf.get('passwd', ''),host = conf.get('host', ''),port = conf.get('port', 3306),database = conf.get('db', '')))
engine = create_engine(str)
conn = engine.connect()
sql = """
SELECT count(1) from tb; -- maxscale route to slave
"""
conn.execute("SET AUTOCOMMIT=1")
conn.execute(sql)
print "finished!"
2.hint写法
-- maxscale <hint body>
hint有两种表示方式
-- maxscale route to [master | slave | server <server name>]
-- maxscale <param>=<value>
例如
INSERT INTO table1 VALUES ("John","Doe",1);
SELECT * from table1; -- maxscale route to master #此select语句将被发往master执行
3.测试
3.1 hint 到master
将脚本中哪一行写成 SELECT count(1) from tb; -- maxscale route to master,执行脚本,查看maxscale日志初输出:
2017-05-19 13:29:18.771 [9] info : Server 'server1' responded to a session command, sending the response to the client.
2017-05-19 13:29:18.772 [9] info : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt:
SELECT * from tb; -- maxscale route to master
, Hint: HINT_ROUTE_TO_MASTER ##定向到master了
2017-05-19 13:29:18.772 [9] info : Route query to master 127.0.0.1:3306 <
2017-05-19 13:29:18.791 [9] info : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_ROLLBACK, stmt: rollback
2017-05-19 13:29:18.791 [9] info : Route query to master 127.0.0.1:3306 <
2017-05-19 13:29:20.073 [9] info : Stopped Read-Write Service client session [9]
3.2 hint到slave
将脚本中哪一行写成 SELECT count(1) from tb; -- maxscale route to slave,执行脚本,查看maxscale日志初输出:
2017-05-19 13:14:41.765 [8] info : Server 'server1' responded to a session command, sending the response to the client.
2017-05-19 13:14:41.766 [8] info : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt:
SELECT * from tb; -- maxscale route to slave
, Hint: HINT_ROUTE_TO_SLAVE ##定向到slave了
2017-05-19 13:14:41.766 [8] info : Route query to slave 127.0.0.1:3309 <
2017-05-19 13:14:41.784 [8] info : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_ROLLBACK, stmt: rollback
2017-05-19 13:14:41.784 [8] info : Route query to master 127.0.0.1:3306 <
2017-05-19 13:14:43.086 [8] info : Stopped Read-Write Service client session [8]
3.3 hint到指定server
将脚本中哪一行写成 SELECT count(1) from tb; -- maxscale route to server server3,
我的server3定义如下:
[server3]
type=server
address=127.0.0.1
port=3308
protocol=MySQLBackend
serv_weight=3
执行脚本,查看maxscale日志初输出:
2017-05-19 13:32:11.026 [12] info : Server 'server1' responded to a session command, sending the response to the client.
2017-05-19 13:32:11.027 [12] info : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt:
SELECT * from tb; -- maxscale route to server server3
, Hint: HINT_ROUTE_TO_NAMED_SERVER ##定向到了我们指定的server3上了
2017-05-19 13:32:11.027 [12] info : Hint: route to server 'server3'
2017-05-19 13:32:11.027 [12] info : Route query to slave 127.0.0.1:3308 <
2017-05-19 13:32:11.057 [12] info : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_ROLLBACK, stmt: rollback
2017-05-19 13:32:11.057 [12] info : Route query to master 127.0.0.1:3306 <
2017-05-19 13:32:12.329 [12] info : Stopped Read-Write Service client session [12]
好了,演示到此结束。