MySQL Router 安装学习研究（二）

安装介质

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服务器