mysql-proxy实现读写分离

数据库读写分离可以用很多软件实现：mysql-proxy 、MyCat 、Amoeba

用 server3 来做proxy，server1 和 server2 一个读一个写
做以下实验之前，先做好server1和server2的gtid主从复制
server1，server2修改配置文件:

server1登陆数据库，创建用户并授权：

server2配置master：


server3上：
先关闭之前的mysql，因为proxy也用3306端口

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy	#软链接便于访问

cd /usr/local/mysql-proxy/bin
./mysql-proxy --help	##查看帮助
./mysql-proxy --help-proxy	##查看proxy的帮助
./mysql-proxy --help-all	##查看所有帮助

cd /usr/local/mysql-proxy
mkdir conf	##建立配置文件目录

#编辑配置文件（自己手动新建）

vim mysql-proxy.conf	

[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.40.1:3306
proxy-read-only-backend-addresses=172.25.40.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

创建日志目录

mkdir /usr/local/mysql-proxy/log

修改lua脚本

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 

min_idle_connections = 1,	##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为2个好测试
max_idle_connections = 2,

启动mysql-proxy

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

启动报错：
2019-07-30 17:20:05: (critical) mysql-proxy-cli.c:326: loading config from ‘/usr/local/mysql-proxy/conf/mysql-proxy.conf’ failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren’t secure (0660 or stricter required)
2019-07-30 17:20:05: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2019-07-30 17:20:05: (message) shutting down normally, exit code is: 1

因为配置文件权限过大

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

再启动，正常

查看日志

cat /usr/local/mysql-proxy/log/mysql-proxy.log

看到两个节点都加进来了

在server1上授权新用户读写权限

mysql> grant insert,update,select on *.* to cici@'%' identified by 'Westos+123';

mysql> flush privileges;

mysql> create database monster;
Query OK, 1 row affected (0.01 sec)

mysql> use monster;
Database changed
mysql> create table usertb(
    -> username varchar(10) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.02 sec)

mysql>  desc usertb;

在物理机上打开一个shell来连接数据库

[kiosk@foundation61 ~]$ mysql -h 172.25.40.3 -ucici  -pWestos+123

然后在server3上看到连接已经建立

[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 5058 root   10u  IPv4  37024      0t0  TCP server3:mysql (LISTEN)
mysql-pro 5058 root   11u  IPv4  37062      0t0  TCP server3:mysql->gateway:58522 (ESTABLISHED)
mysql-pro 5058 root   12u  IPv4  37063      0t0  TCP server3:53282->server1:mysql (ESTABLISHED)

在物理机上再打开一个shell来连接数据库
再到server3上查看

[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 5058 root   10u  IPv4  37024      0t0  TCP server3:mysql (LISTEN)
mysql-pro 5058 root   11u  IPv4  37062      0t0  TCP server3:mysql->gateway:58522 (ESTABLISHED)
mysql-pro 5058 root   12u  IPv4  37063      0t0  TCP server3:53282->server1:mysql (ESTABLISHED)
mysql-pro 5058 root   13u  IPv4  37414      0t0  TCP server3:mysql->gateway:58526 (ESTABLISHED)
mysql-pro 5058 root   14u  IPv4  37415      0t0  TCP server3:53288->server1:mysql (ESTABLISHED)

’发现两次连接都指向server1’

在物理机上再打开一个shell来连接数据库
再到server3上查看

[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 5058 root   10u  IPv4  37024      0t0  TCP server3:mysql (LISTEN)
mysql-pro 5058 root   11u  IPv4  37062      0t0  TCP server3:mysql->gateway:58522 (ESTABLISHED)
mysql-pro 5058 root   12u  IPv4  37063      0t0  TCP server3:53282->server1:mysql (ESTABLISHED)
mysql-pro 5058 root   13u  IPv4  37414      0t0  TCP server3:mysql->gateway:58526 (ESTABLISHED)
mysql-pro 5058 root   14u  IPv4  37415      0t0  TCP server3:53288->server1:mysql (ESTABLISHED)
mysql-pro 5058 root   15u  IPv4  37449      0t0  TCP server3:mysql->gateway:58528 (ESTABLISHED)
mysql-pro 5058 root   16u  IPv4  37450      0t0  TCP server3:57936->server2:mysql (ESTABLISHED)

’这次连接到了server2，说明读写分离启用’

测试读写分离

在物理机上插入数据：

发现server1和server2都能看到，看不出读写分离，因为存在主从复制

现在到server2上关闭复制

mysql> stop slave;

这时再在物理机上插入数据

在server1上查看：

在srever2查看：

发现插入的数据看不到，但是在server1上可以看到，这就说明了它在读的时候读的是server2上的数据，而写操作却写在了server1上.