proxy实现mysql读写分离

实验环境

iptables和selinux关闭
redhat6.5
master：server2 172.25.35.52
slave：server3 172.25.35.53
proxy：servre4 172.25.35.54

配置实现

首先实现server2和server3的mysql主从复制，具体步骤见：https://blog.csdn.net/Ying_smile/article/details/81739152
server4:

[root@server4 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@server4 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local
[root@server4 ~]# cd /usr/local
[root@server4 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server4 local]# cd mysql-proxy
[root@server4 mysql-proxy]# mkdir conf
[root@server4 mysql-proxy]# cd conf
[root@server4 conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.35.52:3306   //后端master
proxy-read-only-backend-addresses=172.25.35.53:3306  后端slave
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua   //实现读写分离的脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=debug   //日志
daemon=true
keepalived=true
[root@server4 conf]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 
 38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 2,   //为了测试设置最大连接数
 42 
 43                 is_debug = false
 44         }
 45 end
[root@server4 conf]# vim /etc/profile  //添加环境变量
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server4 conf]# vim ~/.bash_profile 
[root@server4 conf]# mkdir ../logs
[root@server4 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server4 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server4 conf]# netstat -antlp |grep mysql-proxytcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1690/mysql-proxy    
tcp        0      0 172.25.35.54:3306           172.25.35.250:57896         ESTABLISHED 1690/mysql-proxy    
tcp        0      0 172.25.35.54:60100          172.25.35.52:3306           ESTABLISHED 1690/mysql-proxy    

客户端测试：

首先root用户需要在master和slave做授权

[root@foundation35 ~]# mysql -u root -h 172.25.35.54 -pZhanG@2424
MySQL [(none)]> use haha;
MySQL [haha]> select * from info;

MySQL [haha]> insert into info values('user6','666');
Query OK, 1 row affected (0.73 sec)

MySQL [haha]> select * from info;

server2、server3都可以查看到插入的数据：

[root@server2 ~]# mysql -pZhanG@2424
MySQL [haha]> select * from info;

监控查看连接情况：

server3:
[root@server3 ~]# yum install lsof -y  //下载监控工具
[root@server3 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1898 mysql   35u  IPv6   9702      0t0  TCP *:mysql (LISTEN)
mysqld  1898 mysql   55u  IPv4   9761      0t0  TCP server3:52117->server2:mysql (ESTABLISHED)

server2:
[root@server2 ~]# lsof -i :3306  
//查看首先连接的是master，所以此时是对master读写，还没有读写分离
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1945 mysql   34u  IPv6  10443      0t0  TCP server2:mysql->server3:52117 (ESTABLISHED)
mysqld  1945 mysql   35u  IPv6  10375      0t0  TCP *:mysql (LISTEN)
mysqld  1945 mysql   58u  IPv6  10871      0t0  TCP server2:mysql->server4:60100 (ESTABLISHED)

当开启第三个客户端的连接的时候，因为超过了最大连接数，会连接slave，对slave读对master写，<font color=#dd0000">产生读写分离:

server2：
[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1945 mysql   34u  IPv6  10443      0t0  TCP server2:mysql->server3:52117 (ESTABLISHED)
mysqld  1945 mysql   35u  IPv6  10375      0t0  TCP *:mysql (LISTEN)
mysqld  1945 mysql   51u  IPv6  10964      0t0  TCP server2:mysql->server4:60101 (ESTABLISHED)
mysqld  1945 mysql   58u  IPv6  10871      0t0  TCP server2:mysql->server4:60100 (ESTABLISHED)

server3：
[root@server3 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1898 mysql   35u  IPv6   9702      0t0  TCP *:mysql (LISTEN)
mysqld  1898 mysql   49u  IPv6  10480      0t0  TCP server3:mysql->server4:38316 (ESTABLISHED)
mysqld  1898 mysql   55u  IPv4   9761      0t0  TCP server3:52117->server2:mysql (ESTABLISHED)