maxscale读写分离
操作手册（www.mariadb.com）
配置maxscale
1.先配置好主从，过程省略
实验主机：
master 192.168.116.131
slave 192.168.116.132
maxscale 192.168.116.145
2.下载maxscale源
[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...
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
23 files removed
[root@maxscale ~]# yum clean all
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
0 files removed
[root@maxscale ~]# yum makecache
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
CentOS-8.5.2111 - Base - mirrors.aliyun.com 5.3 MB/s | 4.6 MB 00:00
CentOS-8.5.2111 - Extras - mirrors.aliyun.com 32 kB/s | 10 kB 00:00
CentOS-8.5.2111 - AppStream - mirrors.aliyun.com 7.5 MB/s | 8.4 MB 00:01
CentOS Configmanagement SIG - ansible-29 335 kB/s | 2.1 MB 00:06
MariaDB Server 85 kB/s | 634 kB 00:07
MariaDB MaxScale 1.1 kB/s | 6.8 kB 00:05
MariaDB Tools 4.5 kB/s | 25 kB 00:05
Metadata cache created.
3.安装maxscale
[root@maxscale ~]# yum -y install maxscale
Complete!
4.在主数据库为MaxScale创建用户帐户
mysql> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON mysql.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)
5.创建客户端用户帐户让其能够访问maxscale主机(在主数据库中创建)
mysql> CREATE USER 'tom'@'192.168.116.145' IDENTIFIED BY 'Passw0rd@_~';
Query OK, 0 rows affected (0.01 sec)
6.maxscale主机中用登入tom登入测试
[root@maxscale ~]# mysql -utom -pPassw0rd@_~ -h'192.168.116.131'
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 18
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.
7.创建客户端用户帐户(在主数据库中创建)
mysql> GRANT all ON *.* TO 'tom'@'192.168.116.145';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER 'tom'@'192.168.116.1' IDENTIFIED BY 'Passw0rd@_~';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT all ON *.* TO 'tom'@'192.168.116.1';
Query OK, 0 rows affected (0.00 sec)
8.查看tom权限（主数据库）
mysql> SHOW GRANTS FOR 'tom'@'192.168.116.145';
+--------------------------------------------------------------------------+
| Grants for tom@192.168.116.145 |
+--------------------------------------------------------------------------+
| GRANT all ON *.* TO `tom`@`192.168.116.145` |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'tom'@'192.168.116.1';
+------------------------------------------------------------------------+
| Grants for tom@192.168.116.1 |
+------------------------------------------------------------------------+
| GRANT all *.* TO `tom`@`192.168.116.1` |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
9.配置maxscale配置文件(maxscale主机)
[maxscale]
threads=auto
//配置服务器
[server1]
type=server
address=192.168.116.131
port=3306
protocol=MySQLBackend
[server2]
type=server
address=192.168.116.132
port=3306
protocol=MySQLBackend
//配置监视器
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=monitor
password=monitor
monitor_interval=2s
//配置服务和侦听器
#[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
10.创建监视需要的账号并授权（主数据库中创建）
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION CLIENT on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 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.00 sec)
11.设置maxscale服务开机自启（maxscale主机）
[root@maxscale ~]# systemctl enable --now maxscale
[root@maxscale ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 127.0.0.1:8989 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 128 *:3306 *:*
12.使用 MaxCtrl 检查 MaxScale 状态
[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.116.131 │ 3306 │ 0 │ Master, Running │ │ MariaDB-Monitor │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼─────────────────┤
│ server2 │ 192.168.116.132 │ 3306 │ 0 │ Slave, Running │ │ MariaDB-Monitor │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┴──────┴─────────────────┘
[root@maxscale ~]# maxctrl list listeners
┌─────────────────────┬──────┬──────┬─────────┬────────────────────┐
│ Name │ Port │ Host │ State │ Service │
├─────────────────────┼──────┼──────┼─────────┼────────────────────┤
│ Read-Write-Listener │ 3306 │ :: │ Running │ Read-Write-Service │
└─────────────────────┴──────┴──────┴─────────┴────────────────────┘
[root@maxscale ~]# maxctrl list sessions //记录客户端访问过几次，有多少个客户端在访问
┌────┬──────┬───────────────┬───────────────────────┬───────┬────────────────────┬────────┬──────────────┐
│ Id │ User │ Host │ Connected │ Idle │ Service │ Memory │ I/O-Activity │
13.主数据库要进入到数据库里面，授权之后不要退出来，不然有可能连接不上
[root@master ~]# /usr/local/mysql/bin/mysql -uroot -pPassw0rd@_~
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 51
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>
使用workbench连接MaxScale主机上的tom账号测试
//在后台可以查看具体的操作，是读还是写，写是master，读是slave
[root@maxscale ~]# maxctrl list sessions //查看客户端连接了几次，有哪些客户端连接
┌────┬──────┬───────────────┬───────────────────────┬──────┬────────────────────┬────────┬──────────────┐
│ Id │ User │ Host │ Connected │ Idle │ Service │ Memory │ I/O-Activity │
├────┼──────┼───────────────┼───────────────────────┼──────┼────────────────────┼────────┼──────────────┤
│ 3 │ tom │ 192.168.116.1 │ 3/28/2024, 9:27:56 PM │ 5.3 │ Read-Write-Service │ 69046 │ 70 │
├────┼──────┼───────────────┼───────────────────────┼──────┼────────────────────┼────────┼──────────────┤
│ 2 │ tom │ 192.168.116.1 │ 3/28/2024, 9:27:56 PM │ 5.4 │ Read-Write-Service │ 68740 │ 45 │
└────┴──────┴───────────────┴───────────────────────┴──────┴────────────────────┴────────┴──────────────┘
[root@maxscale ~]# maxctrl show server server1
┌─────────────────────┬──────────────────────────────────────────────┐
│ Server │ server1 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Source │ /etc/maxscale.cnf │
├─────────────────────┼──────────────────────────────────────────────┤
│ Address │ 192.168.116.131 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Port │ 3306 │
├─────────────────────┼──────────────────────────────────────────────┤
│ State │ Master, Running │
├─────────────────────┼──────────────────────────────────────────────┤
│ Version │ 8.0.35 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Uptime │ 6356 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Last Event │ server_down │
├─────────────────────┼──────────────────────────────────────────────┤
│ Triggered At │ Thu, 28 Mar 2024 12:36:45 GMT │
├─────────────────────┼──────────────────────────────────────────────┤
│ Services │ Read-Write-Service │
├─────────────────────┼──────────────────────────────────────────────┤
│ Monitors │ MariaDB-Monitor │
├─────────────────────┼──────────────────────────────────────────────┤
│ Master ID │ -1 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Node ID │ 10 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Slave Server IDs │ │
├─────────────────────┼──────────────────────────────────────────────┤
│ Current Connections │ 2 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Total Connections │ 3 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Max Connections │ 2 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Statistics │ { │
│ │ "active_operations": 0, │
│ │ "adaptive_avg_select_time": "0ns", │
│ │ "connection_pool_empty": 0, │
│ │ "connections": 2, │
│ │ "failed_auths": 0, │
│ │ "max_connections": 2, │
│ │ "max_pool_size": 0, │
│ │ "persistent_connections": 0, │
│ │ "response_time_distribution": { │
│ │ "read": { │
│ │ "distribution": [ │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000001", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000010", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000100", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 50, │
│ │ "time": "0.001000", │
│ │ "total": 0.026857559 │
│ │ }, │
│ │ { │
│ │ "count": 12, │
│ │ "time": "0.010000", │
│ │ "total": 0.01585836 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.100000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100000.000000", │
│ │ "total": 0 │
│ │ } │
│ │ ], │
│ │ "operation": "read", │
│ │ "range_base": 10 │
│ │ }, │
│ │ "write": { │
│ │ "distribution": [ │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000001", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000010", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000100", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 16, │
│ │ "time": "0.001000", │
│ │ "total": 0.007707844 │
│ │ }, │
│ │ { │
│ │ "count": 8, │
│ │ "time": "0.010000", │
│ │ "total": 0.017451464 │
│ │ }, │
│ │ { │
│ │ "count": 3, │
│ │ "time": "0.100000", │
│ │ "total": 0.074012241 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100000.000000", │
│ │ "total": 0 │
│ │ } │
│ │ ], │
│ │ "operation": "write", │
│ │ "range_base": 10 │
│ │ } │
│ │ }, │
│ │ "reused_connections": 0, │
│ │ "routed_packets": 90, │
│ │ "total_connections": 3 │
│ │ } │
├─────────────────────┼──────────────────────────────────────────────┤
│ Parameters │ { │
│ │ "address": "192.168.116.131", │
│ │ "disk_space_threshold": null, │
│ │ "extra_port": 0, │
│ │ "max_routing_connections": 0, │
│ │ "monitorpw": null, │
│ │ "monitoruser": null, │
│ │ "persistmaxtime": "0ms", │
│ │ "persistpoolmax": 0, │
│ │ "port": 3306, │
│ │ "priority": 0, │
│ │ "proxy_protocol": false, │
│ │ "rank": "primary", │
│ │ "replication_custom_options": null, │
│ │ "socket": null, │
│ │ "ssl": false, │
│ │ "ssl_ca": null, │
│ │ "ssl_cert": null, │
│ │ "ssl_cert_verify_depth": 9, │
│ │ "ssl_cipher": null, │
│ │ "ssl_key": null, │
│ │ "ssl_verify_peer_certificate": false, │
│ │ "ssl_verify_peer_host": false, │
│ │ "ssl_version": "MAX" │
│ │ } │
└─────────────────────┴──────────────────────────────────────────────┘
[root@maxscale ~]# maxctrl show server server2
┌─────────────────────┬──────────────────────────────────────────────┐
│ Server │ server2 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Source │ /etc/maxscale.cnf │
├─────────────────────┼──────────────────────────────────────────────┤
│ Address │ 192.168.116.132 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Port │ 3306 │
├─────────────────────┼──────────────────────────────────────────────┤
│ State │ Slave, Running │
├─────────────────────┼──────────────────────────────────────────────┤
│ Version │ 8.0.35 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Uptime │ 6359 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Last Event │ server_down │
├─────────────────────┼──────────────────────────────────────────────┤
│ Triggered At │ Thu, 28 Mar 2024 12:36:45 GMT │
├─────────────────────┼──────────────────────────────────────────────┤
│ Services │ Read-Write-Service │
├─────────────────────┼──────────────────────────────────────────────┤
│ Monitors │ MariaDB-Monitor │
├─────────────────────┼──────────────────────────────────────────────┤
│ Master ID │ 10 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Node ID │ 20 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Slave Server IDs │ │
├─────────────────────┼──────────────────────────────────────────────┤
│ Current Connections │ 2 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Total Connections │ 3 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Max Connections │ 2 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Statistics │ { │
│ │ "active_operations": 0, │
│ │ "adaptive_avg_select_time": "0ns", │
│ │ "connection_pool_empty": 0, │
│ │ "connections": 2, │
│ │ "failed_auths": 0, │
│ │ "max_connections": 2, │
│ │ "max_pool_size": 0, │
│ │ "persistent_connections": 0, │
│ │ "response_time_distribution": { │
│ │ "read": { │
│ │ "distribution": [ │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000001", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000010", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000100", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 100, │
│ │ "time": "0.001000", │
│ │ "total": 0.05487916 │
│ │ }, │
│ │ { │
│ │ "count": 48, │
│ │ "time": "0.010000", │
│ │ "total": 0.08707095 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.100000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100000.000000", │
│ │ "total": 0 │
│ │ } │
│ │ ], │
│ │ "operation": "read", │
│ │ "range_base": 10 │
│ │ }, │
│ │ "write": { │
│ │ "distribution": [ │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000001", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000010", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.000100", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.001000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.010000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "0.100000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "1000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "10000.000000", │
│ │ "total": 0 │
│ │ }, │
│ │ { │
│ │ "count": 0, │
│ │ "time": "100000.000000", │
│ │ "total": 0 │
│ │ } │
│ │ ], │
│ │ "operation": "write", │
│ │ "range_base": 10 │
│ │ } │
│ │ }, │
│ │ "reused_connections": 0, │
│ │ "routed_packets": 147, │
│ │ "total_connections": 3 │
│ │ } │
├─────────────────────┼──────────────────────────────────────────────┤
│ Parameters │ { │
│ │ "address": "192.168.116.132", │
│ │ "disk_space_threshold": null, │
│ │ "extra_port": 0, │
│ │ "max_routing_connections": 0, │
│ │ "monitorpw": null, │
│ │ "monitoruser": null, │
│ │ "persistmaxtime": "0ms", │
│ │ "persistpoolmax": 0, │
│ │ "port": 3306, │
│ │ "priority": 0, │
│ │ "proxy_protocol": false, │
│ │ "rank": "primary", │
│ │ "replication_custom_options": null, │
│ │ "socket": null, │
│ │ "ssl": false, │
│ │ "ssl_ca": null, │
│ │ "ssl_cert": null, │
│ │ "ssl_cert_verify_depth": 9, │
│ │ "ssl_cipher": null, │
│ │ "ssl_key": null, │
│ │ "ssl_verify_peer_certificate": false, │
│ │ "ssl_verify_peer_host": false, │
│ │ "ssl_version": "MAX" │
│ │ } │
└─────────────────────┴──────────────────────────────────────────────┘
1145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
