在此前的两个版本中，不同于数据库集群大小和数据大小做了不同的线上测试，有一系列问题，最终被下架更换到haproxy。在新的环境中使用了2.4，因为2.4有很多变动。仍然保持热情的进行体验。
Galera cluster集群模式中节点都可以作为读写，但是通常而言，会在一台节点进行写入，其他节点读取。因此选择读写分离模块readwritesplit路由模式。
readwritesplit
读写分离readwritesplit旨在提高群集的只读处理能力，同时保持一致性。不修改数据的数据分散在多个节点上，而所有写查询都将发送到单个节点上。
那么，除了readwritesplit，maxscale还提供SchemaRouter和SmartRouter,有兴趣可以查看。
此外，maxscale提供了新的Clustrix作为监视工具MaxCtrl
安装
建议下载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配置
我现在有两台数据库是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数据库授权
授权给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.监控模块
[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 ~]#
用户授权
数据库授权后，每一个登陆的授权用户同时仍然需要授权给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)
