1
2
3
4
MySQL Router
轻量级中间件，提供应用程序连接目标的故障转移。MySQL Router是一个轻量级的中间件，可以提供负载均衡和应用连接的故障转移。它是MySQL团队为MGR量身打造的，通过使用Router和Shell,用户可以利用MGR实现完整的数据库层的解决方案。如果您在使用MGR，请一定配合使用Router和Shell，可以理解为它们是为MGR而生的，会配合MySQl 的开发路线图发展的工具。
MySQL Shell
新的MySQL客户端，多种接口模式。可以设置群组复制及Router。MySQL Shell是MySQL团队打造的一个统一的客户端， 它可以对MySQL执行数据操作和管理。它支持通过JavaScript，Python，SQL对关系型数据模式和文档型数据模式进行操作。使用它可以轻松配置管理InnoDB Cluster

1 安装mysql-router

1
2
3
4
5
6
7
8
9
10
11
12
13

# 下载：https://dev.mysql.com/downloads/router/
# wget https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-8.0.33-linux-glibc2.28-x86_64.tar.gz
# tar xzf mysql-router-8.0.33-linux-glibc2.28-x86_64.tar.gz
# mv mysql-router-8.0.33-linux-glibc2.28-x86_64 /usr/local/mysq-router

# 配置相关环境变量
# vim /etc/profile
export PATH=$PATH:/usr/local/mysq-router/bin
source /etc/profile

# 查看版本
# mysqlrouter -V

2. 编写配置文件并启动

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
- 创建目录  data run conf log
# cd /usr/local/mysq-router
# mkdir  data  run  conf  log
# cp share/doc/mysqlrouter/sample_mysqlrouter.conf conf/mysqlrouter.conf
# useradd -M -s /sbin/nologin mysql
# chown -R mysql:mysql  data

# vim /usr/local/mysq-router/conf/mysqlrouter.conf
[DEFAULT]
logging_folder = /usr/local/mysq-router/log
plugin_folder = /usr/local/mysq-router/lib/mysqlrouter
config_folder = /usr/local/mysq-router/conf
runtime_folder = /usr/local/mysq-router/run
data_folder = /usr/local/mysq-router/data
#keyring_path = /var/lib/keyring-data
#master_key_path = /var/lib/keyring-key
max_total_connections = 10240
thread_stack_size=512

[logger]
level = INFO
filename = mysqlrouter.log
timestamp_precision = second

# 主节点故障转移配置
[routing:basic_failover]
bind_address=192.168.64.33
bind_port=6002
mode=read-write
# 主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations=192.168.64.32:3306,192.168.64.33:3306

# 从节点负载均衡配置
[routing:balancing]
bind_address=192.168.64.33
bind_port=6001
# 连接超时时间
connect_timeout = 3
# 最大连接数
max_connections = 4000
max_Connect_Errors = 1000000
mode=read-only
# 后端服务器地址
destinations=192.168.64.33:3306,192.168.64.34:3306

[keepalive]
interval = 60

[http_server]
port=1806
[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm
[rest_router]
require_realm=default_auth_realm
[rest_api]
[http_auth_backend:default_auth_backend]
backend=file
filename=/usr/local/mysq-router/pwd/mysqlrouter.pwd
[rest_routing]
require_realm=default_auth_realm
[rest_metadata_cache]
require_realm=default_auth_realm

# 启动MySQL Router
# mysqlrouter -c  /usr/local/mysq-router/conf/mysqlrouter.conf
# nohup mysqlrouter -c  /usr/local/mysq-router/conf/mysqlrouter.conf &
# 查看日志
# tail -f /usr/local/mysq-router/log/mysqlrouter.log

# 配置启动文件
# cat /usr/lib/systemd/system/mysqlrouter.service
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
 
[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/local/mysq-router/bin/mysqlrouter -c /usr/local/mysq-router/conf/mysqlrouter.conf
PrivateTmp=true
Restart=on-failure
LimitNOFILE=102400
 
[Install]
WantedBy=multi-user.target


# systemctl enable mysqlrouter
# systemctl start mysqlrouter

3. 添加用户 访问验证

1
2
3
4
5
6
7
8
9
10
11
# 添加用户:建组的时候已经创建
reate user 'mysqlrouter'@'192.168.64.%' identified by 'Mysqlrouter@2023!';
# mysql> grant all privileges on test.* to 'mysqlrouter'@'192.168.64.%' with grant option;
## mysql> create user 'mysqlrouter'@'%' identified by 'Mysqlrouter@2023!';
## mysql> GRANT  ALL ON  test.*  TO 'mysqlrouter'@'%';

# 测试是否可以访问得到：[6001 端口只读，轮询方式]
# mysql  -h 192.168.64.33 -P 6001 -umysqlrouter -pMysqlrouter@2023! -e  "select @@hostname"

# 测试是否可以访问得到：[6002 端口读写]
# mysql  -h 192.168.129.33 -P 6002 -umysqlrouter -pMysqlrouter@2023! -e  "select @@hostname"

4. 写入插入语句成功，通过6001端口则不成功, 通过6002端口则不成功

1
2
3
4
5
6
7
8
9
10
11
12
#  mysql  -h 192.168.64.33 -P 6001 -umysqlrouter -pMysqlrouter@2023!   "insert into repl.test values (6,'写入开发3');"
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

# mysql  -h 192.168.129.33 -P 6002 -umysqlrouter -pMysqlrouter@2023! -e  "insert into repl.test values (6,'写入开发3');"  
select * from test;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 测试         |
|  2 | 写入测试     |
|  3 | 写入开发     |
+----+--------------+

5. 把mysql__32的mysql 停掉

1
2
3
4
5
6
7
8
9
# 通过6001端口会直接跳过server1
# mysql  -h 192.168.64.33 -P 6001 -umysqlrouter -pMysqlrouter@2023! -e "select @@hostname"

# 通过6002端口会直接跳过server1,但不是新的PRIMARY库
# mysql  -h 192.168.64.33 -P 6002 -umysqlrouter -pMysqlrouter@2023!  -e  "select @@hostname"

# 解决写操作不是新的主库
# 第一台主库DOWN后，MySQL Router如何处理？默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库，稍后如果第一台主库被修复后，那么默认仍然连接第二台为主库进行读写，不会自动切回到第一台主库。
# 如果第一台主数据库被修复后，又希望切换回第一台主库，怎么办？可以重启MySQL Router

6. 开启防火墙

1
2
3
4
5
6
7
8
9
systemctl status firewalld
systemctl start firewalld
firewall-cmd --reload
firewall-cmd --list-all
firewall-cmd --state

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=6001/tcp --permanent
firewall-cmd --zone=public --add-port=6002/tcp --permanent

7. mysqlrouter配置api

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# 生产环境
cd /usr/local/mysq-router/conf
cp mysqlrouter.conf mysqlrouter.conf-202312140830

# 编辑mysqlrouter加入如下：
vim mysqlrouter.conf
...
# 最大连接数
max_connections = 4000
max_Connect_Errors = 1000000
mode=read-only
# 后端服务器地址
destinations=192.168.64.33:3306,192.168.64.34:3306
...
[http_server]
port=1806
[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm
[rest_router]
require_realm=default_auth_realm
[rest_api]
[http_auth_backend:default_auth_backend]
backend=file
filename=/usr/local/mysq-router/pwd/mysqlrouter.pwd
[rest_routing]
require_realm=default_auth_realm
[rest_metadata_cache]
require_realm=default_auth_realm
...

mkdir /usr/local/mysq-router/pwd
touch /usr/local/mysq-router/pwd/mysqlrouter.pwd

/usr/local/mysq-router/bin/mysqlrouter_passwd set /usr/local/mysq-router/pwd/mysqlrouter.pwd mysqlrouter
# 测试：Mysqlrouter@123
/usr/local/mysq-router/bin/mysqlrouter_passwd list /usr/local/mysq-router/pwd/mysqlrouter.pwd
# mysqlrouter:$5$1/2/H5.07K8Z7NTM$HawD8ScC8KE4t1WpheL5FNuvXPZI5tzIUbEplmKoiT9

systemctl restart mysqlrouter.service

curl -u mysqlrouter:MysqlrouterP@123 http://192.168.129.62:1806/api/20190715/swagger.json
curl -u mysqlrouter:MysqlrouterP@123 http://192.168.129.62:1806/api/20190715/routes/balancing/config

8. 遇到问题

8.1 ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

1
2
通过mysqlrouter连接数据库报：ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0，单独连接集群数据库正常
mysql-router日志报：processor failed: Too many open files (generic:24)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
解决方法一：
# cat /etc/sysctl.conf 
vm.swappiness = 0
net.ipv4.neigh.default.gc_stale_time = 120

# #关闭ipv6
net.ipv6.conf.all.disable_ipv6 = 0
net.ipv6.conf.default.disable_ipv6 = 0
#net.ipv6.conf.lo.disable_ipv6 = 1
# ## 避免放大攻击
net.ipv4.icmp_echo_ignore_broadcasts = 1
# ## 开启恶意icmp错误消息保护
net.ipv4.icmp_ignore_bogus_error_responses = 1
# ##关闭路由转发
net.ipv4.ip_forward = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
# ##开启反向路径过滤
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1
#net.ipv4.conf.all.rp_filter = 0
#net.ipv4.conf.default.rp_filter = 0
#net.ipv4.conf.default.arp_announce = 2
#net.ipv4.conf.lo.arp_announce = 2
#net.ipv4.conf.all.arp_announce = 2
# ##处理无源路由的包
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
# ##关闭sysrq功能
kernel.sysrq = 0
# # 开启SYN洪水攻击保护
# see details in https://help.aliyun.com/knowledge_detail/41334.html
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_max_tw_buckets = 5000
net.ipv4.tcp_max_syn_backlog = 1024
net.ipv4.tcp_synack_retries = 2
# #开启重用。允许将TIME-WAIT sockets 重新用于新的TCP 连接
net.ipv4.tcp_tw_reuse = 0
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 1
# #启用timewait 快速回收
net.ipv4.tcp_tw_recycle = 0
# # 确保无人能修改路由表
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
# #修改消息队列长度
kernel.msgmnb = 65536
kernel.msgmax = 65536

net.core.somaxconn=32768

fs.file-max = 100000000     #设置些项
net.ipv4.tcp_timestamps = 1
vm.overcommit_memory = 1

kernel.sysrq = 1

# 这个参数表示内核套接字接受缓存区默认的大小
net.core.rmem_default = 33554432
# 这个参数表示内核套接字接受缓存区的最大大小
net.core.rmem_max = 33554432

fs.inotify.max_user_watches = 1280000
fs.inotify.max_user_instances = 512

sysctl -p

# 进行系统优化
ulimit -a
ulimit -SHn 65535
# cat /etc/security/limits.conf 
* soft nofile 655360
* hard nofile 131072
* soft nproc 655350
* hard nproc 655350
* seft memlock unlimited
* hard memlock unlimitedd

些方法未解决问题，使用第二种
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
解决方法二：
# cat /usr/lib/systemd/system/mysqlrouter.service
...
[Service]
Type=simple
User=root
Group=root 
ExecStart=/usr/local/mysq-router/bin/mysqlrouter -c /usr/local/mysq-router/conf/mysqlrouter.conf
PrivateTmp=true
Restart=on-failure
LimitNOFILE=102400    #Service新增
...

systemctl daemon-reload
systemctl restart mysqlrouter.service