读写分离
注意:将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是只读