mysql读写分离(proxy)
程序员文章站
2022-03-08 17:12:50
...
mysql读写分离
master:server3 172.25.25.3
slave:server2 172.25.25.2
mysql-proxy:server1 172.25.25.1
先做好主从复制,检测一下
在master查看
mysql> show slave status\G
mysql> show master status;
在slave查看
mysql> show slave status\G
mysql> use test;
mysql> select * from yin;
在master对数据库操作
mysql> use test;
mysql> show tables;
mysql> select * from yin;
mysql> insert into yin values('2','dsa');
在slave查看
mysql> select * from yin;
# 设置为只读模式
mysql> set global read_only=1;
slave可以同步到,说明主从复制配制成功
在server1安装部署mysql-proxy
[root@server1 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server1 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@server1 ~]# cd /usr/local/mysql-proxy/
[root@server1 mysql-proxy]# mkdir conf
[root@server1 mysql-proxy]# cd conf/
# 添加环境变量
[root@server1 conf]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server1 conf]# source ~/.bash_profile
编写启动脚本
[aaa@qq.com conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.25.3:3306
proxy-read-only-backend-addresses=172.25.25.2:3306
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
keepalive=true
[aaa@qq.com conf]# cd ..
[aaa@qq.com mysql-proxy]# mkdir logs
[aaa@qq.com mysql-proxy]# chmod 660 conf/mysql-proxy.conf
/usr/local/mysql-proxy/conf/mysql-proxy.conf文件内容:
[aaa@qq.com mysql-proxy]# vim 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, #最大连接数
is_debug = false
}
end
[aaa@qq.com mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --plugins=proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[aaa@qq.com mysql-proxy]# cat logs/mysql-proxy.log
2018-08-14 10:21:39: (message) proxy listening on port 0.0.0.0:3306
2018-08-14 10:21:39: (message) added read/write backend: 172.25.25.3:3306
2018-08-14 10:21:39: (message) added read-only backend: 172.25.25.2:3306
/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua文件更改的内容:
测试:
最大连接数为2,至少需要连接三次才会连接到slave
第一次连接:物理主机
[kiosk@foundation25 ~]$ mysql -h 172.25.25.1 -u root -pRedhat+123
在master上安装lsof
[root@server3 ~]# yum install -y lsof
[root@server3 ~]# lsof -i :3306
server3和server2,server1各有一次连接
第二次连接:物理主机不断开原来的连接,再打开一个shell连接
[kiosk@foundation25 ~]$ mysql -h 172.25.25.1 -u root -pRedhat+123
master
[root@server3 ~]# lsof -i :3306
server3和server2有一次连接,与server1有两次
第三次连接:物理主机不断开原来的连接,再打开一个shell
[kiosk@foundation25 ~]$ mysql -h 172.25.25.1 -u root -pRedhat+123
在master上:
[root@server3 ~]# lsof -i :3306
与第二次相比没有变化
在slave上:
[root@server2 ~]# yum install -y lsof
[root@server2 ~]# lsof -i :3306
这一次server1连接到了slave上
在物理主机连接slave的shell里执行
MySQL [(none)]> use test;
MySQL [test]> insert into yin values('3','dsd');
在master查看
mysql> select * from yin;
master可以查看到,说明插入语句的时候,proxy强制定义到master上
附:
访问一个http的过程(第一行从左到右)
client | cdn | 接入层 | web | app | 分布式 | DB | system | disk |
---|---|---|---|---|---|---|---|---|
firefox | sqiud | 高可用 | apache | php-fpm | memcached | mysql | cpm | ssd |
IE | nginx | 负载均衡 | nginx | tomcat | redis | orcle | mem | raid |
cache | ATS | ~ | srcache | ~ | ~ | DB2 | ~ | ~ |
DNS | varnish | ~ | memc | ~ | ~ | ~ | ~ | ~ |
local | ~ | ~ | ~ | ~ | ~ | ~ | ~ | ~ |