MySQL笔记——安装MySQL Router 8_mysql router安装-CSDN博客

本文链接：https://blog.csdn.net/qq_32838955/article/details/124487772

MySQL Router是MySQL官方提供的一个轻量级中间件，可以作为应用程序与MySQL数据库中间的路由层。可以用来解决MySQL主从库读写分离的路由或MySQL集群的高可用、负载均衡、易扩展等方面。而且对于应用来说是透明的。
本次介绍的是在MySQL主从架构（一主一从）上的应用，主要用于读写分离。程序写操作在主库进行，读操作根据MySQL Router配置分散到主库和备库上，减轻主库压力。架构示意图如下：

1.1MySQL Router主从架构示意图

一、环境准备

服务器：
操作系统：Centos 7.6

主机名	ip	角色	软件
local110	192.168.172.110	MySQL Router	mysql-router-8.0.28
local111	192.168.172.111	MySQL主库	mysql-8.0.28
local112	192.168.172.112	MySQL备库	mysql-8.0.28

环境准备：
1.MySQL搭建好主备库，备库设置只读模式。本次不做多余介绍。可以参考：MySQL主从搭建
2.服务器192.168.172.110参考：安装MySQL，只需要做好第一步安装前的准备即可。因为启动MySQL Router也需要类似的环境。

二、安装MySQL Router

1.下载MySQL Router

登陆MySQL官方网站，进入社区版下载：下载
在这里插入图片描述
本次使用免安装版本（Linux-Generic）进行相关配置，在下载后上传安装包到服务器192.168.172.110上。

2.解压

下载的压缩包为xz类型，创建指定文件夹，将解压后的文件放到指定文件夹下：
（如果有大容量磁盘，建议安装部署到大容量磁盘路径下）

[root@local110 ~]# mkdir -p /data/mysql
[root@local110 ~]# xz -d mysql-router-8.0.28-linux-glibc2.12-x86_64.tar.xz
[root@local110 ~]# tar -xvf mysql-router-8.0.28-linux-glibc2.12-x86_64.tar
[root@local110 ~]# mv mysql-router-8.0.28-linux-glibc2.12-x86_64 /data/mysql/mysql-router-8.0.28
[root@local110 ~]# cd /data/mysql
[root@local110 mysql]# ls
mysql-router-8.0.28

3.配置相关环境变量

将MySQL Router环境变量配置到/etc/profile文件中：

[root@local110 ~]# vim /etc/profile
......
export PATH=$PATH:/data/mysql/mysql-router-8.0.28/bin
[root@local110 ~]# source /etc/profile
[root@local110 ~]# mysqlrouter -help
MySQL Router Ver 8.0.28 for Linux on x86_64 (MySQL Community - GPL)
Copyright (c) 2015, 2022, Oracle and/or its affiliates. 
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Configuration read from the following files in the given order (enclosed
in parentheses means not available for reading):
  (/data/mysql/mysql-router-8.0.28/bin/.././mysqlrouter.conf)
  (/data/mysql/mysql-router-8.0.28/bin/.././mysqlrouter.ini)
Plugins Path:
  /data/mysql/mysql-router-8.0.28/lib/mysqlrouter
Default Log Directory:
  /data/mysql/mysql-router-8.0.28
Default Persistent Data Directory:
  /data/mysql/mysql-router-8.0.28/bin/../var/lib/mysqlrouter
Default Runtime State Directory:
  /data/mysql/mysql-router-8.0.28/bin/../run
 
......

环境变量已生效。从mysqlrouter帮助提示中看到默认配置文件寻找路径及其顺序，插件路径、日志目录、持久化数据目录、运行时状态目录的默认位置等信息。这些配置信息也可以在配置文件中手动指定路径。然后在启动命令mysqlrouter使用–config或-c选项指定自定义的配置文件即可生效。

三、配置MySQL Router

本次安装将持久化数据目录、运行状态目录和配置文件分别创建在解压后的文件夹内统一管理。

[root@local110 ~]# cd /data/mysql/mysql-router-8.0.28
[root@local110 mysql-router-8.0.28]# ls
bin	lib	LICENSE.router	man		README.router	share
[root@local110 mysql-router-8.0.28]# mkdir  data  run  conf  log
[root@local110 mysql-router-8.0.28]# ls
bin		conf	data	lib	LICENSE.router	log	man		README.router	run	share

data：数据持久化目录
run：运行状态目录
conf：配置文件存放目录
log：日志目录

将安装目录下的示例配置文件拷贝到conf目录下：

[root@local110 mysql-router-8.0.28]# cp share/doc/mysqlrouter/sample_mysqlrouter.conf   conf/mysqlrouter.conf

更改整个安装包的属主和属组：

[root@local110 ~]# chown -R mysql:mysql  /data

注意本次直接修改整个data目录下所有文件，只有mysql相关文件。如果目录下有其他程序或目录建议单个进行修改。

编辑MySQL Router的参数文件mysqlrouter.conf。

[root@local110 ~]# cd /data/mysql/mysql-router-8.0.28/conf
[root@local110 conf]# vim mysqlrouter.conf
......
[DEFAULT]
user = mysql
logging_folder = /data/mysql/mysql-router-8.0.28/log
plugin_folder = /data/mysql/mysql-router-8.0.28/lib/mysqlrouter
runtime_folder = /data/mysql/mysql-router-8.0.28/run
data_folder = /data/mysql/mysql-router-8.0.28/data

[logger]
level = INFO
filename = mysqlrouter.log

[routing:basic_failover]
bind_address = 0.0.0.0
bind_port = 7001
mode = read-write
destinations = 192.168.172.111:3306,192.168.172.112:3306

[routing:load_balance]
bind_address = 0.0.0.0
bind_port = 7002
mode = read-only
destinations = 192.168.172.111:3306,192.168.172.112:3306

以上是简单的配置了两个路由策略，分别是[routing:basic_failover]、[routing:load_balance]。第一个表示失败转移，第二个表示负责均衡。其实本次配置的意义是第一个策略用于程序写操作（主库提供写，而且本次设置备库只读，所以主库停掉后写操作将无法进行，如果要求写操作真正实现失败转移，需要的MySQL的主主同步架构，不在本次讨论和测试范围内）。第二个用于程序读操作。
配置的路由主要功能实现在于参数mode，该参数的可选值为read-write或read-only，实际作用非字面意思。主要意义如下：
（1）read-write表示根据参数destinations指定的两个数据库地址，每次访问7001端口时，在两个地址都有效的情况下链接请求总是访问第一个地址，如果第一个地址失效后才会访问第二个地址。而且如果第一个数据库地址恢复有效也不会将其提供给连接请求。需要重启MySQL Router才可重新访问。
（2）read-only表示参数destinations指定的两个数据库地址，每次访问7002端口时，在两个地址都有效的情况下，会轮询访问，比如第一个连接请求访问第一个地址，第二个连接请求访问第二个地址，第三个连接请求又访问第一个地址，等等等等。。。数据库地址失效后的表现与read-write模式不同，如果第一个地址失效后又恢复有效，下次连接请求会重新访问第一个地址。类似于心跳检测机制。

四、启动与连接

1.启动MySQL Router

参数文件配置完成后，正式启动MySQL Router。

[root@local110 ~]# mysqlrouter -c  /data/mysql/mysql-router-8.0.28/conf/mysqlrouter.conf

通过查看日志，观察是否启动成功：

[root@local110 ~]# tailf  /data/mysql/mysql-router-8.0.28/log/mysqlrouter.log

2.测试连接

找一台有MySQL数据库的服务器，命令行远程连接测试
（1）使用7001端口连接，每次连接都会连接到主库上。

[root@localhost ~]# mysql -h 65.26.2.253 -P 7001 -utest -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 480
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| local111   |
+------------+
1 row in set (0.04 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -h 65.26.2.253 -P 7001 -utest -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 481
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| local111   |
+------------+
1 row in set (0.04 sec)

mysql> exit
Bye

（2）使用7002端口连接，明显看出分别连接到了主库和从库上。

[root@localhost ~]# mysql -h 65.26.2.253 -P 7002 -utest -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 482
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| local111   |
+------------+
1 row in set (0.04 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -h 65.26.2.253 -P 7002 -utest -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| local112   |
+------------+
1 row in set (0.04 sec)

mysql> exit
Bye