maxscale读写分离
maxscale安装
官网配置文件详解地址：
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-configuration-usage-scenarios/#authentication
安装包 maxscale-1.4.3-1.centos.6.x86_64.rpm
# rpm -ivh maxscale-1.4.3-1.centos.6.x86_64.rpm
# mkdir /data/maxscale3306
# cd /data/maxcale3306
# make {cache,data,lang,log}
# cat/etc/maxscale.cnf
[maxscale]
threads=auto ##开启线程个数，默认为1.设置为auto会同cpu核数相同
# Server definitions
maxlog=1 #将日志写入到maxscale的日志文件中
log_to_shm=0 #不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_warning=1 #记录告警信息
log_notice=1 #记录notice
log_info=1 #记录info
log_debug=0 #不打开debug模式
#auth_connect_timeout=60 #默认3秒
#auth_read_timeout=60 #默认1秒
#auth_write_timeout=60 #默认2秒
#localhost_match_wildcard_host=1
logdir=/data/maxscale3306/log/ #日志位置
datadir=/data/maxscale3306/data/ #数据位置
cachedir=/data/maxscale3306/cache/ #缓存位置
piddir=/data/maxscale3306/ #pid文件位置
connection_timeout=300 #连接超时
max_connections=1000 #最大连接数
#language=/data/maxscale3306/lang/
# Set the address of the server to the network
# address of a MySQL server.
#
#[server1],和[server2]是主从，223是主 224是从，可以有多个在加[server3....]
[server1]
type=server
address=192.168.144.223
port=3312
protocol=MySQLBackend
#serversize=10 #权重设置
[server2]
type=server
address=192.168.144.224
port=3310
protocol=MySQLBackend
#serversize=10 #权重设置
# 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] #监控信息，监控的用户需要对后端数据库有访问replication client的权限 grant replication slave, replication client on *.* to scalemon@'%' identified by '123456';
type=monitor
module=mysqlmon
servers=server1,server2
user=scalemon
passwd=B7A14BD5A08414AF147BD35CE0F761FC #加密的123456，后面有加密的方法，也可不加密
monitor_interval=10000 #监控心跳为1秒
detect_stale_master=true #slave节点全部断掉时，将所有的读写都指向master节点
#max_slave_replication_lag=5 #超过延迟5秒，把请求转发给其他slave，这里就两个节点就没开
# 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的选项全都注释掉了
#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server1,server2
#user=maxscale
#passwd=123456
#router_options=slave
#filters=Hint
#[Hint]
#type=filter
#module=hintfilter
# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
#读写分离，用户需要有SELECT ON mysql.db；SELECT ON mysql.tables_priv；SHOW DATABASES ON *.*的权限 grant select,show databases on mysql.* to maxscale@'%' identified by '123456';
[Read-Write Service]
type=service
router=readwritesplit
servers=server2,server1
router_options=master_accept_reads=false,master_failure_mode=fail_on_write
user=maxscale
passwd=B7A14BD5A08414AF147BD35CE0F761FC #123456的加密后的
max_slave_connections=100% #所有的slave提供select查询服务
use_sql_variables_in=all ###动态参数可以走全部的数据库 [all|master] 如果设置为master的话前台看到的中文是乱码，一般建议设置为all
#weightby=serversize #权重设置
#enable_root_user=1 #允许root用户登录执行
auth_all_servers=true #
log_auth_warnings=true #身份验证失败和警告的日志记录，记录那些试图连接到MaxScale和来自哪里
filters=Hint #强制select走master的选项，参考：https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-hint-syntax/ 不知道为什么不好用，强制了也不走master
[Hint]
type=filter
module=hintfilter
# 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的全部注释掉了，上面说过了
#[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
port=6603 #管理端口
加密：
# maxkeys /data/maxscale3306/data/.secrets ---创建加密文件
# maxpasswd /data/maxscale3306/data/.secrets 123456 ---生成加密后的密码
B7A14BD5A08414AF147BD35CE0F761FC 这就是配置文件中的password
注：创建加密文件的.secrets文件要在datadir定义的目录中
启动：
# /etc/init.d/maxscale start|stop|restart
或者 maxscale -f /etc/maxscale.cnf
# netstat -ntelp | grep max*
测试：
测试就不演示了
管理：
默认密码：mariadb
# maxadmin list servers -pmariadb 查看当前服务器状态
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.144.223 | 3312 | 0 | Master, Running
server2 | 192.168.144.224 | 3310 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
# maxadmin list services -pmariadb 查看当前服务连接
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Write Service | readwritesplit | 1 | 788
MaxAdmin Service | cli | 2 | 5
--------------------------+----------------------+--------+---------------
# maxadmin list listeners -pmariadb 查看当前运行端口
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name | Protocol Module | Address | Port | State
---------------------+--------------------+-----------------+-------+--------
Read-Write Service | MySQLClient | * | 4006 | Running
MaxAdmin Service | maxscaled | * | 6603 | Running
---------------------+--------------------+-----------------+-------+--------
如果想改管理密码要在配置文件里加上
[MaxAdmin]
type=service
router=cli
user=maxscale
passwd=Mhu87p2D
网站栏目：maxscale读写分离
本文网址：http://pwwzsj.com/article/iejodd.html
官网配置文件详解地址：
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-configuration-usage-scenarios/#authentication
安装包 maxscale-1.4.3-1.centos.6.x86_64.rpm
# rpm -ivh maxscale-1.4.3-1.centos.6.x86_64.rpm
# mkdir /data/maxscale3306
# cd /data/maxcale3306
# make {cache,data,lang,log}
# cat/etc/maxscale.cnf
[maxscale]
threads=auto ##开启线程个数，默认为1.设置为auto会同cpu核数相同
# Server definitions
maxlog=1 #将日志写入到maxscale的日志文件中
log_to_shm=0 #不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_warning=1 #记录告警信息
log_notice=1 #记录notice
log_info=1 #记录info
log_debug=0 #不打开debug模式
#auth_connect_timeout=60 #默认3秒
#auth_read_timeout=60 #默认1秒
#auth_write_timeout=60 #默认2秒
#localhost_match_wildcard_host=1
logdir=/data/maxscale3306/log/ #日志位置
datadir=/data/maxscale3306/data/ #数据位置
cachedir=/data/maxscale3306/cache/ #缓存位置
piddir=/data/maxscale3306/ #pid文件位置
connection_timeout=300 #连接超时
max_connections=1000 #最大连接数
#language=/data/maxscale3306/lang/
# Set the address of the server to the network
# address of a MySQL server.
#
#[server1],和[server2]是主从，223是主 224是从，可以有多个在加[server3....]
[server1]
type=server
address=192.168.144.223
port=3312
protocol=MySQLBackend
#serversize=10 #权重设置
[server2]
type=server
address=192.168.144.224
port=3310
protocol=MySQLBackend
#serversize=10 #权重设置
# 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] #监控信息，监控的用户需要对后端数据库有访问replication client的权限 grant replication slave, replication client on *.* to scalemon@'%' identified by '123456';
type=monitor
module=mysqlmon
servers=server1,server2
user=scalemon
passwd=B7A14BD5A08414AF147BD35CE0F761FC #加密的123456，后面有加密的方法，也可不加密
monitor_interval=10000 #监控心跳为1秒
detect_stale_master=true #slave节点全部断掉时，将所有的读写都指向master节点
#max_slave_replication_lag=5 #超过延迟5秒，把请求转发给其他slave，这里就两个节点就没开
# 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的选项全都注释掉了
#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server1,server2
#user=maxscale
#passwd=123456
#router_options=slave
#filters=Hint
#[Hint]
#type=filter
#module=hintfilter
# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
#读写分离，用户需要有SELECT ON mysql.db；SELECT ON mysql.tables_priv；SHOW DATABASES ON *.*的权限 grant select,show databases on mysql.* to maxscale@'%' identified by '123456';
[Read-Write Service]
type=service
router=readwritesplit
servers=server2,server1
router_options=master_accept_reads=false,master_failure_mode=fail_on_write
user=maxscale
passwd=B7A14BD5A08414AF147BD35CE0F761FC #123456的加密后的
max_slave_connections=100% #所有的slave提供select查询服务
use_sql_variables_in=all ###动态参数可以走全部的数据库 [all|master] 如果设置为master的话前台看到的中文是乱码，一般建议设置为all
#weightby=serversize #权重设置
#enable_root_user=1 #允许root用户登录执行
auth_all_servers=true #
log_auth_warnings=true #身份验证失败和警告的日志记录，记录那些试图连接到MaxScale和来自哪里
filters=Hint #强制select走master的选项，参考：https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-hint-syntax/ 不知道为什么不好用，强制了也不走master
[Hint]
type=filter
module=hintfilter
# 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的全部注释掉了，上面说过了
#[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
port=6603 #管理端口
加密：
# maxkeys /data/maxscale3306/data/.secrets ---创建加密文件
# maxpasswd /data/maxscale3306/data/.secrets 123456 ---生成加密后的密码
B7A14BD5A08414AF147BD35CE0F761FC 这就是配置文件中的password
注：创建加密文件的.secrets文件要在datadir定义的目录中
启动：
# /etc/init.d/maxscale start|stop|restart
或者 maxscale -f /etc/maxscale.cnf
# netstat -ntelp | grep max*
测试：
测试就不演示了
管理：
默认密码：mariadb
# maxadmin list servers -pmariadb 查看当前服务器状态
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.144.223 | 3312 | 0 | Master, Running
server2 | 192.168.144.224 | 3310 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
# maxadmin list services -pmariadb 查看当前服务连接
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Write Service | readwritesplit | 1 | 788
MaxAdmin Service | cli | 2 | 5
--------------------------+----------------------+--------+---------------
# maxadmin list listeners -pmariadb 查看当前运行端口
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name | Protocol Module | Address | Port | State
---------------------+--------------------+-----------------+-------+--------
Read-Write Service | MySQLClient | * | 4006 | Running
MaxAdmin Service | maxscaled | * | 6603 | Running
---------------------+--------------------+-----------------+-------+--------
如果想改管理密码要在配置文件里加上
[MaxAdmin]
type=service
router=cli
user=maxscale
passwd=Mhu87p2D
网站栏目：maxscale读写分离
本文网址：http://pwwzsj.com/article/iejodd.html
