mysql-router读写分离

最新推荐文章于 2024-11-19 15:44:45 发布

原创最新推荐文章于 2024-11-19 15:44:45 发布 · 置顶 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql-router #负载均衡

HA 专栏收录该内容

15 篇文章

订阅专栏

本文介绍 MySQL Router 的安装配置过程及其实现的功能，包括读写分离、故障切换、负载均衡等，并通过实例展示了如何配置 MySQL Router 来实现这些功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL router功能有读写分离，故障切换、负载均衡、透明代理等功能

1、搭建数据库主从略

2、mysql_router安装如下:

tar -xvf mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz
cp mysql-router-2.1.4-linux-glibc2.12-x86-64bit /usr/local/mysqlrouter -rf
cd /usr/local/mysqlrouter/
# 创建日志目录
mkdir -p /usr/local/mysqlrouter/logs
# 创建配置文件目录
mkdir -p /usr/local/mysqlrouter/conf
touch /usr/local/mysqlrouter/conf/mysqlrouter.ini
#配置文件为 /usr/local/mysqlrouter/conf/mysqlrouter.ini

[DEFAULT]
logging_folder = /usr/local/mysqlrouter/logs
plugin_folder = /usr/local/mysqlrouter/lib/mysqlrouter
config_folder = /usr/local/mysqlrouter/conf
runtime_folder = /usr/local/mysqlrouter/run

[logger]
#level = INFO
level = DEBUG

[routing:read_write]
bind_address = 172.16.32.102
bind_port = 7001
mode = read-write
destinations = 172.16.32.11:3307,172.16.32.10:3307
max_connections = 65535
max_connect_errors = 10000
client_connect_timeout = 9

[routing:read_only]
bind_address = 172.16.32.102
bind_port = 7002
mode = read-only
destinations = 172.16.32.10:3307,172.16.32.104:3307
max_connections = 65535
max_connect_errors = 10000
client_connect_timeout = 9

[keepalive]
interval = 60

###部分参数说明

DEFAULT]
# 定义日志目录
logging_folder = /usr/local/mysqlrouter/logs
[logger]
# 定义日志等级
level = INFO
# 一个高可用的标签
[routing:failover]
bind_address = 0.0.0.0
bind_port = 7001
max_connections = 1024
# 目前就支持两种 : read-write 和 read-only # read-write:用于高可用，用于可读可写
# read-only:用于负载均衡，只读
mode = read-write
# 实际转发的地址
# 第一个socket如果可用，就一直会使用第一个
# 如果第一个socket无法连接了，才会连接到第二个socket
destinations = 主库地址:3307, 备选主库地址:3307

# 一个用于复杂均衡的标签
[routing:balancing]
bind_address = 0.0.0.0
bind_port = 7002
max_connections = 1024
# 用于负载均衡的只读模式
mode = read-only
# 这里的两个socket是轮询用的
destinations = 从库地址:port,从库地址2:port //读负载均衡配置

######测试如下:

##服务端口检查:netstat -tnl
看日志:
2018-02-06 16:17:59 DEBUG [7f4fe95bb700] [routing:read_write] TCP connection from 6 accepted at 172.16.32.102:7001
2018-02-06 16:17:59 DEBUG [7f4fd37fe700] Trying server 172.16.32.11:3307 (index 0)
2018-02-06 16:17:59 DEBUG [7f4fd37fe700] [routing:read_write] source [172.16.32.102]:39043 - dest [172.16.32.11]:3307
2018-02-06 16:18:34 INFO [7f4feb5bf700] keepalive
2018-02-06 16:19:00 DEBUG [7f4fd37fe700] [routing:read_write] Routing stopped (up:481b;down:320b)
2018-02-06 16:19:01 DEBUG [7f4fe95bb700] [routing:read_write] TCP connection from 6 accepted at 172.16.32.102:7001
2018-02-06 16:19:01 DEBUG [7f4fd37fe700] Trying server 172.16.32.11:3307 (index 0)
2018-02-06 16:19:01 DEBUG [7f4fd37fe700] [routing:read_write] source [172.16.32.102]:39045 - dest [172.16.32.11]:3307
2018-02-06 16:19:34 INFO [7f4feb5bf700] keepalive
1、实现读负载均衡
##在不同的SESSION中登陆7002访问到不同的DB
2、从库离线处理
##从库自动上线离线处理
##连接切换(秒切换)
3、主库离线与备选主库
##实现备选主库切换,主库恢复后不在使用连接
##一组多从情况下，非线性级联情况下，最后个从库同步异常
##适合多主环境、GTID，如MGR，不适合异步复制
##重启服务路由更新
4、并发测试
##出现服务异常，7002仍然可以访问，重启服务后恢复
##最大支持500并发