安装介质
mysql-router-8.0.24-linux-glibc2.12-x86_64.tar.xz
上传目录
/home/mysql(自选目录)
解压
tar -Jxvf mysql-router-8.0.24-linux-glibc2.12-x86_64.tar.xz
配置环境变量
vi .bash_profile
PATH=/mysql/mysql80/bin:/home/mysql/mysql-router-8.0.24-linux-glibc2.12-x86_64/bin:$HOME/bin:$PATH
source .bash_profile
$mysql --version
mysql Ver 8.0.24 for Linux on x86_64 (MySQL Community Server - GPL)
$mysqlrouter --version
MySQL Router Ver 8.0.24 for Linux on x86_64 (MySQL Community - GPL)
添加Router配置文件
$vi .mysqlrouter.conf
[logger]
level = INFO
[routing:secondary]
bind_address = localhost
bind_port = 7001
destinations = xxx.xxx.xxx.xxx:xxxx,xxx.xxx.xxx.xxx:xxxx,xxx.xxx.xxx.xxx:xxxx
routing_strategy = round-robin
[routing:primary]
bind_address = localhost
bind_port = 7002
destinations = xxx.xxx.xxx.xxx:xxxx,xxx.xxx.xxx.xxx:xxxx,xxx.xxx.xxx.xxx:xxxx
routing_strategy = first-available
启动mysql router
$mysqlrouter -c /home/mysql/.mysqlrouter.conf &
查看进程
$ps -ef | grep router
mysql 29602 25579 0 14:17 pts/1 00:00:00 mysqlrouter -c /home/mysql/.mysqlrouter.conf
查看日志
$vi mysqlrouter.log
2021-07-16 14:17:16 io INFO [7fda08342780] starting 4 io-threads, using backend ‘linux_epoll’
2021-07-16 14:17:16 routing INFO [7fd86bfff700] [routing:secondary] started: routing strategy = round-robin
2021-07-16 14:17:16 routing INFO [7fd8affff700] [routing:primary] started: routing strategy = first-available
2021-07-16 14:17:16 routing INFO [7fd8affff700] Start accepting connections for routing routing:primary listening on 7002
2021-07-16 14:17:16 routing INFO [7fd86bfff700] Start accepting connections for routing routing:secondary listening on 7001
查看监听
netstat -tnlp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp6 0 0 :7001 ::: LISTEN 29602/mysqlrouter
tcp6 0 0 :7002 ::: LISTEN 29602/mysqlrouter
根据配置策略,实现通过7001端口,循环连接到3台数据库服务器
mysql -uusr1 -p**** -hlocalhost -P7001 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| @@hostname |
+-----------------+
| gp16clouddbrr1k |
+-----------------+
$mysql -uusr1 -p**** -hlocalhost -P7001 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| @@hostname |
+-----------------+
| gp16clouddbv0cr |
+-----------------+
$mysql -uusr1 -p**** -hlocalhost -P7001 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| @@hostname |
+-----------------+
| gp16clouddbtaf5 |
+-----------------+
根据配置策略,实现通过7002端口,每次只连接到1台可用的主数据库服务器,当主服务器发生改变,连接到第二个可用的主服务器,以此类推
$mysql -uusr1 -p*** -hlocalhost -P7002 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| @@hostname |
+-----------------+
| gp16clouddbrr1k |
+-----------------+
$mysql -uusr1 -p*** -hlocalhost -P7002 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| @@hostname |
+-----------------+
| gp16clouddbrr1k |
+-----------------+
$mysql -uusr1 -p*** -hlocalhost -P7002 --protocol=TCP -e"select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| @@hostname |
+-----------------+
| gp16clouddbrr1k |
+-----------------+
学习点一:routing_strategy,控制路由策略
值分为:first-available、next-available、round-robin、round-robin-with-fallback
round-robin:每个新连接都以循环方式连接到下一个可用的服务器,实现负载平衡
first-available:新连接从目标列表路由到第一个可用服务器。如果失败,则使用下一个可用的服务器,如此循环,直到所有服务器都不可用为止
next-available:与first-available类似,新连接从目标列表路由到第一个可用服务器。与first-available不同的是,如果一个服务器被标记为不可访问,那么它将被丢弃,并且永远不会再次用作目标。重启Router后,所有被丢弃服务器将再次可选
round-robin-with-fallback:用于InnoDB Cluster。每个新的连接都以循环方式连接到下一个可用的SECONDARY服务器。如果SECONDARY服务器不可用,则以循环方式使用PRIMARY服务器