mysql的读写分离

读写分离

注意：将server1 and server2 恢复到之前的GTID复制

配置server3：

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit  /usr/local/mysql-proxy
Cd /usr/local/mysql-proxy

cd bin/

./mysql-proxy --help-proxy

[root@server3 bin]# cd ..
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf


[mysql-proxy]
proxy-address=0.0.0.0:3306  运行端口
proxy-read-only-backend-addresses=172.25.13.2:3306  slave节点只读   
proxy-backend-addresses=172.25.13.1:3306   master读写
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
plugins=proxy 
log-level=debug  日志级别
keepalive=true   崩溃重启
daemon=true   打入后台


[root@server3 conf]# cd  /usr/local/mysql-proxy/share/doc/mysql-proxy/
[root@server3 mysql-proxy]# vim r
ro-balance.lua    ro-pooling.lua    rw-splitting.lua  
[root@server3 mysql-proxy]# vim rw-splitting.lua 

[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# mkdir log

[root@server3 mysql-proxy]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 

[root@server3 mysql-proxy]# cd  /usr/local/mysql-proxy/log/
[root@server3 log]# ls
mysql-proxy.log  mysql-proxy.pid
查看日志是否有报错 

测试:

server1:
mysql> grant insert,update,select on *.* to fangyuan@'%' identified by 'Redhat=123';

mysql> flush privileges;
mysql> create database westos;
mysql> use westos;
Database changed
mysql> create table redhat(
    -> username varchar(10) not null,
    -> password varchar(10) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into  redhat values ('user1','123');

Server2:查看

Server3:
[root@server3 log]# yum install lsof  -y
[root@server3 log]# lsof -i:3306
真机访问连续三次

mysql  -h 172.25.13.3     -ufangyuan   -pRedhat=123

Server3：查看用户多的时候自动启动读写分离

关闭server2的slave  
Stop slave；

真机在数据库写入数据

可以看到可以写入但是不能查看因为读取的是server2上的数据

在server1：上查看

可以看到数据已经插入说明server1是读写
Server2是只读