maxscale读写分离
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,maxscale安装官网配置文件详解地址:https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-configur
千家信息网最后更新 2024年09月22日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
官网配置文件详解地址:
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
文件
加密
日志
位置
端口
密码
数据
权重
用户
节点
服务
监控
管理
配置
信息
数据库
权限
模式
注释
缓存
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发第四代技术
嘉定区推广软件开发供应商
中医方剂数据库
易捷保网络技术有限公司
有关网络技术的英语翻译
安卓登陆数据库源码下载
手机软件开发员招聘
软件开发公司运维人员的职责
java项目数据库的设计
辽宁交通网络技术好考吗
信息技术与数据库技术论文
数据库四大组件
河南安家助拍网络技术有限公司
核电厂模拟器网络安全
安卓带数据库源码下载
软件开发行业基本工资和绩效工资
数据库后缀文件
扫黄扫非 护苗 网络安全
计算机三级数据库技术破解
电商软件开发工资待遇
杭州开创网络技术有公司
数据库限定数据取值范围
musql 数据库如何优化
知名app软件开发公司
数据库安全与管理实验报告
黑龙江省公安厅网络安全处
三维仿真软件开发公司
易语言读写配置数据库
邮件服务器配置要求
网络安全人员培训资料