MySQL配置Maxscale实习读写分离

Maxscale

系统	IP	主机名	服务
Rocky-9.3	192.168.117.10	master	MySQL主
Rocky-9.3	192.168.117.11	node1	MySQL从
Rocky-9.3	192.168.117.12	maxscale	MySQL，Maxscale

注:需要先配置master和node1的主从配置

配置maxscale

//配置mariadbYUM源
[root@maxscale ~]# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 11.2 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg ~
~
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
0 files removed
[root@maxscale ~]# yum makecache

//安装所需软件包
[root@maxscale ~]# yum -y install maxscale
[root@maxscale ~]# yum -y install mysql

//配置server服务器信息
[root@maxscale ~]# vim /etc/maxscale.cnf
[server1]
type=server
address=192.168.117.10
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.117.11
port=3306
protocol=MySQLBackend

//监控配置
[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2   //与上文一致
user=monitor
password=monitor
monitor_interval=2s

配置master

[root@master ~]# mysql -uroot -p000000
//为MaxScale创建用户帐户
mysql> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';
Query OK, 0 rows affected (0.05 sec)

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用户并授权
mysql> CREATE USER 'admin'@'192.168.117.10' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'admin'@'192.168.117.12' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,insert,update,delete on *.* to 'admin'@'192.168.117.10';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on *.* to 'admin'@'192.168.117.12';
Query OK, 0 rows affected (0.00 sec)

//创建监视所需的账号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.00 sec)

mysql> GRANT SUPER, RELOAD on *.* to 'monitor'@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)

在maxscale上测试连接主库

[root@maxscale ~]#  mysql -uadmin -padmin -h'192.168.117.10'
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 10
Server version: 8.0.35 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> 

读写分离

[root@maxscale ~]# vim /etc/maxscale.cnf
//注释只读服务
#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=service_user
#password=service_pw
#router_options=slave

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
password=maxscale

#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=mariadbprotocol
#port=4008

//配置侦听服务
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=mariadbprotocol
port=3306

[root@maxscale ~]# systemctl start maxscale
[root@maxscale ~]# ss -antlp
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port    Process                                                                         
LISTEN    0         128                0.0.0.0:22               0.0.0.0:*        users:(("sshd",pid=970,fd=3))                                                  
LISTEN    0         4096             127.0.0.1:8989             0.0.0.0:*        users:(("maxscale",pid=34043,fd=19))                                           
LISTEN    0         4096                     *:3306             0.0.0.0:*        users:(("cupsd",pid=968,fd=7))                                                 
LISTEN    0         4096                     *:3306                [::]:*        users:(("cupsd",pid=968,fd=6))                                                 
LISTEN    0         4096                     *:3306                   *:*        users:(("maxscale",pid=34043,fd=25))                                           
LISTEN    0         4096                     *:3306                   *:*        users:(("maxscale",pid=34043,fd=29))                                           
LISTEN    0         128                   [::]:22                  [::]:*        users:(("sshd",pid=970,fd=4))                             

[root@maxscale ~]# maxctrl list services
┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service            │ Router         │ Connections │ Total Connections │ Targets          │
├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Read-Write-Service │ readwritesplit │ 0           │ 0                 │ server1, server2 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
[root@maxscale ~]# maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬──────────────────┬──────┬───────────────┐
│ Server  │ Address        │ Port │ Connections │ State            │ GTID │ Monitor       │
├─────────┼────────────────┼──────┼─────────────┼──────────────────┼──────┼───────────────┤
│ server1 │ 192.168.117.10 │ 3306 │ 0           │ Master, Running  │      │ MySQL-Monitor │
├─────────┼────────────────┼──────┼─────────────┼──────────────────┼──────┼───────────────┤
│ server2 │ 192.168.117.11 │ 3306 │ 0           │ Slave, Running   │      │ MySQL-Monitor │
└─────────┴────────────────┴──────┴─────────────┴──────────────────┴──────┴───────────────┘
[root@maxscale ~]# maxctrl list listeners
┌─────────────────────┬──────┬──────┬─────────┬────────────────────┐
│ Name                │ Port │ Host │ State   │ Service            │
├─────────────────────┼──────┼──────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 3306 │ ::   │ Running │ Read-Write-Service │
└─────────────────────┴──────┴──────┴─────────┴────────────────────┘