环境
| 主机IP | 角色 | 系统 |
| 192.168.1.101 | 主服务器 | ubuntu22.04 |
| 192.168.1.102 | 从服务器 | ubuntu22.04 |
| 192.168.1.103 | maxscale服务器 | ubuntu22.04 |
前提：主从架构需部署完成
创建监控用户
[主从都要执行]
mysql -u root -p
CREATE USER 'maxscale_mon'@'192.168.1.103' IDENTIFIED BY 'wakamizu';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscale_mon'@'192.168.1.103';创建路由用户
主服务器执行
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'wakamizu';
#赋予maxscale查看数据库相关信息，如表权限等
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
GRANT SELECT ON mysql.* TO 'maxscale'@'%';
#授予测试数据库所有权限，按需授予
GRANT ALL PRIVILEGES ON employees.* TO 'maxscale'@'%';从服务器执行
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'wakamizu';
#赋予maxscale查看数据库相关信息，如表权限等
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
GRANT SELECT ON mysql.* TO 'maxscale'@'%';
#授予测试数据库查询权限，按需授予
GRANT SELECT ON employees.* TO 'maxscale'@'%';下载安装maxscale
apt install ./maxscale-23.08.5-1.ubuntu.jammy.x86_64.deb -y生成加密密码
#生成加密文件，默认路径在/var/lib/maxscale/.secrets
maxkeys
#生成加密密码
maxpasswd wakamizu配置maxscale
vim /etc/maxscale.cnf#配置主服务器
[server1]
type = server
address = 192.168.1.101
port = 3306
#配置从服务器
[server2]
type = server
address = 192.168.1.102
port = 3306
#配置监控器
[MariaDB-Monitor]
type = monitor
module = mariadbmon
servers = server1,server2
user = maxscale_mon
password = E38A4DC32AF8692DB663BD5DABC88A254F4823875FE2F5E7D48784292F9965AC
monitor_interval = 2s
#配置读写分离池
[Read-Write-Service]
type = service
router = readwritesplit
servers = server1,server2
user = maxscale
password = E38A4DC32AF8692DB663BD5DABC88A254F4823875FE2F5E7D48784292F9965AC
enable_root_user = true
[Read-Write-Listener]
type = listener
service = Read-Write-Service
protocol = MariaDBClient
port = 4006
#配置仅读池
[Read-Only-Service]
type=service
router=readconnroute
servers=server2
user=maxscale
password=E38A4DC32AF8692DB663BD5DABC88A254F4823875FE2F5E7D48784292F9965AC
router_options=slave
[Read-Only-Listener]
type=listener
service=Read-Only-Service
protocol=mariadbprotocol
port=4008重启服务
systemctl restart maxscale读写分离测试
查看数据库状态
maxctrl list servers┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server1 │ 192.168.1.101 │ 3306 │ 0 │ Master, Running │ 0-1-200 │ MariaDB-Monitor │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────┤
│ server2 │ 192.168.1.102 │ 3306 │ 0 │ Slave, Running │ 0-1-200 │ MariaDB-Monitor │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────┘查看读写池状态
maxctrl list services┌────────────────────┬────────────────┬─────────────┬───────────────────┬─────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├────────────────────┼────────────────┼─────────────┼───────────────────┼─────────┤
│ Read-Write-Service │ readwritesplit │ 0 │ 1 │ server1 │
├────────────────────┼────────────────┼─────────────┼───────────────────┼─────────┤
│ Read-Only-Service │ readconnroute │ 0 │ 1 │ server2 │
└────────────────────┴────────────────┴─────────────┴───────────────────┴─────────┘测试
#4006端口会自动路由sql语句到对应的服务器
mysql -u maxscale -p -h 127.0.0.1 -P 4006
#可以在从节点新建一个表，然后分别在4006端口和主节点对这个表进行查询和删除操作