Maxscale 的允许对router规则施加hint，强制sql重定向到某台指定server。

这对于某些不能容忍主从延时的操作，比如创建商品订单，紧接着要查看订单内容，非常有用，可以直接hint到master进行查询，可以保持很好的用户体验。

1.准备工作

1.1开启hint模块

要使用它routing hints，需要启用  hintfilter 模块，

1
2
3
4
5
6
7
8
9
10
11
12
[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演示。需要的大体环境在此简单罗列，

1
2
3
4
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

测试脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[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写法

1
2
3
4
-- maxscale <hint body>
hint有两种表示方式
-- maxscale route to [master | slave | server <server name>]
-- maxscale <param>=<value>

例如

1
2
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日志初输出：

1
2
3
4
5
6
7
8
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日志初输出：

1
2
3
4
5
6
7
8
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定义如下：

1
2
3
4
5
6
[server3]
type=server
address=127.0.0.1
port=3308
protocol=MySQLBackend
serv_weight=3

执行脚本，查看maxscale日志初输出：

1
2
3
4
5
6
7
8
9
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]

好了，演示到此结束。