MySQL Router安装和读写分离实现
MySQL Router 安装
解压缩，修改路径名字
root@ubuntu06:/usr/local/mysql_install_package# tar xvf mysql-router-8.3.0-linux-glibc2.28-x86_64.tar.xz
root@ubuntu06:/usr/local/mysql_install_package# mv mysql-router-8.3.0-linux-glibc2.28-x86_64 mysql-router8.3
root@ubuntu06:/usr/local/mysql_install_package# cd mysql-router8.3
创建一个router服务的数据路径router
root@ubuntu06:/usr/local/mysql-router8.3# mkdir router
root@ubuntu06:/usr/local/mysql-router8.3# cd router
在router路径下创建data log config run
root@ubuntu06:/usr/local/mysql-router8.3/router# mkdir data log config run用解压包中的设置文件模板，依据当前的主从复制，编辑router的设置文件
logging_folder = /usr/local/mysql-router8.3/router/log
plugin_folder = /usr/local/mysql-router8.3/lib/mysqlrouter
config_folder = /usr/local/mysql-router8.3/router/config
runtime_folder = /usr/local/mysql-router8.3/router/run
data_folder = /usr/local/mysql-router8.3/router/data
#keyring_path = /var/lib/keyring-data
#master_key_path = /var/lib/keyring-key
level = INFO
filename = mysqlrouter.log
timestamp_precision = second
# To be more transparent, use MySQL Server port 3306
bind_address = 0.0.0.0
bind_port = 7001
routing_strategy = first-available
mode = read-write
destinations = 192.168.152.112:8000,192.168.152.113:8000
bind_address = 0.0.0.0
bind_port = 7002
#routing_strategy = first-available
mode = read-only
destinations = 192.168.152.113:8000,192.168.152.112:8000
# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
interval = 60
MySQL Router systemd服务脚本
MySQL Router systemd服务脚本如下
Description=Percona MySQL Router
After=network.target
After=syslog.target
Type=notify
User=mysql
Group=mysql
# Start main service
ExecStart=/usr/local/mysql-router8.3/bin/mysqlrouter -c /usr/local/mysql-router8.3/router/config/mysqlrouter.conf
# Sets open_files_limit
LimitNOFILE = 100000
Restart=on-failure
PrivateTmp=true
WantedBy=multi-user.target 编辑好MySQL systemd设置文件后，reload
systemctl daemon-reload一开始通过systemctl start mysqlrouter报错，查看系统日记如下，提示相关路径没有权限
https://img2024.cnblogs.com/blog/380271/202504/380271-20250425131341630-1185778423.png
重新授权 MySQL router的数据文件路径chown mysql:mysql -R mysql-router8.3，再次启动，可以正常启动
https://img2024.cnblogs.com/blog/380271/202504/380271-20250425131115972-1336966521.png
MySQL Router 读写分离
通过绑定的读&写节点端口号70001端口，可以在正常连接至节点
https://img2024.cnblogs.com/blog/380271/202504/380271-20250425132325244-70672755.png
通过绑定的只读节点的7002端口，哀求可以在主从两个节点之间轮训，请注意MySQL是在连接的时候轮训设置的读节点的，数据库连接之后指向的节点就稳定了，所以这个验证要新建连接的方式
https://img2024.cnblogs.com/blog/380271/202504/380271-20250425132241821-697838812.png
自动故障转移
https://img2024.cnblogs.com/blog/380271/202504/380271-20250425135020359-1802944762.png
MySQL Router安装位置
MySQL Router自身不存储数据，足够的轻量和简单，进做一个数据库连接的转发工作，算做事无状态服务。正如官网上给出的示例图，MySQL Router更应该安装在Application端，作为Application的一部分，而不是数据库端。
https://img2024.cnblogs.com/blog/380271/202504/380271-20250425160500660-598925186.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]