用gtid做mysql的读写分离

实验环境：
server1： 172.25.254.1 --> 读写库
server2： 172.25.254.2 --> 只读库
server3： 172.25.254.3 --> mysql-proxy 结点

server-uuid=26ff60fd-92a1-11ea-9713-525400e49b44

简介

mysql的读写分离：

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

读写分离的必要性：

减轻单台数据库服务器的并发访问压力 提高数据库服务器硬件利用率

使用的工具：

• mysql-proxy 只有企业6版本的，但再企业7中也可以使用
• Mycat 常用
• Amoeba 不常用,不支持事务

这里我们使用mysql-proxy进行讲解学习。

SQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经由MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。

配置mysql进行读写分离

server1 和 server2 ：
关闭mysql，清除之前的所有数据，再开启mysql。更改配置文件：

server1:
log-bin=mysql-bin
server-id=1
gtid_mode=ON           使用 gtid 的模式进行复制
enforce-gtid-consistency=ON

server2:
log-bin=mysql-bin
server_id=2
gtid_mode=ON
enforce-gtid-consistency=ON

登陆server1 数据库：

mysql> grant replication slave on *.* to repl@'172.25.254.%' identified by 'Cc990718-+';
Query OK, 0 rows affected, 1 warning (0.04 sec)

授权并创建repl用户，允许172.25.254网段的slave结点复制。

登陆server2数据库：

change master to master_host='172.25.254.1',master_user='repl',master_password='Cc990718-+',master_auto_position=1;
start slave ;    
show slave status\G;

IO 和SQL 线程开启.

再server3中安装 mysql-proxy 到/usr/local 下：

tar zxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
ln -s /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz  /usr/local/mysql-proxy 

目录中有以下目录：

没有自带的配置文件，我们自行编写一个：

mkdir conf
vim conf/mysql-proxy.conf

[mysql-proxy]
proxy-address=0.0.0.0:3306        指定地址和端口，充当数据库
proxy-backend-addresses=172.25.254.1:3306		指定读写后端
proxy-read-only-backend-addresses=172.25.254.2:3306    只读后端
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua    lua脚本
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid		pid文件
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log		日志文件
plugins=proxy		插件模式
log-level=debug		日志级别，debug为最详细的日志级别
keepalive=true		相当于守护进程
daemon=true

mysql-proxy的读写分离lua文件在 /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ，它是有一定的启动机制的：

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,       
                max_idle_connections = 2,

代表最小连接一个，最大连接两个时，就开启读写分离，我们这里改成1 和2 是方便我们的实验。

然后我们创建log目录 mkdir /usr/local/mysql-proxy/log。

尝试启动:

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

查看日志文件，看见读后端和读写后端已经添加，并监听3306端口。

然后我们应该在server1创建一个用户，并进行授权，因为我们在mysql安全初始化的时候禁用了root远程登陆。
server1:

mysql> grant insert,update,select on *.* to cay@'%' identified by 'Cc990718-+';
Query OK, 0 rows affected, 1 warning (0.29 sec)
#授予 insert update select 权限
mysql> flush privileges;      # 刷新授权表
Query OK, 0 rows affected (0.08 sec)

在serve1插入数据：

#创建一个表
mysql> create table usertb (
    -> username varchar(10) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.25 sec)

然后我们在真实的物理主机上进行远程登陆：

在server3上安装 lsof 可以查看我们的连接：

yum install lsof -y
lsof -i:3306   查询3306端口

我们可以看出当只有一个请求的时侯还没有进行读写分离，连接到了server1上。
我们在用物理主机连接两个：

此时我们可以看见连接第三个的时候已经读写分离了，server3主机已经将请求转发到了只读后端server2.此时写只能从server1上去写，读只能从server2上去读。

那么如何去证明这些那？
我们先关闭server2的主从复制，这样的话，我们在写的时候只能从server1去写，但是由于没有复制，server2上时看不到插入的信息的，而我们恰巧时从server2上读取的，所以我们看不到写入的数据。

在server2关闭主从复制：

mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)

我们用刚刚连接的物理机进行写操作,插入数据：

我们却读取不到刚插入的数据，但是在server1中就能读取到:

这就说明我们写到了server1中，读取时从server2中读取。由于server2关闭了主从复制，所以看不到插入的数据。

现在我们在server2中打开主从复制：

 start slave;

然后在物理机中查看：

就可以看到了。