mysql路由器配置

1.开启一个新的节点配置mysql路由器

[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm     %安装相关rpm安装包
[root@server4 ~]# cd /etc/mysqlrouter/
[root@server4 mysqlrouter]# ls
mysqlrouter.conf
[root@server4 mysqlrouter]# vim mysqlrouter.conf      %添加下面的内容
[routing:ro]                    %只读路由
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.1.1:3306,172.25.1.2:3306,172.25.1.3:3306
routing_strategy = round-robin     %调度算法

[routing:rw]                    %读写路由     
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.1.1:3306,172.25.1.2:3306,172.25.1.3:3306
routing_strategy = first-available 
[root@server4 mysqlrouter]# systemctl start mysqlrouter.service       %重启服务
[root@server4 mysqlrouter]# yum install -y net-tools
[root@server4 mysqlrouter]# netstat -antulp           %查看相关端口是否开启
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      4011/mysqlrouter    
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      3237/master         
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      4011/mysqlrouter   

2.在server1上创建mysql远程登陆用户，并作相应授权

[root@server1 data]# mysql -pwestos
mysql> grant select on *.* to user1@'%' identified by 'westos';    %创建user1并且授予select（查询）权限
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all on test.* to user2@'%' identified by 'westos';    %创建user2并且授予对test库具备all（完全）权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

3.路由器配置好之后在宿主机上以指定用户远程连接mysql指定路由端口，7001或7002

[root@foundation1 ~]# mysql -h 172.25.1.4 -P 7001 -u user1 -pwestos     %连接7001端口为只读
[root@foundation1 ~]# mysql -h 172.25.1.4 -P 7002 -u user2 -pwestos     %连接7002端口为可读写

4.使用lsof在mysql其它节点上测试连接情况

[root@server1 data]# yum install -y lsof
[root@server1 data]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  3666 mysql   31u  IPv6  27108      0t0  TCP *:mysql (LISTEN)
mysqld  3666 mysql   83u  IPv6  32262      0t0  TCP server1:mysql->server4:45442 (ESTABLISHED)

此时远端再次登入数据库发现已经调度到server2上：