1 内容概述
MySQL Router是MySQL官方提供的一个轻量级中间件,可以作为应用程序与MySQL数据库中间的路由层。可以用来解决MySQL主从库读写分离的路由或MySQL集群的高可用、负载均衡、易扩展等方面。而且对于应用来说是透明的。
本次介绍的是在MySQL主从架构(一主一从)上的应用,主要用于读写分离。程序写操作在主库进行,读操作根据MySQL Router配置分散到主库和备库上,减轻主库压力。架构示意图如下:
2 环境准备
服务器:
操作系统:Centos 7.6
环境准备:
1.MySQL搭建好主备库,备库设置只读模式。本次不做多余介绍。可以参考:MySQL主从搭建
2.服务器192.168.172.110参考:安装MySQL,只需要做好第一步安装前的准备即可。因为启动MySQL Router也需要类似的环境。
3 安装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选项指定自定义的配置文件即可生效。
4 配置MySQL Router
1 创建目录 data run conf log
本次安装将持久化数据目录、运行状态目录和配置文件分别创建在解压后的文件夹内统一管理。
[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相关文件。如果目录下有其他程序或目录建议单个进行修改。
2 编辑配置文件
编辑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模式不同,如果第一个地址失效后又恢复有效,下次连接请求会重新访问第一个地址。类似于心跳检测机制。
5 启动与连接
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 - GPLCopyright (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 - GPLCopyright (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 - GPLCopyright (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 - GPLCopyright (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
6 总结
本次测试主要是针对MySQL主从复制架构的读写分离要求。程序写操作在没有批量写入数据情况下,一般不会达到性能瓶颈。主要在于读操作,读写分离后可以减轻对主库读的压力。但是对于主从同步要求较高,否则会出现数据查询不一致现象。