mysql（三）读写分离与高可用

mysql（三）读写分离与高可用

mysql-proxy实现读写分离

读写分离可以用很多软件实现：mysql-proxy 、MyCat 、Amoeba；我们采用的是mysql-proxy。

用server3来做proxy，1和2一个读一个写
做实验之前，先做好server1和server2的gtid主从复制

node3

#先关闭之前的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.136.1:3306
proxy-read-only-backend-addresses=172.25.136.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脚本
min_idle_connections = 1,	##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为1个好测试
max_idle_connections = 2,

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


#可能会启动报错：
2019-03-15 14:15:04: (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-03-15 14:15:04: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2019-03-15 14:15:04: (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
看到两个节点都加进来了

2019-03-15 15:07:22: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2521 alive
2019-03-15 15:07:22: (debug) chassis-unix-daemon.c:157: waiting for 2521
2019-03-15 15:07:22: (debug) chassis-unix-daemon.c:121: we are the child: 2521
2019-03-15 15:07:22: (critical) plugin proxy 0.8.5 started
2019-03-15 15:07:22: (debug) max open file-descriptors = 1024
2019-03-15 15:07:22: (message) proxy listening on port 0.0.0.0:3306
2019-03-15 15:07:22: (message) added read/write backend: 172.25.136.1:3306
2019-03-15 15:07:22: (message) added read-only backend: 172.25.136.2:3306

node1

授权新用户读写权限
mysql> grant insert,update,select on *.* to wsp@'%' identified by 'Wsp+123ld';

mysql> flush privileges;

mysql> use westos;
Database changed
mysql> create table usertb (
    -> username varchar(10) not null,
    -> password varchar(15) not null);

mysql> desc usertb;


此时在另一台机器连接数据库（node3），当连接个数超过两个时，会发生负载均衡
eg：
[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root   10u  IPv4  28318      0t0  TCP