Maxscale实现Mysql的读写分离

介绍：

Maxscale是mariadb开发的一个MySQL数据中间件，配置简单，能够实现读写分离，并且能根据主从状态实现写库的自动切换，对多个服务器实现负载均衡。

实验环境：

基于gtid的主从同步的基础上进行配置

中间件	192.168.93.20	MaxScale 24.02.5	Rocky8
主服务器	192.168.93.10	Mysql 8.0.41	openEuler
从服务器	192.168.93.11	Mysql 8.0.41	openEluer
从服务器	192.168.93.12	Mysql 8.0.41	openEuler

配置：

1、安装maxscale

下载的话可以先配置maxscale的yum源：/etc/yum.repos.d/mariadb.repo

[mariadb-main]
name = MariaDB Server
baseurl = https://dlm.mariadb.com/repo/mariadb-server/11.rolling/yum/rhel/8/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1
module_hotfixes = 1

[mariadb-maxscale]
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
name = MariaDB MaxScale
baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/8/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
gpgcheck = 1
enabled = 1

[mariadb-tools]
name = MariaDB Tools
baseurl = https://downloads.mariadb.com/Tools/rhel/8/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
gpgcheck = 1
enabled = 1

官方文档上给了脚本的方式配置：

https://mariadb.com/kb/en/library/mariadb-package-repository-setup-and-usage/

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

查看是否又安装包：

[root@Rocky-1 ~]# dnf install maxscale.x86_64

2、配置

安装完成之后会有一个主配置文件/etc/maxscal.cnf，也可在其子配置文件下配置，注意以.cnf结尾

[maxscale]

#开启线程个数，默认为1，设置auto会同cpu核数相同
threads=auto

[dbserver1]
type=server
address=192.168.93.10
port=3306
protocol=MariaDBBackend

[dbserver2]
type=server
address=192.168.93.11
port=3306
protocol=MariaDBBackend

[dbserver3]
type=server
address=192.168.93.12
port=3306
protocol=MariaDBBackend

[MySQL-Monitor]
#监视进程
type=monitor
module=mariadbmon
servers=dbserver1,dbserver2,dbserver3
user=maxscale_monitor
password=Admin@123456
monitor_interval=2s

[Read-Write-Service]

读写分离
type=service
router=readwritesplit
servers=dbserver1,dbserver2,dbserver3
user=maxscale_route
password=Admin@123456
enable_root_user=true

[Read-Write-Listenner]

#监听读写端口
type=listener
service=Read-Write-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306

3、创建用户

创建监控用户：

mysql> create user 'maxscale_monitor'@'192.168.93.%' identified with mysql_native_password by 'Admin@123456';
 

mysql> grant replication slave, replication client on *.* to maxscale_monitor@'192.168.93.%';

创建路由用户：

create user 'maxscale_route'@'192.168.93.%' identified with mysql_native_password by 'Admin@123456';

GRANT SHOW DATABASES ON *.* TO maxscale_route@'192.168.93.%';
GRANT SELECT ON mysql.user TO maxscale_route@'192.168.93.%';
GRANT SELECT ON mysql.db TO maxscale_route@'192.168.93.%';
GRANT SELECT ON mysql.tables_priv TO maxscale_route@'192.168.93.%';
GRANT SELECT ON mysql.columns_priv TO maxscale_route@'192.168.93.%';
GRANT SELECT ON mysql.proxies_priv TO maxscale_route@'192.168.93.%';

配置完成重启服务：

[root@Rocky-1 ~]# maxscale -f /etc/maxscale.cnf.d/maxscale.cnf -U maxscale

#查看端口的监听状态

[root@Rocky-1 ~]# ps -ef | grep maxscale
maxscale   10908       1  0 23:23 ?        00:00:00 maxscale -f /etc/maxscale.cnf.d/maxscale.cnf -U maxscale
root       10921    1619  0 23:25 pts/0    00:00:00 grep --color=auto maxscale

查看运行状态，注册服务，服务监听状态信息

测试

在master上创建一个访问用，已经实现主从复制的前提下，主库创建的用户能同步到从库上

create user 'admin_user'@'%' identified with mysql_native_password by 'Admin@123456';

grant all privileges on *.* to 'admin_user'@'%' with grant option;

#测试连接

主库创建一个数据库，从库马上更新，实验完成！