欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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;

mysql读写分离(proxy)
在slave查看

mysql> show slave status\G
mysql> use test;
mysql> select * from yin;

mysql读写分离(proxy)
mysql读写分离(proxy)
在master对数据库操作

mysql> use test;
mysql> show tables;
mysql> select * from yin;
mysql> insert into yin values('2','dsa');

mysql读写分离(proxy)
在slave查看

mysql> select * from yin;
# 设置为只读模式
mysql> set global read_only=1;

mysql读写分离(proxy)
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 

mysql读写分离(proxy)
mysql读写分离(proxy)

编写启动脚本

[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 

mysql读写分离(proxy)
/usr/local/mysql-proxy/conf/mysql-proxy.conf文件内容:
mysql读写分离(proxy)

[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

mysql读写分离(proxy)
mysql读写分离(proxy)
/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua文件更改的内容:
mysql读写分离(proxy)

测试:

最大连接数为2,至少需要连接三次才会连接到slave
第一次连接:物理主机

[kiosk@foundation25 ~]$ mysql -h 172.25.25.1 -u root -pRedhat+123

mysql读写分离(proxy)
在master上安装lsof

[root@server3 ~]# yum install -y lsof
[root@server3 ~]# lsof -i :3306

mysql读写分离(proxy)
server3和server2,server1各有一次连接
mysql读写分离(proxy)

第二次连接:物理主机不断开原来的连接,再打开一个shell连接

[kiosk@foundation25 ~]$ mysql -h 172.25.25.1 -u root -pRedhat+123

mysql读写分离(proxy)
master

[root@server3 ~]# lsof -i :3306

server3和server2有一次连接,与server1有两次
mysql读写分离(proxy)
第三次连接:物理主机不断开原来的连接,再打开一个shell

[kiosk@foundation25 ~]$ mysql -h 172.25.25.1 -u root -pRedhat+123

mysql读写分离(proxy)
在master上:

[root@server3 ~]# lsof -i :3306

与第二次相比没有变化
mysql读写分离(proxy)
在slave上:

[root@server2 ~]# yum install -y lsof
[root@server2 ~]# lsof -i :3306

mysql读写分离(proxy)
这一次server1连接到了slave上
mysql读写分离(proxy)

在物理主机连接slave的shell里执行

MySQL [(none)]> use test;
MySQL [test]> insert into yin values('3','dsd');

mysql读写分离(proxy)
在master查看

mysql> select * from yin;

master可以查看到,说明插入语句的时候,proxy强制定义到master上
mysql读写分离(proxy)

附:
访问一个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 ~ ~ ~ ~ ~ ~ ~ ~