配置maxscale中间件实现MySQL读写分离

读写分离概述

使用读写分离的原因

• 数据库写入效率要低于读取效率
• 一般来说，数据读取频率高于写入频率
• 单个数据库实例在写入的时候会影响读取性能

读写分离的实现

• 主服务器处理增删改请求
• 从服务器处理读请求
• 可由程序员通过代码实现
• 也可以通过中间件服务器实现，如mysql-proxy、mycat、maxscale

读写分离实施

读写分离拓扑图

maxscale服务器配置

• 安装maxscale：下载安装包 maxscale-2.1.2-1.rhel.7.x86_64.rpm

[root@mysql8 ~]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

• 修改配置文件

[root@mysql8 ~]# vim /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md

# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md

[maxscale]
threads=auto    # 线程数设置为auto，CPU有几个核心就产生几个线程

# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#

[server1]      # 定义要连接的mysql服务器
type=server
address=192.168.1.11
port=3306
protocol=MySQLBackend

[server2]      # 定义要连接的mysql服务器
type=server
address=192.168.1.12
port=3306
protocol=MySQLBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md

[MySQL Monitor]         # 定义要监视的数据库节点
type=monitor
module=mysqlmon
servers=server1,server2
user=maxscalemon
passwd=TEST2021@guodong.com
monitor_interval=10000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md

# [Read-Only Service]       # 注释只读服务
# type=service
# router=readconnroute
# servers=server1
# user=myuser
# passwd=mypwd
# router_options=slave

# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md

[Read-Write Service]          # 定义读写分离的数据库节点
type=service
router=readwritesplit
servers=server1,server2
user=maxscalerouter
passwd=TEST2021@guodong.com
max_slave_connections=100%

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md

[MaxAdmin Service]
type=service
router=cli

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

# [Read-Only Listener]          # 注释只读监听信息
# type=listener
# service=Read-Only Service
# protocol=MySQLClient
# port=4008

[Read-Write Listener]           # 定义读写分离服务配置
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]             # 定义管理服务配置
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016

• 在数据库服务器上创建maxscale需要的用户

[root@mysql1 ~]# mysql -uroot -pTEST2021@guodong.com
# 创建监控用户
mysql> grant replication slave,replication client on *.* to  maxscalemon@'%' identified by 'TEST2021@guodong.com';

# 创建路由用户
mysql> grant select on mysql.* to maxscalerouter@"%" identified by 'TEST2021@guodong.com';

# 辅助服务器上查看用户是否已同步
mysql> select user, host from mysql.user where user like 'maxscale%';
+----------------+------+
| user           | host |
+----------------+------+
| maxscalemon    | %    |
| maxscalerouter | %    |
+----------------+------+
2 rows in set (0.00 sec)

• 启动maxscale服务

[root@mysql8 ~]# systemctl start maxscale
# 如果无法启动，可查看/var/log/maxscale/maxscale.log日志

验证

• 查看监控信息。maxadmin默认用户为admin，密码是mariadb

[root@mysql8 ~]# maxadmin -uadmin -pmariadb -P4016
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.1.11    |  3306 |           0 | Master, Running
server2            | 192.168.1.12    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

• 在mysql主服务器上创建用于连接数据库的用户

mysql> grant all on test2021.* to guodong@'%' identified by 'TEST2021@guodong.com';

• 客户端192.168.1.10访问数据库测试

[root@rhel8 ~]# mysql -h192.168.1.18 -P4006 -uguodong -pTEST2021@guodong.com
# 查询。查询主机名，因为查询只会发到从服务器，所以得到的是从服务器主机名
mysql> select @@hostname;
+----------------+
| @@hostname     |
+----------------+
| mysql2 |
+----------------+
1 row in set (0.00 sec)

# 写入测试。因为写入的是主服务器，数据会同步到从服务器。在从服务器上可以查到新增内容
mysql> use test2021;
mysql> insert into departments(dept_name) values('hr1');
[root@mysql2 ~]# mysql -uroot -pTEST2021@guodong.com
mysql> use test2021;
mysql> select * from departments where dept_name='hr1';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      13 | hr1       |
+---------+-----------+
1 row in set (0.00 sec)