​Mysql数据读写分离（多实例配置）

目录

一.读写分离概述

二. 读写分离实施maxscale

1.环境准备

2.maxscale服务器配置

---

一.读写分离概述

使用读写分离的原因

• 数据库写入效率要低于读取效率

• 一般来说，数据读取频率高于写入频率

• 单个数据库实例在写入的时候会影响读取性能

读写分离的实现

• 主服务器处理增删改请求

• 从服务器处理读请求

• 可由程序员通过代码实现

• 也可以通过中间件服务器实现，如mysql-proxy、mycat、maxscale

二. 读写分离实施maxscale

读写分离实施

        增删改查

        增删改

        查

        同步数据

1.环境准备

client:192.168.1.10

maxscale:192.168.1.18

master:192.168.1.11

slave:192.168.1.12

2.maxscale服务器配置

• 不需要在maxscale1上安装mysqld服务。如果已经安装，则停止它。

[root@maxscale1 ~]# systemctl stop mysqld
[root@maxscale1 ~]# systemctl disable mysqld

• 准备yum源

[root@zzgrhel8 ~]# wget https://downloads.mariadb.com/MaxScale/2.0.2/centos/5Server/x86_64/maxscale-2.0.2.centos.5.tar.gz
[root@zzgrhel8 ~]# tar -zxf maxscale-2.0.2.centos.5.tar.gz
[root@zzgrhel8 ~]# cp /linux-soft/4/mysql/maxscale-2.1.2-1.rhel.7.x86_64.rpm /var/www/html/mysql/
[root@zzgrhel8 ~]# cd /var/www/mysql/
[root@zzgrhel8 mysql]# createrepo -v .

• 安装maxscale

[root@maxscale1 ~]# yum clean all
[root@maxscale1 ~]# yum install -y maxscale

• 修改配置文件

[root@maxscale1 ~]# 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=NSD2021@tedu.cn
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=NSD2021@tedu.cn
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

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

[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
# 创建监控用户
mysql> grant replication slave,replication client on *.* to  maxscalemon@'%' identified by 'NSD2021@tedu.cn';
​
# 创建路由用户
mysql> grant select on mysql.* to maxscalerouter@"%" identified by 'NSD2021@tedu.cn';
​
# 辅助服务器上查看用户是否已同步
mysql> select user, host from mysql.user where user like 'maxscale%';
+----------------+------+
| user           | host |
+----------------+------+
| maxscalemon    | %    |
| maxscalerouter | %    |
+----------------+------+
2 rows in set (0.00 sec)

• 启动maxscale服务

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

验证

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

[root@maxscale1 ~]# 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 nsd2021.* to zzg@'%' identified by 'NSD2021@tedu.cn';

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

[root@zzgrhel8 ~]# mysql -h192.168.1.18 -P4006 -uzzg -pNSD2021@tedu.cn
# 查询。查询主机名，因为查询只会发到从服务器，所以得到的是从服务器主机名
mysql> select @@hostname;
+----------------+
| @@hostname     |
+----------------+
| mysql2.tedu.cn |
+----------------+
1 row in set (0.00 sec)
​
# 写入测试。因为写入的是主服务器，数据会同步到从服务器。在从服务器上可以查到新增内容
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('hr1');
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments where dept_name='hr1';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      13 | hr1       |
+---------+-----------+
1 row in set (0.00 sec)