使用MySqlrouter实现MYSQL读写分离

一、MYSQL读写分离

1.1 读写分离概述：

​ 鉴于数据量增多，单机的数据库支撑业务有压力的情况下，进行了读写分离的初步部署；

​ 目前读写分离的中间件比较多，比如有美团的DBProxy、360的Altas、OneProxy、MyCat、阿里的DRDS/TDD等，就简单的进行读写分离，考虑到更新维护情况和暂不进行数据库分库分表操作的情况，本次选择了mysql兼容更好的mysqlrouter；

​ 本次部署采用mysql官方的中间件mysqlrouter-8.0.21,（部署数据库版本为mysql8.0.21）；

​ MySQL Router是一个介于应用层和DB层之间的开源的轻量级中间件，它能够将前端应用的请求分析转发给后端DB服务器处理，从而实现DB的负载均衡。

1.2 部署示例结构：

​

1.3 部署mysqlrouter

[root@c7108:/opt/src]# tar -xf mysql-router-8.0.21-el7-x86_64.tar.gz -C /data/tools/
[root@c7108:/opt/src]# cd /data/tools/
[root@c7108:/data/tools]# mv mysql-router-8.0.21-el7-x86_64 mysql-router
[root@c7108:/data/tools]# cat >> /etc/profile << EOF
export MYSQL_ROUTER_HOME=/data/tools/mysql-router
export PATH=$MYSQL_ROUTER_HOME/bin:$PATH
EOF
[root@c7108:/data/tools]# source /etc/profile
[root@c7108:/data/tools]# mysqlrouter --version
MySQL Router  Ver 8.0.21 for Linux on x86_64 (MySQL Community - GPL)

配置文件
[root@c7108:/data/tools]# vim /etc/mysqlrouter.conf
[DEFAULT]
logging_folder = /data/tools/logs
config_folder = /etc/
runtime_folder = /data/tools/mysql-router/run
data_folder = /data/tools/mysql-router/data

[logger]
level = INFO

[routing:secondary]
# To be more transparent, use MySQL Server port 3306
bind_address=0.0.0.0
bind_port = 7001
routing_strategy = round-robin
destinations = 192.168.13.105:3306,192.168.13.106:3306,192.168.13.107:3306

[routing:primary]
bind_address=0.0.0.0
bind_port = 7002
routing_strategy = first-available
destinations = 192.168.13.102:3306

[keepalive]
interval = 60

创建system unit文件
cat > /usr/lib/systemd/system/mysqlrouter.service << EOF
[Unit]
Description=mysqlrouter
Documentation=https://dev.mysql.com/doc/
After=network.target

[Service]
Type=simple
User=root
ExecStart=/data/tools/mysql-router/bin/mysqlrouter -c /etc/mysqlrouter.conf   
Restart=on-failure

[Install]
WantedBy=multi-user.target

EOF

启动
systemctl daemon-reload
systemctl enable mysqlrouter.service
systemctl start mysqlrouter.service
systemctl status mysqlrouter.service

1.4 mysqlrouter读测试

[root@mysql-delay:/root]# mysql -uroot -p'Test%150' -h192.168.7.108 -P7001 --protocol=TCP -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-s02  |
+------------+
[root@mysql-delay:/root]# mysql -uroot -p'Test%150' -h192.168.7.108 -P7001 --protocol=TCP -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-s03  |
+------------+
[root@mysql-delay:/root]# mysql -uroot -p'Test%150' -h192.168.7.108 -P7001 --protocol=TCP -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql-s01  |
+------------+

其中s01为从1   s02为从2  s03为从3

1.5 mysqlrouter写测试

[root@mysql-delay:/root]# mysql -uroot -p'Test%150' -h192.168.7.108 -P7002 --protocol=TCP -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql      |
+------------+
[root@mysql-delay:/root]# mysql -uroot -p'Test%150' -h192.168.7.108 -P7002 --protocol=TCP -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql      |
+------------+

其中mysql为主

由上可见，发送到MySQLrouter(192.168.7.108):7001端口的请求，被循环转发至三个从服务器，而发送至MySQLrouter(192.168.7.108):7002端口的请求，被转发至主服务器。

其中mysql为主

**由上可见，发送到MySQLrouter(192.168.7.108):7001端口的请求，被循环转发至三个从服务器，而发送至MySQLrouter(192.168.7.108):7002端口的请求，被转发至主服务器。**

原文链接:https://blog.csdn.net/weixin_43667733/article/details/117333595