proxy实现mysql读写分离
程序员文章站
2022-05-02 10:03:55
...
实验环境
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做授权
[aaa@qq.com ~]# 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都可以查看到插入的数据：
[aaa@qq.com ~]# mysql aaa@qq.com
MySQL [haha]> select * from info;
监控查看连接情况：
server3:
[aaa@qq.com ~]# yum install lsof -y //下载监控工具
[aaa@qq.com ~]# 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:
[aaa@qq.com ~]# 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写，产生读写分离:
server2：
[aaa@qq.com ~]# 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：
[aaa@qq.com ~]# 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)