前言

在现在读写分离已经是不奇怪了, 基本上有接触一点MySQL的都会谈到要读写分离。下面我们以3个方面来探讨一些并且介绍如何使用Maxscale来做适合业务的读写分离：

  2. 读写分离要怎么做呢？
  3. 在一个项目当中应该什么时候接入读写分离呢？
  4. 如何正确的使用读写分离呢？

读写分离要怎么做

其实读写分离最稳定的做法就是直接嵌入到程序中，通过业务程序来直接做判断哪些SQL需要访问哪个库。但是往往对于一个开放团队来说，在一个已有的项目中应入读写分离，其实工作量还是有的。特别是在一个公司如果没有一个很好的程序设计的人员，做读写分离将会是无趣找代码、cpoy代码并且让代码变的维护性变的更差。

插曲：如果有一个比较强的程序设计人员可以将重构读写分离的程序达到一两行代码搞定(这边使用程序编程的装饰器和工厂模式配合将会比较合适,当然肯定有更合适的方法)。

当然现在能做的读写分离的中间件很多，Maxscale就能够胜任这样的工作，Maxscale的Hint解析分发SQL能见读写分离做的更加灵活。最主要的是使用Maxscale的Hint最代码的改造将会减少很多。

注意：这边有的人会有疑问Maxscale天生不就是做读写分离么，为啥还要用到Hint，别急下面会说道'正确的使用读写分离'(可能你的业务默认使用Maxscale读写分离就能满足了)。

做读写分离其实在前期和开发沟通是主要一方面，要不断引导他们如何去做读写分离。毕竟一个公司里面不都是经验丰富的开发0-2年工作经验的偏多。

什么时候接入读写分离

在一个新项目开始的时候基本上是以功能为核心，以功能多并且还比较好来抢占市场，来积累一定的用户，是不稳定的。因为他们在一点点的快速摸索迭代中，过早的引入读写分离，会在一定程度上拉长项目的进度(当然这个阶段如果有DBA最基本的规范优化还是要有的)。

如果有项目需要重构 或 一般当项目上线一段时间了发现了发现了一般业务的SQL不能满足业务的发展需要一些花哨的功能来响应市场，但是这些功能编写出来的SQL会一定程度上会影响到数据库的运行。这时候就应该规划读写分离的使用。

当然如果对项目的的未来有个比较好的预测，那从项目的开始就使用读写分离降低之后接入读写分离的成本这是一个正确的做法。比如现在阿里、移动、电信又要搞一个产品那我觉得可以把读写分离提前就搞上，毕竟他们有用户基础，只要产品出来稍微推广一下压力就上来了。

正确的使用读写分离

要正确的使用读写分离，那就必须和实际情况和业务相结合了。

这边先说使用普通读写分离的一个现象：直接通过中间件使用读写分离，让读都走 slave。在没啥压力的时候主从不延时的时候这样能很好的服务。但是当压力以上来主从有延时了那么就有问题了。比如：创建一个商品创建的时候写的是Master，可是创建成功了一般还要从新查询一下数据库数据，并转跳到编辑页面。但是这时候主从有延时的时候就读取不到数据了。

所以一般在查询列表的时候可以读slave，但是在精确查找的时候应该是读master的。当然比较普遍的复杂查询也放在slave是比较明智的。

Maxscale使用Hint读写分离实验

二话不说在之前环境的基础上来做接下来的实验。直接上配置文件：

主要配置



[Read-Write Service]... filters=Hint [Hint] type=filter module=hintfilter




1


2


3


4


5


6


7




[Read-Write Service]


...


filters=Hint


 


[Hint]


type=filter


module=hintfilter



完整配置



[root@normal_11 ~]# cat /etc/maxscale.cnf ################################################### # CREATE USER maxscale@'%' IDENTIFIED BY "123456"; # GRANT replication slave, replication client ON *.* TO maxscale@'%'; # GRANT SELECT ON mysql.* TO maxscale@'%'; # GRANT ALL ON maxscale_schema.* TO maxscale@'%'; # GRANT SHOW DATABASES ON *.* TO maxscale@'%'; # groupadd maxscale # useradd -g maxscale maxscale # cd /opt # tar -zxf maxscale-2.0.1.rhel.7.tar.gz # ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale # chown -R maxscale:maxscale /usr/local/maxscale # mkdir -p /u01/maxscale/{data,cache,logs,tmp} # mkdir -p /u01/maxscale/logs/{binlog,trace} # chown -R maxscale:maxscale /u01/maxscale # /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/ # /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456 ################################################### [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 # 相关目录设置 basedir=/usr/local/maxscale/ logdir=/u01/maxscale/logs/trace/ datadir=/u01/maxscale/data/ cachedir=/u01/maxscale/cache/ piddir=/u01/maxscale/tmp/ [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 [MySQL Monitor] type=monitor module=mysqlmon servers=server1,server2,server3 user=maxscale passwd=1D30C1E689410756D7B82C233FCBF8D9 # 监控心态为 10s monitor_interval=10000 # 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点 detect_stale_master=true # 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master) # detect_replication_lag=true [Read-Only Service] type=service router=readconnroute servers=server1,server2,server3 user=maxscale passwd=1D30C1E689410756D7B82C233FCBF8D9 router_options=slave # 允许root用户登录执行 enable_root_user=1 # 查询权重 weightby=serv_weight [Read-Write Service] type=service router=readwritesplit servers=server1,server2,server3 user=maxscale passwd=1D30C1E689410756D7B82C233FCBF8D9 max_slave_connections=100% # sql语句中的存在变量只指向master中执行 use_sql_variables_in=master # 允许root用户登录执行 enable_root_user=1 # 允许主从最大间隔(s) max_slave_replication_lag=3600 filters=Hint [MaxAdmin Service] type=service router=cli [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=/u01/maxscale/tmp/maxadmin.sock port=6603 [Hint] type=filter module=hintfilter




1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23


24


25


26


27


28


29


30


31


32


33


34


35


36


37


38


39


40


41


42


43


44


45


46


47


48


49


50


51


52


53


54


55


56


57


58


59


60


61


62


63


64


65


66


67


68


69


70


71


72


73


74


75


76


77


78


79


80


81


82


83


84


85


86


87


88


89


90


91


92


93


94


95


96


97


98


99


100


101


102


103


104


105


106


107


108


109


110


111


112


113


114


115


116


117


118


119


120


121


122


123


124


125


126


127


128


129


130


131


132


133


134


135


136




[root@normal_11 ~]# cat /etc/maxscale.cnf


###################################################


# CREATE USER maxscale@'%' IDENTIFIED BY "123456";


# GRANT replication slave, replication client ON *.* TO maxscale@'%';


# GRANT SELECT ON mysql.* TO maxscale@'%';


# GRANT ALL ON maxscale_schema.* TO maxscale@'%';


# GRANT SHOW DATABASES ON *.* TO maxscale@'%';


# groupadd maxscale


# useradd -g maxscale maxscale


# cd /opt


# tar -zxf maxscale-2.0.1.rhel.7.tar.gz


# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale


# chown -R maxscale:maxscale /usr/local/maxscale


# mkdir -p /u01/maxscale/{data,cache,logs,tmp}


# mkdir -p /u01/maxscale/logs/{binlog,trace}


# chown -R maxscale:maxscale /u01/maxscale


# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/


# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456


###################################################


 


[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


 


# 相关目录设置


basedir=/usr/local/maxscale/


logdir=/u01/maxscale/logs/trace/


datadir=/u01/maxscale/data/


cachedir=/u01/maxscale/cache/


piddir=/u01/maxscale/tmp/


 


[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


 


[MySQL Monitor]


type=monitor


module=mysqlmon


servers=server1,server2,server3


user=maxscale


passwd=1D30C1E689410756D7B82C233FCBF8D9


# 监控心态为 10s


monitor_interval=10000


# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点


detect_stale_master=true


# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)


# detect_replication_lag=true


 


[Read-Only Service]


type=service


router=readconnroute


servers=server1,server2,server3


user=maxscale


passwd=1D30C1E689410756D7B82C233FCBF8D9


router_options=slave


# 允许root用户登录执行


enable_root_user=1


# 查询权重


weightby=serv_weight


 


[Read-Write Service]


type=service


router=readwritesplit


servers=server1,server2,server3


user=maxscale


passwd=1D30C1E689410756D7B82C233FCBF8D9


max_slave_connections=100%


# sql语句中的存在变量只指向master中执行


use_sql_variables_in=master


# 允许root用户登录执行


enable_root_user=1


# 允许主从最大间隔(s)


max_slave_replication_lag=3600


filters=Hint


 


[MaxAdmin Service]


type=service


router=cli


 


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


port=6603


 


[Hint]


type=filter


module=hintfilter



编写​​Python​程序实现Hint读写分离

这边由于在MySQL Client 控制台效果演示不出来，这边就用程序来演示：

  2. 代码

在代码中我们手动指定SELECT读取Master 在SQL后面加上 -- maxscale route to master



[root@normal_11 tmp]# cat test.py#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine conf = { 'host': '192.168.137.11', 'port': 4006, 'user': 'HH', 'passwd': '<a href="http://www.ttlsa.com/oracle/" title="Oracle"target="_blank">oracle</a>', 'db': 'test', 'charset': 'utf8' } # 生成链接数据库engine 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 * from t1; -- maxscale route to master """ conn.execute("SET AUTOCOMMIT=1") conn.execute(sql)




1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23


24


25


26


27


28


29


30


31




[root@normal_11 tmp]# cat test.py


#!/usr/bin/env python


# -*- coding:utf-8 -*-


 


from sqlalchemy import create_engine


 


conf = {


    'host': '192.168.137.11',


    'port': 4006,


    'user': 'HH',


    'passwd': 'oracle',


    'db': 'test',


    'charset': 'utf8'


}


# 生成链接数据库engine


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 * from t1; -- maxscale route to master


"""


 


conn.execute("SET AUTOCOMMIT=1")


conn.execute(sql)



  2. 执行



[root@normal_11 tmp]# python test.py




1




[root@normal_11 tmp]# python test.py



  2. 查看日志



# 这边Maxscale路由到手动指定master上2016-11-05 11:34:43.681 [7] info : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT * from t1; -- maxscale route to master , Hint: HINT_ROUTE_TO_MASTER




1


2


3


4




# 这边Maxscale路由到手动指定master上


2016-11-05 11:34:43.681   [7]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt:


SELECT * from t1; -- maxscale route to master


, Hint: HINT_ROUTE_TO_MASTER



  2. 手动指定 Slave



-- 修改代码中的查询语句，让语句路由到SlaveSELECT * from t1; -- maxscale route to master -- 修改给 SELECT * from t1; -- maxscale route to slave




1


2


3


4




-- 修改代码中的查询语句，让语句路由到Slave


SELECT * from t1; -- maxscale route to master


-- 修改给


SELECT * from t1; -- maxscale route to slave



  2. 运行代码 并 产看日志情况



# 这边Maxscale路由到手动指定slave上2016-11-05 11:48:56.974 [6] info : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: SELECT * from t1; -- maxscale route to slave , Hint: HINT_ROUTE_TO_SLAVE




1


2


3


4




# 这边Maxscale路由到手动指定slave上


2016-11-05 11:48:56.974   [6]  info   : (route_single_stmt): > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt:


SELECT * from t1; -- maxscale route to slave


, Hint: HINT_ROUTE_TO_SLAVE



总结

Maxscale的Hint功能就能满足通过业务情况在实现正确的读写分离了，并且这对于程序原来说只需要有使用到SQL语句的地方就好了。