MySQL主从读写分离之MaxScale（openEuler版超详解析）

实验目的：

使用MariaDB MaxScale实现读写分离

实验过程：

实验环境：（不会实现的主从同步的，之前的文章中有详细的介绍）

服务器	地址
master	192.168.27.137
node1	192.168.27.139
node2	192.168.27.140
MaxScale	192.168.27.141

实验步骤：

1.检查一主两从是否正常并进入从库配置文件中（/etc/my.cnf）添加read_only=1

mysql>show slave status \G

2.在主库master中创建监控和路由用户（创建用户并授权）

mysql> create user 'maxscale_monitor'@'%' identified with mysql_native_password by 'Admin@123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave, replication client on *.* to maxscale_monitor@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'maxscale_route'@'%' identified with mysql_native_password by 'Admin@123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SHOW DATABASES ON *.* TO maxscale_route@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON mysql.user TO maxscale_route@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON mysql.db TO maxscale_route@'%'; 
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON mysql.tables_priv TO maxscale_route@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON mysql.columns_priv TO maxscale_route@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON mysql.proxies_priv TO maxscale_route@'%';
Query OK, 0 rows affected (0.00 sec)

3.下载安装docker并启动服务（由于openEuler存在兼容性问题所以我们选用docker进行安装MaxScale）

[root@localhost ~]# dnf install docker -y
[root@localhost ~]# systemctl enable --now docker

4.启动一个新的容器并在后台运行

[root@localhost ~]#  docker run -d --name mxs -p 8989:8989 -p 3306:3306 -v /data/maxscale/maxscale.cnf:/etc/maxscale.cnf.d mariadb/maxscale:latest
Unable to find image 'mariadb/maxscale:latest' locally
latest: Pulling from mariadb/maxscale
7ecefaa6bd84: Pull complete 
ca0e00deb174: Pull complete 
a477bc0ba89b: Pull complete 
1197a5109a4f: Pull complete 
07a9436e0cbc: Pull complete 
ea7a1629d291: Pull complete 
22d7399fd0e3: Pull complete 
9de24e307d1e: Pull complete 
4b69deb551d5: Pull complete 
099ea1430764: Pull complete 
ebc519f0dc79: Pull complete 
Digest: sha256:5c658141992f1b83580759137bbe1b881ebe73e07c85819e46a8a14f332ff690
Status: Downloaded newer image for mariadb/maxscale:latest
f28d964d1e026f836fffe1027179efda2b789b527ffa62807503e6100f0170e2

5.查看容器并进入容器查看相关日志

[root@localhost ~]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mariadb/maxscale    latest              832603ab534f        2 months ago        911MB
[root@localhost ~]# docker exec -it mxs /bin/bash
[root@f28d964d1e02 /]# maxscale --version
MaxScale 23.08.4
[root@f28d964d1e02 /]# cat /var/log/maxscale/maxscale.log 
MariaDB MaxScale  /var/log/maxscale/maxscale.log  Thu Mar  7 09:17:21 2024
----------------------------------------------------------------------------
2024-03-07 09:17:21   notice : /var/lib/maxscale/maxscale.cnf.d does not exist, not reading.
2024-03-07 09:17:21   notice : Using up to 506.4MiB of memory for query classifier cache
2024-03-07 09:17:21   notice : syslog logging is disabled.
2024-03-07 09:17:21   notice : maxlog logging is enabled.
2024-03-07 09:17:21   notice : Host: 'f28d964d1e02' OS: Linux@5.10.0-182.0.0.95.oe2203sp3.x86_64, #1 SMP Sat Dec 30 13:10:36 CST 2023, x86_64 with 2 processor cores (2.00 available).
2024-03-07 09:17:21   notice : Total main memory: 3.3GiB (3.3GiB usable).
2024-03-07 09:17:21   notice : MaxScale is running in process 18
2024-03-07 09:17:21   notice : MariaDB MaxScale 23.08.4 started (Commit: 6d7aa94fc08d4f60d411709f6bdc325ce403d4d7)
2024-03-07 09:17:21   notice : Configuration file: /etc/maxscale.cnf
2024-03-07 09:17:21   notice : Log directory: /var/log/maxscale
2024-03-07 09:17:21   notice : Data directory: /var/lib/maxscale
2024-03-07 09:17:21   notice : Module directory: /usr/lib64/maxscale
2024-03-07 09:17:21   notice : Service cache: /var/cache/maxscale
2024-03-07 09:17:21   notice : Working directory: /var/log/maxscale
2024-03-07 09:17:21   notice : Query classification results are cached and reused. Memory used per thread: 506.4MiB
2024-03-07 09:17:21   notice : Password encryption key file '/var/lib/maxscale/.secrets' not found, using configured passwords as plaintext.
2024-03-07 09:17:21   notice : Module 'pp_sqlite' loaded from '/usr/lib64/maxscale/libpp_sqlite.so'.
2024-03-07 09:17:21   notice : [MariaDBProtocol] Parser plugin loaded.
2024-03-07 09:17:21   notice : Using HS256 for JWT signatures
2024-03-07 09:17:21   notice : Started REST API on [0.0.0.0]:8989
2024-03-07 09:17:21   notice : No services defined in any of the configuration files
2024-03-07 09:17:21   notice : MaxScale started with 1 worker threads.

6.配置maxscale

在/etc/maxscale.cnf.d新建一个配置my.cnf(可以借鉴/etc/maxscale.cnf.template中的内容)
[root@localhost ~]# cat /data/maxscale/maxscale.cnf/my.cnf 
[maxscale]
threads=auto

[dbserv1]
type=server
address=192.168.27.137
port=3306
protocol=MariaDBBackend

[dbserv2]
type=server
address=192.168.27.139
port=3306
protocol=MariaDBBackend

[dbserv3]
type=server
address=192.168.27.140
port=3306
protocol=MariaDBBackend

[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=dbserv1, dbserv2, dbserv3
user=maxscale_monitor
password=Admin@123456
monitor_interval=2s

[Read-Write-Service]
type=service
router=readwritesplit
servers=dbserv1,dbserv2,dbserv3
user=maxscale_route
password=Admin@123456
enable_root_user=true

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306

7.重启maxscale服务并进行查看

[root@localhost ~]# docker exec -it mxs /bin/bash//进入容器
[root@f28d964d1e02 /]#  maxscale -f /etc/maxscale.cnf.d/my.cnf -U maxscale//重启服务
[root@f28d964d1e02 /]# maxctrl list servers//查看运行状态
┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬──────┬───────────────┐
│ Server  │ Address        │ Port │ Connections │ State           │ GTID │ Monitor       │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤
│ dbserv1 │ 192.168.27.137 │ 3306 │ 0           │ Master, Running │      │ MySQL-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤
│ dbserv2 │ 192.168.27.139 │ 3306 │ 0           │ Slave, Running  │      │ MySQL-Monitor │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤
│ dbserv3 │ 192.168.27.140 │ 3306 │ 0           │ Slave, Running  │      │ MySQL-Monitor │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴──────┴───────────────┘
[root@f28d964d1e02 /]#  maxctrl list services//查看注册服务
┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
│ Service            │ Router         │ Connections │ Total Connections │ Targets                   │
├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
│ Read-Write-Service │ readwritesplit │ 0           │ 0                 │ dbserv1, dbserv2, dbserv3 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
[root@f28d964d1e02 /]#  maxctrl list listeners Read-Write-Service//查看服务监听状态信息
┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
│ Name                │ Port │ Host    │ State   │ Service            │
├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 3306 │ 0.0.0.0 │ Running │ Read-Write-Service │
└─────────────────────┴──────┴─────────┴─────────┴────────────────────┘