依赖包

yum install -y novacom-server.x86_64 libaio.x86_64 libaio-devel.x86_64

安装

[root@localhost softpackage]# rpm -ivh maxscale-2.4.7-1.centos.7.x86_64.rpm
警告：maxscale-2.4.7-1.centos.7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID 28c12247: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:maxscale-2.4.7-1                 ################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.

环境介绍

使用

创建权限用户

-- 创建监控账号
create user qvbilam_scale_monitor@'192.168.128.%' identified by 'qvbilam';
-- 授权用于监控服务账号权限
grant replication slave,replication client on *.* to qvbilam_scale_monitor@'192.168.128.%';
-- 创建路由账号
create user qvbilam_scale_route@'192.168.128.%' identified by 'qvbilam';
-- 授予路由账号权限
grant select on mysql.* to qvbilam_scale_route@'192.168.128.%';

修改配置

# cp /etc/maxscale.cnf /etc/maxscale.cnf.back
vi /etc/maxscale.cnf
# --------------- 内容如下 ---------------

# 进程数,
[maxscale]
threads=auto

# 数据库地址
[server1]
type=server
address=192.168.128.125
port=3306
protocol=MariaDBBackend

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

[server3]
type=server
address=192.168.128.127
port=3306
protocol=MariaDBBackend

[server4]
type=server
address=192.168.128.128
port=3306
protocol=MariaDBBackend

# 监控模块
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3,server4 # 需要监控的服务器
user=qvbilam_scale_monitor # Mysql监控用户
password=qvbilam # Mysql监控密码
monitor_interval=2000    # 监控间隔时间/毫秒

# 只读模块,不需要注释掉
# [Read-Only-Service]
# type=service
# router=readconnroute
# servers=server1
# user=myuser
# password=mypwd
# router_options=slave

# 读写分离模块
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3,server4 # 服务列表
user=qvbilam_scale_route # mysql创建的路由账号
password=qvbilam # mysql路由账号的密码
max_slave_replication_lag=60 # 从服务器延迟超过60秒后不参与读写分离的集群中

# 只读服务监听,不需要注释掉
# [Read-Only-Listener]
# type=listener
# service=Read-Only-Service
# protocol=MariaDBClient
# port=4008

# 读写分离监听
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=3006 # 默认4006,为了方便相当于使用mysql.

# maxadmin 服务
[MaxAdmin-Service]
type=service
router=cli

[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
port=6603

启动

# 惊奇的发现会自动生成maxscale 用户
[root@localhost ~]# cat /etc/passwd | grep maxscale
maxscale:x:995:994::/home/maxscale:/bin/false
# 启动报错
[root@localhost ~]# maxscale
Error: MaxScale cannot be run as root.
Failed to write child process message!
# 不让用root启动,用自动创建的用户启动测试
[root@localhost ~]# maxscale --u=maxscale
Failed to open file '/var/log/maxscale/maxscale.log': 13, Permission denied
# 给log文件权限后再启动
[root@localhost ~]# chmod 777 maxscale.log
[root@localhost maxscale]# maxscale --u=maxscale -f /etc/maxscale.cnf
# 通过maxadmin查看集群状态 默认账号admin,默认密码mariadb
[root@localhost ~]# maxadmin --user=admin --password=mariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.128.125 |  3306 |           0 | Slave, Running
server2            | 192.168.128.126 |  3306 |           0 | Master, Running
server3            | 192.168.128.127 |  3306 |           0 | Slave, Running
server4            | 192.168.128.128 |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

测试

连接MaxScale

# 连接监控服务器的maxscale服务 
# -u: maxscale配置中[Read-Write-Service]模块下的user,即mysql中创建的路由用户
# -h: 监控服务器ip(没有启动mysql)
# -P: maxscale配置中[Read-Write-Listener]模块下的port,默认4406
mysql -uqvbilam_scale_route -pqvbilam -h192.168.128.129 -P3306
# 成功进入mysql界面
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 17
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 [(none)]>

读写分离

  通过下面演示可以看到当执行读操作的时候,分配的主机名是qvbilam_mha_1(从库125),当执行事务也就是写操作的时候,分配的主机名为qvbilam_mha_2(主库126).

# 测试读
MySQL [(none)]> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| qvbilam_mha_1 |
+---------------+
1 row in set (0.01 sec)

# 测试写
MySQL [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| qvbilam_mha_2 |
+---------------+
1 row in set (0.00 sec)

MySQL [(none)]> rollback;
Query OK, 0 rows affected (0.00 sec)