文章目录一、maxscale概述MaxScale 数据库代理工具简介核心功能主要特点典型使用场景二、读写分离1、环境说明2、mysql主从复制配置3、maxscale安装4、配置maxscale一、maxscale概述MaxScale 数据库代理工具简介MaxScale 是 MariaDB 公司开发的智能数据库代理和负载均衡工具专门为 MySQL/MariaDB 数据库设计。核心功能负载均衡在多个数据库服务器间分配查询负载支持读写分离主从架构高可用性自动故障检测和故障转移支持主从切换和自动重连查询路由基于SQL语句内容的路由决策可将特定查询定向到特定服务器安全功能数据库防火墙查询过滤和重写连接加密主要特点完全兼容 MySQL 协议支持多种路由模块读/写分离、分片等可插拔架构支持自定义模块开发提供REST API进行监控和管理支持二进制日志服务器功能典型使用场景作为MySQL/MariaDB集群的入口点实现透明的读写分离数据库连接池管理在不修改应用代码的情况下扩展数据库架构提供数据库访问的审计和监控层MaxScale是maridb开发的一个mysql数据中间件其配置简单能够实现读写分离并且可以根据主从状态实现写库的自动切换。官网https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-20/下载地址https://dlm.mariadb.com/3927179/MaxScale/24.02.3/rhel/9/x86_64/maxscale-24.02.3-1.rhel.9.x86_64.rpmhttps://dlm.mariadb.com/3993858/MaxScale/24.02.4/rhel/8/x86_64/maxscale-24.02.4-1.rhel.8.x86_64.rpm二、读写分离1、环境说明数据库角色IP应用与系统版本master192.168.100.165rocky linux9.4 mysql-8.0.36slave192.168.100.167rocky linux9.4 mysql-8.0.36slave2192.168.100.166rocky linux9.4 mysql-8.0.36maxscale192.168.166.168rocky linux9.4 maxscale24.02.3-GA2、mysql主从复制配置分别在主从三台服务器上安装mysql8并配置主从复制。#yum install -y mariadb mariadb-server #systemctl start mariadb yum install -y mysql mysql-server ##由于认证插件问题需要在master服务器使用下述命令进行更改密码的验证插件。 ALTER USERslave% IDENTIFIED WITH mysql_native_password BY password; FLUSH PRIVILEGES; ###修改认证插件 ##my.cnf default-authentication-pluginmysql_native_password3、maxscale安装##导入maxscale数据包 ##执行安装命令 yum localinstall -y maxscale-24.02.3-1.rhel.9.x86_64.rpm4、配置maxscale登录到主库[rootmaster ~]# mysql -uroot 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 14 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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. #创建maxscale用户密码是maxscale mysql CREATE USER maxscale% IDENTIFIED BY maxscale; Query OK, 0 rows affected (0.00 sec) #授权maxscale可以查询所有数据库 mysql GRANT SELECT ON mysql.* TO maxscale%; Query OK, 0 rows affected (0.01 sec) #授权可以看所有数据库 mysql GRANT SHOW DATABASES ON *.* TO maxscale%; Query OK, 0 rows affected (0.00 sec) #创建admin用户可以在maxscale上登录 mysql CREATE USER admin192.168.100.% IDENTIFIED BY admin; Query OK, 0 rows affected (0.01 sec)在maxscale上安装mysql[rootmaxscale ~]# yum -y install mysql [rootmaxscale ~]# which mysql /usr/bin/mysql 可省略[rootlocalhost yum.repos.d]# cat /etc/my.cnf.d/client.cnf # # These two groups are read by the client library # Use it for options that affect all clients, but not the server # [client] ssl 0 ###添加该配置 # This group is not read by mysql client library, # If you use the same .cnf file for MySQL and MariaDB, # use it for MariaDB-only client options 可省略[client-mariadb] #登录到master [rootmaxscale ~]# mysql -uadmin -padmin -h192.168.100.165 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 15 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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 show databases; -------------------- | Database | -------------------- | information_schema | | performance_schema | -------------------- 2 rows in set (0.00 sec) mysql exit Bye回到master主库设置增删改查权限mysql GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON *.* TO admin192.168.100.%; Query OK, 0 rows affected (0.00 sec)在maxscale上修改配置文件[rootmaxscale ~]# vim /etc/maxscale.cnf #先看有无这个 [maxscale] threadsauto #修改后端服务器地址 [server1] typeserver address192.168.100.165 port3306 protocolMySQLBackend [server2] typeserver address192.168.100.167 port3306 protocolMySQLBackend [server3] typeserver address192.168.100.166 port3306 protocolMySQLBackend #配置监控 [MySQL-Monitor] typemonitor modulemariadbmon serversserver1,server2,server3 usermonitor passwordmonitor monitor_interval2s #注释掉只读配置 #[Read-Only-Service] #typeservice #routerreadconnroute #serversserver2 #usermaxscale #passwordmaxscale #router_optionsslave #修改读写分离服务 [Read-Write-Service] typeservice routerreadwritesplit serversserver1,server2,server3 usermaxscale passwordmaxscale # mariadb的版本 version_string 10.5 version_string 8.0 #配置listener #注释掉只读 #[Read-Only-Listener] #typelistener #serviceRead-Only-Service #protocolmariadbprotocol #port4008 #修改读写分离 [Read-Write-Listener] typelistener serviceRead-Write-Service protocolmariadbprotocol #(伪装成数据库端口3306) port3306MariaDB数据库配置[maxscale] threadsauto # 增加 MariaDB 兼容性配置 mariadb_compatible1 # 启用 MariaDB 兼容模式 log_info1 # 记录详细日志便于调试 # 后端 MariaDB 服务器配置 [server1] typeserver address192.168.100.128 port3306 protocolMariaDBBackend # 明确指定 MariaDB 后端协议原 MySQLBackend 兼容但建议显式修改 priority1 # 可设置优先级数字越小优先级越高主库建议设为1 [server2] typeserver address192.168.100.129 port3306 protocolMariaDBBackend priority2 # 从库优先级略高 [server3] typeserver address192.168.100.130 port3306 protocolMariaDBBackend priority3 # 从库优先级 # MariaDB 监控配置适配 Galera 集群或主从架构 [MariaDB-Monitor] typemonitor modulemariadbmon # MariaDB 专用监控模块原配置正确补充细节 serversserver1,server2,server3 usermonitor # 需在所有 MariaDB 节点创建该用户并授权 passwordmonitor monitor_interval2s # 增加监控特性根据实际架构选择 # 若为 Galera 集群启用以下配置 # galera_monitor1 # 若为主从架构启用以下配置 # detect_stale_master1 # 检测失效主库 # failover_timeout60s # 故障转移超时时间 # 读写分离服务适配 MariaDB 读写逻辑 [Read-Write-Service] typeservice routerreadwritesplit # 读写分离路由MariaDB 推荐使用 serversserver1,server2,server3 usermaxscale # 需在所有 MariaDB 节点创建该用户并授权用于路由验证 passwordmaxscale version_string10.6 # 匹配实际 MariaDB 版本如 10.6、10.11 等避免协议兼容问题 # 增加 MariaDB 特有优化 master_failure_modefailover # 主库故障时自动故障转移 slave_selection_criteriaLEAST_CURRENT_OPERATIONS # 选择负载最低的从库 strict_multi_master0 # 禁用多主严格模式适合主从架构 disable_sescmd_history1 # 禁用会话命令历史减少内存占用 # 读写分离监听器 [Read-Write-Listener] typelistener serviceRead-Write-Service protocolMariaDBProtocol # 明确使用 MariaDB 协议原 mariadbprotocol 兼容建议标准化 port3306 # 伪装数据库端口客户端可直接用 3306 连接 address0.0.0.0 # 允许所有地址访问生产环境建议指定具体IP切换到主库创建monitor用户mysql CREATE USER monitor% IDENTIFIED BY monitor; Query OK, 0 rows affected (0.01 sec) #再添加授权 mysql GRANT REPLICATION CLIENT on *.* to monitor%; Query OK, 0 rows affected (0.00 sec) mysql GRANT REPLICATION SLAVE on *.* to monitor%; Query OK, 0 rows affected (0.01 sec) mysql GRANT SUPER,RELOAD on *.* to monitor%; Query OK, 0 rows affected, 1 warning (0.01 sec)启动服务[rootmaxscale ~]# systemctl start maxscale查看端口[rootmaxscale ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 4096 127.0.0.1:8989 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 4096 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* [rootmaxscale ~]# ss -antlp State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 4096 127.0.0.1:8989 0.0.0.0:* users:((maxscale,pid15450,fd19)) LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:((sshd,pid792,fd3)) LISTEN 0 4096 *:3306 *:* users:((maxscale,pid15450,fd27)) LISTEN 0 128 [::]:22 [::]:* users:((sshd,pid792,fd4))查看有哪些服务[rootmaxscale ~]# maxctrl list services ┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Targets │ ├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤ │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │ └────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘查看后台服务器有哪些[rootmaxscale ~]# maxctrl list servers ┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┬──────┬───────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤ │ server1 │ 192.168.100.165 │ 3306 │ 0 │ Master, Running │ │ MySQL-Monitor │ ├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤ │ server2 │ 192.168.100.167 │ 3306 │ 0 │ Slave, Running │ │ MySQL-Monitor │ ├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤ │ server3 │ 192.168.100.166 │ 3306 │ 0 │ Slave, Running │ │ MySQL-Monitor │ └─────────┴─────────────────┴──────┴─────────────┴─────────────────┴──────┴───────────────┘测试用客户机连接maxscaleusernameadmin passwordadmin会发现进行读操作时是在slave的从数据库上执行在进行写操作时是在master主数据库上执