数据读写分离
读写分离概述
使用读写分离的原因
- 数据库写入效率要低于读取效率
- 一般来说，数据读取频率高于写入频率
- 单个数据库实例在写入的时候会影响读取性能
读写分离的实现
- 主服务器处理增删改请求
- 从服务器处理读请求
- 可由程序员通过代码实现
- 也可以通过中间件服务器实现，如mysql-proxy、mycat、maxscale
读写分离实施
graph TD
c(client:192.168.1.10)--增删改查-->max(maxscale:192.168.1.18)
max--增删改-->master(master:192.168.1.11)
max--查-->slave(slave:192.168.1.12)
master--同步数据-->slave
maxscale服务器配置
- 不需要在maxscale1上安装mysqld服务。如果已经安装，则停止它。
[root@maxscale1 ~]# systemctl stop mysqld
[root@maxscale1 ~]# systemctl disable mysqld
[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 .
[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
[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)
[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> grant all on nsd2021.* to zzg@'%' identified by 'NSD2021@tedu.cn';
[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)
多实例配置
概述
- 在一台物理主机上运行多个数据库服务
- 节约运维成本
- 提高硬件利用率
- 多实例配置，需要专用的、支持多实例的Mysql软件。本课程中对应的软件包是
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
配置
- 配置192.168.1.11为多实例数据库服务器，如果已安装mysql，则停止其服务，或将其卸载
# 卸载方法
[root@mysql1 ~]# yum list installed | grep mysql
[root@mysql1 ~]# yum remove -y mysql-community-server
- 不能存在/etc/my.cnf文件，如果有此文件则移走它
- 直接解压多实例Mysql软件包
[root@mysql1 ~]# tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql1 ~]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
[mysqld1]
datadir = /mysqlmul/mysqld1
port = 3306
log-error = /mysqlmul/mysqld1/mysqld1.err
pid-file = /mysqlmul/mysqld1/mysqld1.pid
socket = /mysqlmul/mysqld1/mysqld1.sock
[mysqld2]
datadir = /mysqlmul/mysqld2
port = 3307
log-error = /mysqlmul/mysqld2/mysqld2.err
pid-file = /mysqlmul/mysqld2/mysqld2.pid
socket = /mysqlmul/mysqld2/mysqld2.sock
[root@mysql1 ~]# mkdir -p /mysqlmul/mysqld{1,2}
启动服务
[root@mysql1 ~]# /usr/local/mysql/bin/mysqld_multi start 1
// 静候启动完成，记录最后一行产生的密码
2021-04-10T03:17:28.637290Z 1 [Note] A temporary password is generated for root@localhost: ,N1j!G1(M/L.
// 通过本机socket连接数据库并修改密码
[root@mysql1 ~]# mysql -uroot -p',N1j!G1(M/L.' -S /mysqlmul/mysqld1/mysqld1.sock
mysql> alter user root@'localhost' identified by 'NSD2021@tedu.cn';
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
[root@mysql1 ~]# /usr/local/mysql/bin/mysqld_multi start 2
# 静候启动完成，记录最后一行产生的密码
2021-04-10T03:28:42.084264Z 1 [Note] A temporary password is generated for root@localhost: &c/ag7j()41A
# 通过socket连接并修改密码
[root@mysql1 ~]# mysqladmin -uroot -p'&c/ag7j()41A' -S /mysqlmul/mysqld2/mysqld2.sock password 'NSD2021@tedu.cn'
# 通过网络连接
[root@mysql1 ~]# mysql -h127.0.0.1 -uroot -pNSD2021@tedu.cn -P3307
停止服务
[root@mysql1 ~]# /usr/local/mysql/bin/mysqld_multi --user root --password NSD2021@tedu.cn stop 1
加入mysql命令路径到PATH变量
# 临时增加
[root@mysql1 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mysql1 ~]# export PATH=$PATH:/usr/local/mysql/bin
[root@mysql1 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
# 永久修改PATH环境变量
[root@mysql1 ~]# vim .bash_profile # 在下方添加
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
export PATH
[root@mysql1 ~]# source .bash_profile
# 停止实例2
[root@mysql1 ~]# mysqld_multi --user root --password NSD2021@tedu.cn stop 2
网友评论