mysql的读写分离 mysql-proxy

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服务器上
因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率

下面演示MySQL- Proxy读写分离配置过程

搭建实验环境
server1 172.25.15.1 master mysqld
server2 172.25.15.2 slave mysqld
server3 172.25.15.3 代理端 mysql-proxy

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

在server1和server2先配置主从复制

目前的实验环境是上一篇组复制的环境，我先把环境还原到基于gtid的主从复制模式

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

[root@server1 mysql]# systemctl stop mysqld
[root@server1 mysql]# rm -rf *
[root@server1 mysql]# systemctl start mysqld

grep password /var/log/mysqld.log
2019-09-24T15:16:02.500409Z 1 [Note] A temporary password is generated for root@localhost: :JwQGkR#*4/l
mysql> alter user root@localhost identified by ‘Yty123+ld’;
Query OK, 0 rows affected (0.05 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

mysql> grant replication slave on . to repl @‘172.25.15.%’ identified by ‘Yty123+ld’;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show master status;
±--------------±---------±-------------±-----------------±-----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------±---------±-------------±-----------------±-----------------------------------------+
| binlog.000002 | 671 | | | 388faf83-dede-11e9-88e3-525400aa6fcc:1-2 |
±--------------±---------±-------------±-----------------±-----------------------------------------+
1 row in set (0.00 sec)

vim /etc/my.cnf

systemctl start mysqld

在server2上设置slave节点







现在server1（master节点）和server2（slave节点）两个数据库已经准备好，基于gtid的主从复制

接下来配置server3代理端（mysql-proxy）
从真机发送proxy安装包给server3

在server3上面进行配置
systemctl stop mysqld停止之前的mysqld服务
ls查看传过来的压缩包
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/解压到指定目录下

cd /usr/local/切换到解压的目录下
ls查看
mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy给一个短点的名字
ls查看

cd mysql-proxy/进去proxy服务的目录
ls查看
cd bin/进入脚本目录
ls
mysql-binlog-dump mysql-myisam-dump mysql-proxy启动脚本

./mysql-proxy --help

./mysql-proxy --help-proxy

新建一个配置文件的目录，创建配置文件

[mysql-proxy]
2 proxy-address=0.0.0.0:3306 ##mysql-proxy运行的端口
3 proxy-read-only-backend-addresses=172.25.15.2:3306 ##slave节点：只读
4 proxy-backend-addresses=172.25.15.1:3306 ##master节点：可读可写
5 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本的路径
6 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid 进程pid的位置
7 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log ##日志位置
8 plugins=proxy
9 log-level=debug ##定义日志级别
10 keepalive=true ##mysql-proxy崩溃时尝试重启
11 daemon=true ##打入后台

修改数据库发生读写分离时的最大和最小连接数
find . -name *.lua


== 创建日志文件和进程pid文件所在的目录==
cd /usr/local/mysql-proxy/
mkdir log 建立目录，存放进程pid和日志

启动mysql-proxy
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 报错


chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf修改配置文件的权限
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf


此时server3 proxy 已经配置好

接下来测试读写分离
在server1上面:创建新的yty用户并且授权，创建test数据库

flush privileges;刷新授权表
show databases;查看数据库信息

之前已经创建过test库和usertb表，server1，2都可以看到

现在开始测试读写分离

server3安装lsof
yum install -y lsof

在真机上连接数据库代理server3 （第一次）

在server3上面:lsof -i:3306

在真机上连接数据库代理server3（第二次）


在真机上连接数据库代理server3（第三次）


在server3上面:lsof -i:3306
发现开始读写分离(用户超过2的时候，读到了server2上)


server1，2的主从复制正常

现在在真机1上面开始对数据库写

现在在真机写入的可以读到自己写进去的数据，是因为读的是server2，实际上写入的是server1，由于server1和server2是主从复制的关系，所以server2上有server1的数据
在server1上查看：

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

在真机1上面写:

这次发现不能读到刚写入的数据了，就是因为server2没有复制到server1的数据

再次进入server1看，发现可以读到数据

总结：当前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和真机上面都查不到刚刚写进去的数据，此时的真机读的是server2