江苏龙网

  1. 主页 > 生活百科 > >

MaxScale 关于Linux下MySQL主备集群负载均衡之读写分离( 三 )

读写分离

创建监控用户和路由用户
创建监控用户maxscalemon，用于登录后端服务器，检查服务器的状态
grant replication slave,replication client on *.* to maxscalemon@"%" identified by "liruilong";
 

  • replication slave 能够同步数据，查看从服务器上slave的状态；
  • replication client 可以获取数据库服务的状态(数据库服务是否允许，主从是否正常)
MariaDB [(none)]> grant replication slave,replication client on *.* to maxscalemon@"%" identified by "liruilong";Query OK, 0 rows affected (0.00 sec) 
创建路由用户maxscalerouter,检测客户端的用户名和密码在后端数据库中是否存在
 
  • 只是检查用户是否存在，所以此用户只需对mysql库下表有查询权限即可
MariaDB [(none)]> grant select on mysql.* to maxscaleroute@"%" identified by "liruilong";Query OK, 0 rows affected (0.00 sec) 
在mysql库的user表中，查看监控用户和路由用户
MariaDB [(none)]> select user,host from mysql.user;| user | host || maxscalemon | % || maxscaleroute | % || repluser | % || root | 127.0.0.1 || root | ::1 || root | localhost || root | vms153.liruilongs.github.io |7 rows in set (0.00 sec)MariaDB [(none)]>
在154从库的mysql库的user表中，查看同步过去监控用户和路由用户
┌──[root@vms154.liruilongs.github.io]-[~]└─$mysql -uroot -pliruilong -e'select user,host from mysql.user;'| user | host || maxscalemon | % || maxscaleroute | % || tom | % || root | 127.0.0.1 || root | ::1 || root | localhost || root | vms154.liruilongs.github.io |┌──[root@vms154.liruilongs.github.io]-[~]└─$
启动MaxScale服务
┌──[root@vms152.liruilongs.github.io]-[~]└─$maxscale -f /etc/maxscale.cnf -U maxscale┌──[root@vms152.liruilongs.github.io]-[~]└─$netstat -ntulp | grep maxscaletcp 0 0 127.0.0.1:8989 0.0.0.0:* LISTEN 1169/maxscaletcp6 0 0 :::4006 :::* LISTEN 1169/maxscale┌──[root@vms152.liruilongs.github.io]-[~]└─$ps -C maxscalePID TTY TIME CMD1169 ? 00:00:00 maxscale
查看日志，配置文件有个报错，解决下
┌──[root@vms152.liruilongs.github.io]-[~]└─$tail -n 5 /var/log/maxscale/maxscale.log2022-09-29 22:38:24 error : Monitor timed out when connecting to server server2[192.168.26.26.154:3306] : 'Unknown MySQL server host '192.168.26.26.154' (-2)'2022-09-29 22:38:24 notice : [mariadbmon] Selecting new master server.2022-09-29 22:38:24 warning: [mariadbmon] No running master candidates detected and no master currently set. Accepting a non-running server as master.2022-09-29 22:38:24 notice : [mariadbmon] Setting 'server1' as master.2022-09-29 22:39:15 warning: [mariadbmon] The current master server 'server1' is no longer valid because it has been down over 5 (failcount) monitor updates and it does not have any running slaves, but there is no valid alternative to swap to.┌──[root@vms152.liruilongs.github.io]-[~]└─$kill -9 1169┌──[root@vms152.liruilongs.github.io]-[~]└─$vim /etc/maxscale.cnf┌──[root@vms152.liruilongs.github.io]-[~]└─$maxscale -f /etc/maxscale.cnf -U maxscale┌──[root@vms152.liruilongs.github.io]-[~]└─$测试 MaxScale检查全局配置
使用maxctrl show maxscale命令查看全局maxscale配置 。
┌──[root@vms152.liruilongs.github.io]-[~]└─$maxctrl show maxscale┌──────────────┬───────────────────────────────────────────────────────┐│ Version │ 2.5.21 │├──────────────┼───────────────────────────────────────────────────────┤│ Commit │ eb659891d7b507958f3c5f100d1ebe5f0f68afaf │├──────────────┼───────────────────────────────────────────────────────┤│ Started At │ Sun, 09 Oct 2022 14:50:14 GMT │├──────────────┼───────────────────────────────────────────────────────┤│ Activated At │ Sun, 09 Oct 2022 14:50:14 GMT │├──────────────┼───────────────────────────────────────────────────────┤│ Uptime │ 43 │├──────────────┼───────────────────────────────────────────────────────┤│ Parameters │ { ││ │ "admin_auth": true, ││ │ "admin_enabled": true, ││ │ "admin_gui": true, ││ │ "admin_host": "127.0.0.1", ││ │ "admin_log_auth_failures": true, ││ │ "writeq_low_water": 8192 ││ │ } │└──────────────┴───────────────────────────────────────────────────────┘┌──[root@vms152.liruilongs.github.io]-[~]└─$


推荐阅读


上一篇：Macbook同时访问内外网

下一篇：聊聊软件开发，Java介绍和运行机制