mysql(6)基于GTID主从复制的读写分离

1.什么是读写分离

使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。
MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。

2.为什么要进行读写分离

大型网站会触发大量的并发式访问，当负载均衡不能满足我们对压力分担的要求时，可以采用读写分离，大量的数据连接会降低服务器的效率，甚至还会影响数据的安全，对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的业务操作，所以为了分担连接数，可以采用读写分离的方式。

减少数据库的连接方法：利用主从数据库来实现读写分离，从而分担主数据库的压力，从库负责读，主库负责写，通过mysql-proxy作为服务器代理，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上，区分不同任务给不同的服务器。

在多个服务器上部署mysql，将其中一台认为主数据库，而其他为从数据库，实现主从同步。其中主数据库负责主动写的操作，而从数据库则只负责主动读的操作（slave从数据库仍然会被动的进行写操作，为了保持数据一致性），这样就可以很大程度上的避免数据丢失的问题，同时也可减少数据库的连接，减轻主数据库的负载。
因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，不影响了查询的效率。
** 主从复制是读写分离的基础**

3.实验环境

server1 master 172.25.62.1
server2 slave 172.25.62.2
server3 mysql-proxy 172.25.62.3

4.实验思想

server3为代理接受请求，通过lua脚本分析操作是读操作还是写操作，读操作分给slave完成，写操作分给master来完成。

5. 实验步骤

server1:

现在server1，server2上的基于GTID的主从复制配置好
先将之前的环境删掉

server2也一样

[root@server1 mnt]# systemctl stop mysqld [root@server1 mnt]# cd /var/lib/mysql [root@server1 mysql]# ls [root@server1 mysql]# rm -rf * [root@server1 mysql]# systemctl start mysqld

修改配置文件，改为基于GTID的主从复制，不会的看我之前的博客

[root@server1 mysql]# vim /etc/my.cnf
加入：
server_id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=tru

过滤密码，初始化

在里面建立用于复制的用户，并授权，可以看见已经配置好master了

server2:

一样的操作

改配置文件

修改密码初始化

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200515202236274.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMzQwODE0,size_16,color_FFFFFF,t_70`在这里插入图片描述

设置复制
root@server2 mysql]# grep password /var/log/mysqld.log 
2019-07-28T03:16:05.199968Z 1 [Note] A temporary password is generated for root@localhost: cRP_dg<Ye1.r
[root@server2 mysql]# mysql -uroot -p
Enter password: 
mysql> alter user root@localhost identified by 'Gaojia+123';
mysql> change master to master_host='172.25.62.1',master_user='repl',master_password='Gaojia+123',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G

两个线程都好着

插入数据试试

可以，说明基于GTID的主从复制配好了

server3：

将包拷到server3上

在/usr/local/mysql-proxy创建conf作为配置文件的目录，因为la脚本里面指定的路径就是这样

1 [mysql-proxy] 2 proxy-address=0.0.0.0:3306 ##mysql-proxy运行的端口 3 proxy-read-only-backend-addresses=172.25.62.2:3306 ##slave节点：只读 4 proxy-backend-addresses=172.25.62.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 ##打入后台

寻找lua脚本位置

编辑lua脚本，修改最大连接数和最小连接数上限

由于上面配置文件有日志位置，所以要创建日志目录

开启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

发现mysql-proxy已经打开

安装lsof可以看见具体的3306端口代理访问指向哪里
此时是没有触发读写分离的

测试

真机远程链接 ，先连一个

看到指向了server1

再连一个

看到还是指向server1

再连一个

看到有的指向server1，有的指向server2

这时候远程插入数据

可以看见，server1上有数据

server2上也有

当停止slave时

再远程添加数据

server1上是可以写进去的

由于停止slave，数据没有同步

这时虽然server1上写入了，但是远程是读不到的，因为读操作是在slave上的，要读的话，start slave，让数据同步就好