一.maxscale简介
1.MaxScale是maridb开发的一个mysql数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。2.官网：https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-20/二.安装
1.安装可以通过rpm安装2.可以直接下载文件放入指定目录，本次采用第二种方法#wget https://downloads.mariadb.com/MaxScale/2.0.2/centos/5Server/x86_64/maxscale-2.0.2.centos.5.tar.gz#tar zxvf maxscale-2.0.2.centos.5.tar.gz#mkdir /usr/local/maxscale#mv ./maxscale-2.0.2/* /usr/local/maxscale/#cp /usr/local/maxscale/etc/maxscale.cnf.template /etc/maxscale.cnf
修改启动脚本,使其可以提供service服务
#cp /usr/local/maxscale/share/maxscale/maxscale /etc/init.d/maxscale#vi /etc/init.d/maxscale
将脚本中的///bin替换为/usr/local/maxscale/bin/maxscale
这将可以使用 service maxscale start|stop|restart启动停止或重启maxscale 三.账户配置1.在主从库上授权两个账户a.监视账户create user maxscale_monitor@'192.168.1.%' identified by "123456";grant replication slave, replication client on *.* to maxscale_monitor@'192.168.1.%';
b.路由账户
create user maxscale_route@'192.168.1.%' identified by "123456"; GRANT SELECT ON mysql.user TO maxscale_route@'192.168.1.%'; GRANT SELECT ON mysql.db TO maxscale_route@'192.168.1.%'; GRANT SELECT ON mysql.tables_priv TO maxscale_route@'192.168.1.%'; GRANT SHOW DATABASES ON *.* TO maxscale_route@'192.168.1.%';
四.读写分离配置0.vi /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# 记录noticelog_notice=1# 记录infolog_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/
1.读写分离配置
a.配置两个server[server1]type=serveraddress=192.168.1.126port=3306protocol=MySQLBackendserv_weight=3 #读的比重[server2]type=serveraddress=192.168.1.84port=3306protocol=MySQLBackendserv_weight=2 #读的比重
b.配置monitor
[MySQL Monitor]type=monitormodule=mysqlmonservers=server1,server2user=maxscale_monitorpasswd=123456monitor_interval=10000
c.配置读写分离
[Read-Write Service]type=servicerouter=readwritesplitservers=server1,server2user=maxscale_routepasswd=123456max_slave_connections=100% #所有的slave提供select查询服务#use_sql_variables_in=all #动态参数可以走全部的数据库 [all|master] 如果设置为master的话前台看到的中文是乱码，一般建议设置为all#weightby=serversize #权重设置#enable_root_user=1 #允许root用户登录执行master_accept_reads=true #master是否接受读请求#auth_all_servers=true ##log_auth_warnings=true #身份验证失败和警告的日志记录，记录那些试图连接到MaxScale和来自哪里#filters=Hint #强制select走master的选项
d.配置端口
[Read-Write Listener]type=listenerservice=Read-Write Serviceprotocol=MySQLClientport=4006[MaxAdmin Listener]type=listenerservice=MaxAdmin Serviceprotocol=maxscaledport=6603socket=default
五.启动
1./usr/local/maxscale/bin/maxscale start2.查看状态(以下的user和password均为默认账号密码):/usr/local/maxscale/bin/maxadmin --user=admin --password=mariadb(/usr/local/maxscale/bin/maxadmin -S /tmp/maxadmin.sock #使用该项也可以登录)MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server | Address | Port | Connections | Status-------------------+-----------------+-------+-------------+--------------------server1 | 192.168.1.126 | 3306 | 2 | Master, Runningserver2 | 192.168.1.84 | 3306 | 2 | Slave, Running-------------------+-----------------+-------+-------------+--------------------
更多帮助可以使用 help命令来获得
如果想要更改maxadmin的密码在配置文件中加上[MaxAdmin Service]type=servicerouter=cliuser=maxscalepasswd=123456
3.设置服务器维护状态
MaxScale> set server server2 maintenanceMaxScale> list servers-------------------+-----------------+-------+-------------+--------------------Server | Address | Port | Connections | Status-------------------+-----------------+-------+-------------+--------------------server1 | 192.168.1.126 | 3306 | 1 | Master, Runningserver2 | 192.168.1.84 | 3306 | 0 | Maintenance, Slave, Running
4.清除维护状态
MaxScale> clear server server2 maintenanceMaxScale> list servers-------------------+-----------------+-------+-------------+--------------------Server | Address | Port | Connections | Status-------------------+-----------------+-------+-------------+--------------------server1 | 192.168.1.126 | 3306 | 3 | Master, Runningserver2 | 192.168.1.84 | 3306 | 3 | Slave, Running-------------------+-----------------+-------+-------------+--------------------
六.只读,按一定比例分摊读
1.配置只读服务[Read-Only Service]type=servicerouter=readconnrouteservers=server1,server2user=maxscale_routepasswd=123456weightby=serv_weight
server1和server2增加分摊比例
[server1]type=serveraddress=192.168.118.126port=3306protocol=MySQLBackendserv_weight=3[server2]type=serveraddress=192.168.118.84port=3306protocol=MySQLBackendserv_weight=2
2.配置端口
[Read-Only Listener]type=listenerservice=Read-Only Serviceprotocol=MySQLClientport=4008
3.重新启动
七.filter1.读写分离,如果slave没有问题,默认读全部走slave,但有时候我们需要能够读取走master,这时候就需要配置filter在读写分离项中配置,多个filter之前用'|'号分开filters=Hint[Hint]type=filtermodule=hintfilter
重启maxscale
应用在sql查询中:
select * from table where id=10; -- maxscale route to master将使读走master 2.有时候我们希望一个数据表能到达指定服务器,则可以这样配置[tableFilter]type=filtermodule=namedserverfiltermatch=table_name1|table_name2|table_name3options=ignorecase,extended #忽略大小写,并启用扩展配置,这将使用正则表达式server=server1
ps:以上options中按文档应该ignorecase,extended两者都应该生效,但实际测试是发现前面的生效,后面的就不生效
读写分离路由中配置
filters=tableFilter这将使带有table_name1,table_name2,table_name3的查询或更新,全部到达server1
八.测试和总结1.如果你有用过mycat和amoeba,你将会发现maxscale配置更加简单一些,与前两者相比,maxscale并没有使用连接池(不知道是不是我理解有误)2.mycat和amoeba作为中间件,客户端在连接时候,账号和密码是在其配置文件中指定.但maxscale并不是,而是要求在后端(server1和server2)上定义账号,maxscale并不验证账号密码正确性,而是有后端去验证.这项相比前两者,更加透明.