/usr/local/mysql/bin/mysql
----------------------------------------------->
GRANT ALL ON *.* TO 'test'@'192.168.19.%' IDENTIFIED BY 'redhat'; //这里要和demo.sh中的Proxy用户列表信息保持一致
FLUSH PRIVILEGES;
4.回到oneproxy服务器启动进程:
killall -9 oneproxy //先清理掉之前的进程
./demo.sh
tail -f oneproxy.log //查看日志
mysql -uadmin -pOneProxy -h192.168.19.79 --port=4041
----------------------------------------------------------------->
LIST BACKEND;
+------+--------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+---------+-------+---------+-------+---------+------+------+---------+
| INDX | ADDRESS | TYPE | STATUS | MARKUP | REQUESTS | DEGREE | GROUP | IS_M | IS_S | MFile | MOffset | DFile | DOffset | RFile | ROffset | IO | SQL | Seconds |
+------+--------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+---------+-------+---------+-------+---------+------+------+---------+
| 1 | 127.0.0.1:3306 | RW/Master | UP | 0 | 0 | 0 | | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 192.168.19.66:3306 | RW/Master | UP | 1 | 0 | 0 | server1 | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | 192.168.19.74:3306 | RO/Slave | UP | 1 | 0 | 0 | server1 | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4 | 192.168.19.76:3306 | RO/Slave | UP | 1 | 0 | 0 | server1 | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+--------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+---------+-------+---------+-------+---------+------+------+---------+
5.分别在三台mariadb服务器上抓包:
master:
tcpdump -i eth0 -nn -XX ip dst 192.168.19.66 and tcp dst port 3306
slave1:
tcpdump -i eth0 -nn -XX ip dst 192.168.19.74 and tcp dst port 3306
slave2:
tcpdump -i eth0 -nn -XX ip dst 192.168.19.76 and tcp dst port 3306
6.在oneproxy上进行数据库创建及读取:
mysql -utest -predhat -h192.168.19.79 //连接到oneproxy
数据库命令这里不再写出,大家可以从图片可以看出,所有写操作都在master上进行,读操作被负载均衡至slave上。
至此,实验成功,谢谢!
MySQL5.6 Replication主从复制(读写分离) 配置完整版