8 - MySQL数据读写分离｜MySQL多实例

数据库系列文章

1 - 数据库服务概述 | 构建MySQL服务 | 数据库基本管理 | MySQL基本类型
2 - 表结构 | MySQL键值
3 - 字段约束｜MySQL索引｜MySQL用户管理
4 - 基础查询进阶｜连接查询
5 - 视图｜存储过程
6 - 数据备份与恢复｜innobackupex
7 - MySQL主从同步｜主从同步模式
8 - MySQL数据读写分离｜MySQL多实例
9 - 数据分片概述｜部署MyCat服务
10 - MHA集群概述｜部署MHA集群
11 - PXC集群｜MySQL存储引擎

MySQL数据读写分离

前置条件：配置192.168.4.56 不需要安装mysql服务 确保一主一从（51，52）

数据读写分离

把客户端查询数据的请求 和 写数据的请求（instert，delete，update）
分别发送给不同数据库服务处理

• 减去数据库服务器的访问压力
• 提高硬件利用率

如何实现数据的读写分离

第一种方式：通过程序实现（程序在编写访问数据库服务的脚本 执行select命令时，固定连接从服务器 执行insert命令时 固定连接master数据库服务器）

第二种方式：通过服务实现 搭建提供读写分离服务功能的服务器（程序访问时 连接的不是数据库服务器本机，而是连接读写分离服务器，有读写分离服务器提供数据库连接服务）

提供数据读写分离服务的软件（中间件）

mysql-proxy mycat maxscale

maxscale 软件提供的读写分离服务的工作过程

• 接收到客户连接数据库服务的请求后
• 根据客户端访问数据的命令类型 把请求给不同的数据库服务器处理

配置数据读写分离结构 提供数据存储服务

第一步 配置mysql一主一从 同步结构 （参考前面教程）
第二步 配置读写分离服务器(这个主机没有mysql服务)

1. 安装软件

yum -y install maxscale-2.1,..... 

2. 修改配置文件

# 备份配置文件
cp /etc/maxscale.cnf /root/
# 修改配置文件
vim /etc/maxscale.cnf
[maxscale]
threads=auto # 线程数
# 两台主从服务器 需要两个server
[server1]
type=server
address=192.168.4.51
port=3306
protocol=MySQLBackend
[server2]
...
[MySQL Monitor]
...
servers=server1,server2
user=mysqla
passwd=123qqq...a
...
#[Read-Only Service] 只读服务得注释掉
#...
[Read-Write Service]
...
servers=server1,server2
user=mysqlb
passwd=123qqq...a
...
#[Read-Only Listener] 服务没启动 默认注释掉
#...
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016

3. 根据配置文件设置 配置数据库服务器‘
   因为服务器主从结构，所有添加用户只需要在主服务器主机添加。 从服务器通过添加用户的命令在本机执行 所以只需在主服务器 执行添加用户的命令

 添加监控用户mysqla 监视服务器状态 replication client监视服务器运行状态。replication slave 数据库服务器的主从角色
grant replication client,replication slave on *.* to mysqla@"%" identified by "123qqq...A"
 添加路由用户mysqlb 检查数据库服务器上是添加了客户端连接服务使用的用户
grant slelct on mysql.* to mysqlb@"%" identified by "123qqq...a"

4. 启动读写分离服务

# 首先测试数据库服务器的监视服务
# 启动读写分离服务
maxscale -f /etc/maxscale.cnf
# 看日志文件
vim /var/log/maxscale/maxscale.log ERROR error 字样

5. 查看服务状态
   
6. 查看监控信息
   在56本机查看监控状态

maxadmin -admin -pmariadb -p4016
MaxScale > list servers

第三步 测试配置
先启动 51系统 再启动52系统 最后是56系统
第一步 客户端能够连接读写分离服务器访问数据库服务
首先在主数据库服务器 添加客户端连接使用的用户

51 ~]# mysql -uroot -pNSD2019...a
mysql> create database bbsbd;
mysql> create table bbsdb.a(id int);
grant select,insert on bbsdb.* to yaya@"%" identified by "123qqq...a";
# 在从数据库1上查看存储数据库表和添加用户
52 ~]# mysql -uroot -pNSD2019...a
mysql> desc bbsdb.a;
mysql> select user from mysql.user where user="yaya"

客户端连接读写分离服务 访问数据库服务

mysql -h读写分离服务器的ip -P读写分离服务的端口 -u数据库授权用户名 -p密码
50 ~]# mysql -h192.168.4.56 -P4006 -uyaya -p123qqq...a

第二步 连接读写分离服务后，可以对数据做查询和存储操作

mysql> select * from bbsdb.a;
mysql> insert into bbsdb.a values(8888) 

第三步 测试数据读写分离
怎么验证查询select 访问就在52服务器
从服务器本机表里添加一条记录 （在从服务器添加的新数据 主服务器不会同步）
怎么验证存储数据insert访问在51服务器

如果主从结构中的从服务器宕机了，就实现不了读写分离了，会把读写请求都给主服务器
如果主从结构都宕机了，读写分离服务无法访问
读写分离服务器只有1台 单点故障问题无法避免

MySQL多实例

前置条件：配置58 不需要安装mysql服务
在一台服务器上运行多个数据库服务 节约运维成本 提高硬件利用率

配置多实例：

1. 安装软件

2. 创建并编辑主配置文件（重点）

确保没有 /etc/my.cnf 如果有 要删掉
vim /etc/my.cnf
# 第一部分  管理实例服务的运行参数
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
user=root
# 第二部分  定义实例
[mysqld1] # 实例1
datadir=/dir1 # 第一个实例的数据库目录
port=3307 # 多实例 端口号不能一样
pid-file=/dir1/mysqld1.pid
log-error=/dir1/mysqld1.err
socket=/dir1/mysqld1.sock
[mysqld2] # 实例2
datadir=/dir2 # 第一个实例的数据库目录
port=3308 # 多实例 端口号不能一样
pid-file=/dir2/mysqld1.pid
log-error=/dir2/mysqld1.err
socket=/dir2/mysqld1.sock

3. 启动多实例
   mysqld_multi start 1
   
   无论启动成功没成功，都需要 查看有没有实例的端口 如果查到了得干掉进程， 查看父进程 pstree
   
4. 实例停止

# 需要输入用户名和密码
mysqld_multi --user=root --password=密码 stop 实例编号