读写分离概述
使用读写分离的原因
- 数据库写入效率要低于读取效率
- 一般来说，数据读取频率高于写入频率
- 单个数据库实例在写入的时候会影响读取性能
读写分离的实现
- 主服务器处理增删改请求
- 从服务器处理读请求
- 可由程序员通过代码实现
- 也可以通过中间件服务器实现，如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)
