企业级mysql数据库集群实战—— MySQL数据库的读写分离（MySQL-proxy

目录 ：

一、MySQL数据库的读写分离简介

•    1、mysql数据库的读写分离的基础知识（为什么要进行读写分离）
•    2、读写分离的实现方式

二、MySQL数据库读写分离实验

（一）、在server1和server2先配置主从复制

•  1、在server1上进行相关的配置
•   步骤一：在server1上面设置：master节点（基于gtid的异步复制）
•   步骤二：在主配置文件中添加主复制的代码
•   步骤三：初始化数据库
•   步骤四：创建用户并授权
• 2、在server上进行相关的设定
•   步骤一：关闭服务、修改配置文件
•   步骤二：初始化数据库
•   步骤三 ：登录数据库，进行slave和master的链接         

（二）、在server3上进行mysql-proxy代理的相关配置

• 步骤一：
•    1、安装proxy代理包
•    2、了解proxy目录下的相关文件配置
• 步骤二：切换到解压的目录下，进相关的设定
• 步骤三：创建代理配置文件存放的目录
• 步骤四：修改数据库发生读写分离时的最大和最小连接数
• 步骤五：创建日志文件和进程pid文件所在的目录
• 步骤六：启动mysql-proxy
• 步骤七：查看端口是否开启、查看日志、pid是否正常开启

（三）、测试读写分离

• 步骤一：在server1上面:创建新用户并授于读写权限，创建test数据库
• 步骤二：在server2上查看
• 步骤三：查看主从复制的效果
• 步骤五：查看数据是否同步

（四）、验证proxy是否启用读写分离

• 步骤一：在server3上：安装lsof查看其他服务链接server3主机proxy的代理情况
• 步骤二：查看3306端口被连接的情况
• 步骤三：在真机上运程登录server3数据库端
• 步骤四：断开连接，再次登录（查看proxy的代理原理）

（五）、验证读写分离的工作原理

• 步骤一：真机上写入数据、查看实验效果
• 步骤二：关闭slave、在真机上写入数据查看实验效果
• 实验总结

      

 

一、MySQL数据库的读写分离简介

1.mysql数据库的读写分离的基础知识（为什么要进行读写分离）

对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的业务操作
（新闻网站，插入一条新闻，查询操作）
剩下的则是写（insert、update、delete，只要能对MySQL的数据造成更改的操作都叫写操作）操作
在使用负载均衡集群之后，可以很大程度的提升网站的整体性能，但是最终的数据处理的压力还是会落到MySQL数据库上
所有很有必要使用一些技术来提升MySQL的负载能力。（读写分离）
写专门交给写服务器处理（一般网站来说写是比较少的 读写比 4:1） 那么需要把读的任务分配多台服务器来完成的架构，就叫做读写分离

2.读写分离的实现方式

• 注意：    （1）php程序上自己做逻辑判断，写php代码的时候，自己在程序上做逻辑判读写匹配。select，insert、update、delete做正则匹配，根据结果选择写服务器（主服务器）。如果是select操作则选择读服务器（从服务器器） mysql_connect(‘读写的区分’)
•     （2）MySQL- Proxy是实现"读写分离(Read/Write Splitting)"的一个软件（MySQL官方提供 ，也叫中间件），基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查询操作（select）。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。

注意：MySQL-proxy 它能实现读写语句的区分主要依靠的是内部一个lua脚本（能实现读写语句的判断）
注意：如果只在主服务器（写服务器）上完成数据的写操作话；这个时候从服务器上没有执行写操作，是没有数据的
这个时候需要使用另外一个技术来实现主从服务器的数据一致性，这个技术叫做 主从复制技术， 所以说主从复制是读写分离的基础
读写分离(MySQL- Proxy)是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求
对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上
因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率

• 1、mysql数据库的读写分离的基础知识（为什么要进行读写分离）
•    2、读写分离的实现方式

 

二、MySQL数据库读写分离实验

1、搭建实验环境

主机名	IP	角色	服务
server1	172.25.6.1	master	mysqld
server2	172.25.6.2	slave	mysqld
server3	172.25.6.3	代理端	mysql-proxy
[root@foundation6 ~]#	172.25.6.250	客户端（登录proxy代理端）

 

 2、实验原理简诉

• 应用：把写和读分开
• 如果业务压力不是很大的时候要做读写分离，取决于硬盘读取的性能，客户才满意
• 读库（配置低），写库（配置高：固态硬盘，高速存储）
• 读写分离->mysql集群、分布式、代理、高可用
• 1、mysql数据库的读写分离的基础知识（为什么要进行读写分离）
•    2、读写分离的实现方式
• 数据不一定来源于同一个地方
• 还会有测试库，模拟上线，测试服务器
• proxy，相当于一个代理
• 实验一共需要量三台主机
• server1做读、写库
• server2做读库
• server3做proxy代理
• 并且两个库（server1和server2）要实现读写分离，但是还要实现数据同步，基于gtid的异步复制（主从复制）

 

实验部分

（一）、在server1和server2先配置主从复制

1、在server1上

步骤一：

• 1、mysql数据库的读写分离的基础知识（为什么要进行读写分离）
•    2、读写分离的实现方式

在server1上面设置：master节点（基于gtid的异步复制）

[root@server1 ~]# systemctl start mysqld

 

 

步骤二：

在主配置文件中添加主复制的代码

• 1、mysql数据库的读写分离的基础知识（为什么要进行读写分离）
•    2、读写分离的实现方式

 

步骤三：

初始化数据库

1.  
   [root@server1 mysql]# grep password /var/log/mysqld.log ##查看密码日志
2.  
   [root@server1 ~]# mysql_secure_installation ##初始化数据库
3.  
    
4.  
   Securing the MySQL server deployment.
5.  
    
6.  
   Enter password for user root: ##输入初始化密码
7.  
    

 

步骤四：

创建用户并授权

1.  
   [root@server1 ~]# mysql -uroot -pYang+123love ##登录数据库
2.  
   mysql> alter user root@localhost identified by 'Yang+123love'; ##创建用户并授权
3.  
   mysql> show databases;
4.  
   mysql> grant replication slave on *.* to repl @'172.25.6.%' identified by 'Yang+123love'; ##授予slave权限
5.  
   mysql> show master status;

 

（二）在server2上进行相关的设定

步骤一：

关闭服务、修改配置文件

1.  
   [root@server2 mnt]# systemctl stop mysqld ##关闭服务上一篇实验有官这方面的解释
2.  
   [root@server2 mnt]# cd /var/lib/mysql
3.  
   [root@server2 mysql]# ls
4.  
   [root@server2 mysql]# rm -rf * ##删除之前实验的配置环境
5.  
   [root@server2 mysql]# vim /etc/my.cnf
6.  
   加入：
7.  
   server_id=2
8.  
   log-bin=mysql-bin
9.  
   gtid_mode=ON
10.  
    enforce_gtid_consistency=true
11.  
     
12.  
    [root@server2 ~]# systemctl start mysqld ##重启服务

 

步骤二：

初始化数据库

1.  
   [root@server1 mysql]# grep password /var/log/mysqld.log ##查看密码日志
2.  
   [root@server1 ~]# mysql_secure_installation ##初始化数据库
3.  
    
4.  
   Securing the MySQL server deployment.
5.  
    
6.  
   Enter password for user root: ##输入初始化密码
7.  
    

 

步骤三 ：

登录数据库，进行slave和master的链接

1.  
   [root@server2 ~]# mysql -uroot -pYang+123love ##登录数据库
2.  
   Enter password:
3.  
   mysql> alter user root@localhost identified by 'Yang+123love'; ##进行身份认证
4.  
   mysql> change master to master_host='172.25.6.1',master_user='repl',master_password='Yang+123love',master_auto_position=1; ##和matser主机相连
5.  
   mysql> start slave; ##开启slave服务
6.  
   mysql> show slave status\G
7.  
   ...
8.  
   Slave_IO_Running: Yes ##查看IO线程
9.  
   Slave_SQL_Running: Yes ##sql线程
10.  
    ...

(io线程和sql线程为yes是说明配置成功！！！)

 

3、在server3上进行mysql-proxy代理的相关配置

在server3上面：搭建proxy代理服务器（实现客户端写在server1上面、客户端读server2上的数据）

步骤一：

安装proxy代理包

1.  
   [root@server3 ~]# systemctl stop mysqld停止之前的mysqld服务
2.  
   [root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/加压到指定目录下

 

步骤二：

1、切换到解压的目录下，进相关的设定

1.  
   [root@server3 ~]# cd /usr/local/ ##切换到解压的目录下
2.  
   [root@server3 local]# ll ##查看
3.  
   [root@server3 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy ##更改名称方便实验
4.  
   [root@server3 local]# ll查看

 

2、了解proxy目录下的相关文件配置

1.  
   [root@server3 local]# cd mysql-proxy/ ##进入proxy服务的目录
2.  
   [root@server3 mysql-proxy]# ls
3.  
   bin include lib libexec licenses share
4.  
   [root@server3 mysql-proxy]# cd bin/ ##进入脚本目录
5.  
   [root@server3 bin]# ls
6.  
   mysql-binlog-dump mysql-myisam-dump mysql-proxy ##启动脚本

 

步骤三：

创建代理配置文件存放的目录

1.  
   [root@server3 bin]# ls
2.  
   mysql-binlog-dump mysql-myisam-dump mysql-proxy
3.  
   [root@server3 bin]# cd ..
4.  
   [root@server3 mysql-proxy]# ls
5.  
   bin include lib libexec licenses share
6.  
   [root@server3 mysql-proxy]# mkdir conf ##创建目录
7.  
   [root@server3 mysql-proxy]# ls
8.  
   bin conf include lib libexec licenses share
9.  
   [root@server3 mysql-proxy]# cd conf/ ##进入目录中
10.  
    [root@server3 conf]# ls
11.  
    [root@server3 conf]# vim mysql-proxy.conf ##编写代理服务的配置文件
12.  
    ...
13.  
     
14.  
    [mysql-proxy]
15.  
    proxy-address=0.0.0.0:3306 ##mysql-proxy运行的端口
16.  
    proxy-read-only-backend-addresses=172.25.6.2:3306 ##slave节点：只读
17.  
    proxy-backend-addresses=172.25.6.1:3306 ##master节点：可读可写
18.  
    proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本的路径
19.  
    pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid ##进程pid的位置
20.  
    log-file=/usr/local/mysql-proxy/log/mysql-proxy.log ##日志位置
21.  
    plugins=proxy
22.  
    log-level=debug ##定义日志级别
23.  
    keepalive=true ##mysql-proxy崩溃时尝试重启
24.  
    daemon=true ##打入后台

 

步骤四：

修改数据库发生读写分离时的最大和最小连接数

1.  
   [root@server3 bin]# cd ..
2.  
   [root@server3 mysql-proxy]# ls
3.  
   bin conf include lib libexec licenses share
4.  
   [root@server3 mysql-proxy]# find . -name *.lua ##查看配置文件所在的路径
5.  
   [root@server3 mysql-proxy]# cd share/doc/mysql-proxy
6.  
   [root@server3 mysql-proxy]# pwd
7.  
   /usr/local/mysql-proxy/share/doc/mysql-proxy
8.  
   [root@server3 mysql-proxy]# ls
9.  
   [root@server3 mysql-proxy]# vim rw-splitting.lua ##修改主机数量
10.  
    ...
11.  
    40 min_idle_connections = 1, 最小连接数
12.  
    41 max_idle_connections = 2, 最大连接数
13.  
     
14.  
    ...

 

步骤五：

创建日志文件和进程pid文件所在的目录

1.  
   [root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/
2.  
   [root@server3 mysql-proxy]# ls
3.  
   [root@server3 mysql-proxy]# mkdir log ##建立目录，存放进程pid和日志
4.  
   [root@server3 mysql-proxy]# ls

 

步骤六：

启动mysql-proxy

1.  
   [root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf ##启动mysql-proxy
2.  
   [root@server3 mysql-proxy]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf ##给文加设定权限

报错：

2020-03-26 23:10:10: (critical) Key file contains key 'daemon' which has value that cannot be interpreted.
2020-03-26 23:10:10: (message) Initiating shutdown, requested from mysql-proxy-cli.c:367
2020-03-26 23:10:10: (message) shutting down normally, exit code is: 1

解决方法：

在配置文件mysql-proxy.conf 中加入
daemon=1
keepalive=1

重新开启

(开启成功！！！)

其实就编辑了一个配置文件和lua文件

 

步骤七：

查看端口是否开启、查看日志、pid是否正常开启

1.  
   [root@server3 conf]# netstat -anltp
2.  
   [root@server3 log]# cat mysql-proxy.pid
3.  
   [root@server3 log]# cat /usr/local/mysql-proxy/log/mysql-proxy.log

（查看日志看到两个节点都加进来了，说明已经配置成功！！！）

 

（二）、测试读写分离

步骤一 ：

在server1上面:创建新用户并授于读写权限，创建test数据库

1.  
   [root@server1 ~]# mysql -uroot -pYang+123love
2.  
   mysql> grant insert,update,select on *.* to yang@'%' identified by 'Yang+123love';新建一个用户yang并对他授权
3.  
   mysql> flush privileges;刷新授权表
4.  
   mysql> show databases;查看数据库信息

• 1、mysql数据库的读写分离的基础知识（为什么要进行读写分离）
•    2、读写分离的实现方式

步骤二：

在server2上查看

 

步骤三：

查看主从复制的效果

1、在server1上创建库

1.  
   mysql> create database test1;创建test1库
2.  
   mysql> show databases;

 

2、在server2上查看

（查看到在 server1上创建好的库）

 

步骤四：

1、在server1上创建表格

1.  
   mysql> use westos;
2.  
   Database changed创建usertb表
3.  
   mysql> create table usertb (
4.  
   -> username varchar(10) not null,
5.  
   -> password varchar(15) not null);
6.  
   mysql> desc usertb;查看表结构

 

2、在server2上（看不到创建的表格）

 

步骤五：

查看数据是否同步

1、在server1上向表格中写入数据

1.  
   mysql> insert into users values ('user1',20');
2.  
   mysql> use test1;
3.  
   Database changed
4.  
   mysql> select * from users;

 

2、在server2上：

查看数据是否同步

 

 

（三）、验证proxy是否启用读写分离

步骤一：

在server3上：

安装lsof查看其他服务链接server3主机proxy的代理情况

[root@server3 ~]# yum install lsof -y 

 

 

步骤二：

查看3306端口被连接的情况

[root@server3 ~]# lsof -i:3306   

 

 

步骤三：

在真机上运程登录server3数据库端

1.  
   [root@foundation6 ~]# mysql -h 172.25.6.3 -uyang -pYang+123love ##运程登录server3数据库
2.  
   MySQL [(none)]> show databases;

查看到的是第一次连接代理的是server1主机

 

步骤四：

断开连接，再次登录

（还是server1）

 

步骤五：

断开连接，再次登录

由于在同一个shell中连接两次，所有lsof -i:3306查看到第一次连接到server1，第二次连接到server2

到此，server1与server2均被连接到，说明读写分离启用。

 

（四）、验证读写分离的工作原理

步骤一：

1、在server1上写入数据：

1.  
   mysql> insert into users values ('user2','22'); ##插入数据
2.  
   mysql> select * from users; ##查看创建的数据

2、在server2上查看

3、在真机上查看

（说明在server1端上可以写入数据）

 

步骤二：

1、在真机上写入数据

MySQL [test1]> insert into users values('user3','23');          ##写入数据

 

2、在server1端

3、在server2端

总结：server1（master）上能看到数据说明写操作是在master上
而slave可以查看到数据是因为server1（master）和server2（slave）是主从复制的关系
不能说明server2上可以进行写操作，为了更清楚的验证读写分离，我们做下面的实验

 

步骤三：

关闭server2上的slave服务（stop slave;关闭server2的异步复制）

1、在server2上关闭slave

mysql> stop slave;

 

 

2、在真机上插入数据

1.  
   MySQL [test1]> insert into users values('user4','24'); ##写入数据
2.  
   MySQL [test1]> select * from users; ##查看数据

（发现在真机上查看不到刚刚写入的数据！！！）

 

2、在server2上查看（也看不到刚写入的数据）

3、在server3上查看真机上写入的数据

结论：当前server2的slave是关闭的所以不能进行主从复制
测试端插入数据server1（master）可以看到说明写操作是在server1上进行的
server2上没有新插入的数据，读操作是在server2上，所以读写分离了

实验总结：

当访问数据库的真机（用户）数量很多的时候，数据库的代理就会把后端的数据库实现读写分离

在本实验中
server1是写的数据库
server2是读的数据库
当server1和server2满足gtid的异步复制的时候，真机往数据库写入的东西其实是写入了server1，并没有写入server2，server2上面的数据是复制过去的
因此server1、server2、真机上面都能查到刚刚写进去的数据，其实真机查的是server2（读）
当关闭server1和server2的异步复制的时候，真机往数据库写入数据的东西只写进了server1，没有写进去server2，server2也没有复制。
因为server1可以查看到，server2和真机上面都查不到刚刚写进去的数据，此时的真机读的是server