linuxea: 基于Galera cluster的MaxScale 2.4读写分离笔记


在此前的两个版本中,不同于数据库集群大小和数据大小做了不同的线上测试,有一系列问题,最终被下架更换到haproxy。在新的环境中使用了2.4,因为2.4有很多变动。仍然保持热情的进行体验。

Galera cluster集群模式中节点都可以作为读写,但是通常而言,会在一台节点进行写入,其他节点读取。因此选择读写分离模块readwritesplit路由模式。

I. readwritesplit

读写分离readwritesplit旨在提高群集的只读处理能力,同时保持一致性。不修改数据的数据分散在多个节点上,而所有写查询都将发送到单个节点上。

那么,除了readwritesplit,maxscale还提供SchemaRouterSmartRouter,有兴趣可以查看。

此外,maxscale提供了新的Clustrix作为监视工具MaxCtrl

II. 安装

建议下载rpm包,查看相应的版本下载即可。假如打不开,推荐到github下载压缩包进行安装即可

下载完成在本地安装即可

yum localinstall maxscale-2.4.7-1.centos.7.x86_64.rpm -y

他的配置文件默认在/etc下

[root@linuxea_Node ~]# rpm -ql maxscale |grep etc
/etc/maxscale.cnf.template
/etc/prelink.conf.d
/etc/prelink.conf.d/maxscale.conf

III. 配置

我现在有两台数据库是Galera cluster集群,现在我们配置成maxscale读写分离
配置文件分几个部分,分别是全局,节点,service,监控模块和listener。相比之前的版本少了监控的端口配置。
其他配置参考官网配置

  • 全局配置

全局配置中配置一些必要的配置信息

  • 节点配置

除了必要的信息之外,可以添加priority设置优先级,如果为0,永远不会成为主节点

用户请求从4006进入,将请求发给RW-Service,RW-Service根据读写分离策略将请求发给后端,Galera-Monitor作为管理和调度

如下:

# 全局配置
[maxscale]
threads=auto
ms_timestamp=1
syslog=0
maxlog=1
log_warning=1
log_notice=0
log_info=0
log_augmentation=1

# service配置
[RW-Service]
type=service
router=readwritesplit
user=maxscale
password=F3BF776B14BE7CAB61E83256E1C2B56C
cluster=Galera-Monitor


#listener
[RW-Listener]
type=listener
service=RW-Service
protocol=MariaDBClient
address=0.0.0.0
port=4006

# 节点配置
[server1]
type=server
address=172.25.109.5
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=172.25.109.4
port=3306
protocol=MariaDBBackend


# 监控模块
[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2
user=maxscale
# maxkeys /var/lib/maxscale/
# maxpasswd /var/lib/maxscale KLJMKMXOPQOP1  KLJMKMXOPQOP1是授权的密码
password=F3BF776B14BE7CAB61E83256E1C2B56C
monitor_interval=10000
disable_master_failback=true
use_priority=true

IV. 数据库授权

授权给maxscale

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';

完成授权后,修改maxscale的配置文件password字段

[root@linuxea_node_maxscale-1 ~]# maxkeys /var/lib/maxscale/
[root@linuxea_node_maxscale-1 ~]# maxpasswd /var/lib/maxscale password
70DB8B57832941E87AB14C5BC47C6292
[root@linuxea_node_maxscale-1 ~]# sed -i 's/password=.*/password=70DB8B57832941E87AB14C5BC47C6292/g' /etc/maxscale.cnf

而后启动

[root@linuxea_node_maxscale-1 ~]# systemctl restart maxscale
[root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log 


MariaDB MaxScale  /var/log/maxscale/maxscale.log  Sat Mar 21 17:03:08 2020
----------------------------------------------------------------------------
2020-03-21 17:03:08   notice : (mxb_log_set_syslog_enabled): syslog logging is disabled.
2020-03-21 17:03:08   notice : (mxb_log_set_maxlog_enabled): maxlog logging is enabled.
2020-03-21 17:03:08.752   notice : (mxb_log_set_highprecision_enabled): highprecision logging is enabled.
2020-03-21 17:03:08.752   notice : (mxb_log_set_priority_enabled): The logging of warning messages has been enabled.

V. 监控模块

[root@linuxea_node_maxscale-1 ~]# maxctrl list services
┌────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service    │ Router         │ Connections │ Total Connections │ Servers          │
├────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ RW-Service │ readwritesplit │ 0           │ 0                 │ server1, server2 │
└────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
[root@linuxea_node_maxscale-1 ~]#  maxctrl list servers
┌─────────┬──────────────┬──────┬─────────────┬─────────────────────────┬───────┐
│ Server  │ Address      │ Port │ Connections │ State                   │ GTID  │
├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤
│ server1 │ 172.25.109.5 │ 3306 │ 0           │ Slave, Synced, Running  │       │
├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤
│ server2 │ 172.25.109.4 │ 3306 │ 0           │ Master, Synced, Running │ 0-1-6 │
└─────────┴──────────────┴──────┴─────────────┴─────────────────────────┴───────┘
[root@linuxea_node_maxscale-1 ~]#  maxctrl list listeners RW-Service
┌──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┐
│ Name                                             │ Port                                             │ Host                                             │ State                                            │
├──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┤
│ {                                                │ {                                                │ {                                                │ {                                                │
│     "id": "RW-Listener",                         │     "id": "RW-Listener",                         │     "id": "RW-Listener",                         │     "id": "RW-Listener",                         │
│     "type": "listeners",                         │     "type": "listeners",                         │     "type": "listeners",                         │     "type": "listeners",                         │
│     "attributes": {                              │     "attributes": {                              │     "attributes": {                              │     "attributes": {                              │
│         "state": "Running",                      │         "state": "Running",                      │         "state": "Running",                      │         "state": "Running",                      │
│         "parameters": {                          │         "parameters": {                          │         "parameters": {                          │         "parameters": {                          │
│             "protocol": "MariaDBClient",         │             "protocol": "MariaDBClient",         │             "protocol": "MariaDBClient",         │             "protocol": "MariaDBClient",         │
│             "port": 4006,                        │             "port": 4006,                        │             "port": 4006,                        │             "port": 4006,                        │
│             "socket": null,                      │             "socket": null,                      │             "socket": null,                      │             "socket": null,                      │
│             "authenticator_options": "",         │             "authenticator_options": "",         │             "authenticator_options": "",         │             "authenticator_options": "",         │
│             "address": "0.0.0.0",                │             "address": "0.0.0.0",                │             "address": "0.0.0.0",                │             "address": "0.0.0.0",                │
│             "authenticator": null,               │             "authenticator": null,               │             "authenticator": null,               │             "authenticator": null,               │
│             "ssl": "false",                      │             "ssl": "false",                      │             "ssl": "false",                      │             "ssl": "false",                      │
│             "ssl_cert": null,                    │             "ssl_cert": null,                    │             "ssl_cert": null,                    │             "ssl_cert": null,                    │
│             "ssl_key": null,                     │             "ssl_key": null,                     │             "ssl_key": null,                     │             "ssl_key": null,                     │
│             "ssl_ca_cert": null,                 │             "ssl_ca_cert": null,                 │             "ssl_ca_cert": null,                 │             "ssl_ca_cert": null,                 │
│             "ssl_version": "MAX",                │             "ssl_version": "MAX",                │             "ssl_version": "MAX",                │             "ssl_version": "MAX",                │
│             "ssl_cert_verify_depth": 9,          │             "ssl_cert_verify_depth": 9,          │             "ssl_cert_verify_depth": 9,          │             "ssl_cert_verify_depth": 9,          │
│             "ssl_verify_peer_certificate": false │             "ssl_verify_peer_certificate": false │             "ssl_verify_peer_certificate": false │             "ssl_verify_peer_certificate": false │
│         },                                       │         },                                       │         },                                       │         },                                       │
│         "authenticator_diagnostics": [           │         "authenticator_diagnostics": [           │         "authenticator_diagnostics": [           │         "authenticator_diagnostics": [           │
│             {                                    │             {                                    │             {                                    │             {                                    │
│                 "user": "maxscale",              │                 "user": "maxscale",              │                 "user": "maxscale",              │                 "user": "maxscale",              │
│                 "host": "%"                      │                 "host": "%"                      │                 "host": "%"                      │                 "host": "%"                      │
│             },                                   │             },                                   │             },                                   │             },                                   │
│             {                                    │             {                                    │             {                                    │             {                                    │
│                 "user": "maxscale",              │                 "user": "maxscale",              │                 "user": "maxscale",              │                 "user": "maxscale",              │
│                 "host": "%"                      │                 "host": "%"                      │                 "host": "%"                      │                 "host": "%"                      │
│             }                                    │             }                                    │             }                                    │             }                                    │
│         ]                                        │         ]                                        │         ]                                        │         ]                                        │
│     }                                            │     }                                            │     }                                            │     }                                            │
│ }                                                │ }                                                │ }                                                │ }                                                │
└──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┘
[root@linuxea_node_maxscale-1 ~]# 

VI. 用户授权

数据库授权后,每一个登陆的授权用户同时仍然需要授权给maxscale。
比如:maxscale的ip是172.25.109.8。
假设现在授权一个用户sean用户从172.25.111.6来进行访问。使用maxscale,还需要将sean授权给maxscale的ip,权限及密码。

CREATE DATABASE linuxea_com CHARACTER SET utf8mb4;
GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password';
MariaDB [(none)]> CREATE DATABASE linuxea_com CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

假如不授权,是可以登陆,但是会提示没有权限

[root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log 
2020-03-21 17:10:04.865   error  : (129) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES)
2020-03-21 17:10:09.961   error  : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server1'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES)
2020-03-21 17:10:09.961   error  : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES)

如下:

[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 124
Server version: 10.0.33-MariaDB-wsrep

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    129
Current database: *** NONE ***

ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.
MariaDB [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    132
Current database: *** NONE ***

ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.

我们在数据库进行授权maxscale的ip

MariaDB [(none)]>  GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.109.8 IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

回到172.25.111.6登陆

[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 124
Server version: 10.0.33-MariaDB-wsrep

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    129
Current database: *** NONE ***

ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.
MariaDB [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    132
Current database: *** NONE ***

ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.
MariaDB [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    174
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxea_com        |
+--------------------+
2 rows in set (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxea_com        |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 

创建

MariaDB [(none)]> use linuxea_com
Database changed
MariaDB [linuxea_com]> CREATE TABLE MyGuests (
    -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> firstname VARCHAR(30) NOT NULL,
    -> lastname VARCHAR(30) NOT NULL,
    -> email VARCHAR(50),
    -> reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> )
    -> ;
Query OK, 0 rows affected (0.02 sec)

MariaDB [linuxea_com]> show tables;
+-----------------------+
| Tables_in_linuxea_com |
+-----------------------+
| MyGuests              |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [linuxea_com]> desc MyGuests;
+-----------+-----------------+------+-----+-------------------+-----------------------------+
| Field     | Type            | Null | Key | Default           | Extra                       |
+-----------+-----------------+------+-----+-------------------+-----------------------------+
| id        | int(6) unsigned | NO   | PRI | NULL              | auto_increment              |
| firstname | varchar(30)     | NO   |     | NULL              |                             |
| lastname  | varchar(30)     | NO   |     | NULL              |                             |
| email     | varchar(50)     | YES  |     | NULL              |                             |
| reg_date  | timestamp       | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-----------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
0 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2020-03-24分类: Mariadb

标签: mariadb, maxscale

发表评论