mysql oneproxy_利用oneproxy实现mysql读写分离搭建笔记

我不是女神ヾ 2022-10-25 01:22 134阅读 0赞

功能：

1.具有SQL白名单(防SQL注入)及IP白名单功能的SQL防火墙软件

2.数据库故障切换

3.读写分离

4.分库分表

一、下载

oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz

二、部署

master: 192.168.0.181 hostname:cen01

slave: 192.168.0.182 hostname:cen02

oneproxy: 192.168.0.183 hostname:cen03

系统：全部是centos6.7

需要读写分离的库名称：mydata，表test01 (id int(3),name char(8)); 可不用先建好

总结：

1.master与slave安装好MYSQL，已做好主从复制，oneproxy不用安装MYSQL

2.当外来业务访问数据库时，只需直接访问oneproxy机器的:3307端口就行了。它会直接调度master和slave节点。

mysql -uuser01 -puser01 -P3307 -h192.168.0.183

1.将软件包解压

tar -xzf oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz -C /usr/local

2.配置oneproxy

cd /usr/local/oneproxy

vim ./demo.sh

将ONEPROXY\_HOME的路径改为软件部署路径：/usr/local/oneproxy

如下图所示：

![5a394c001ef859df12322309f7dbe137.png][]

vim oneproxy.service

将ONEPROXY\_HOME的路径改为软件部署的路径：/usr/local/oneproxy

如下图所示：

![f1fc8c8bb6e21fa083b49ed6e1ac98f6.png][]

vim conf/proxy.conf

event-threads = 4     //并发线程数，最大允许48个线程。通常可以设为CPU Core数量的两倍

proxy-address = :3307   //设置对外提供访问的端口

mysql-version = 5.6.25   //设置mysql版本

proxy-slave-addresses.1 = 192.168.0.182:3306@server1      //192.168.0.182为slave的IP，server1为服务组名称，任意写

proxy-slave-addresses.2 = 192.168.0.184:3306@server1      //填写第二个slave的IP，如果有的话

proxy-master-addresses.1 = 192.168.0.181:3306@server1     //192.168.0.181为master的IP，server1为服务组名称，任意写

proxy-group-policy = server1:read\_slave     //设置策略为：在master写，在slave读

proxy-user-list = user01/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@mydata          //user01为用户名，用于给oneproxy登录后端MYSQL节点/后面的字符串是密码，mydata是要读写分离的数据库名称

repadmin-username = repadmin         //指定复制管理帐号的用户名，后端MYSQL节点都要建

repadmin-password = 1378F6CC3A8E8A43CA388193FBED5405982FBBD3          //指定复制帐号的密码，三台mysql都要有

保存退出

登录所有后端MYSQL(master/slave节点)，执行：

由于已经设置了主从复制，所以只对master添加用户即可，创建用户操作会同步到slave

create user user01;

grant all on mydata.\* to user01@'%' identified by 'user01';

create user 'repadmin'@'%' identified by 'user01';           //oneproxy可能通过该帐号在后端MYSQL节点执行：show master/slave status

grant replication slave on \*.\* to 'repadmin'@'%';       //以便用来取得复制的运行数据

grant replication client on \*.\* to 'repadmin'@'%';

flush privileges;

退出

三、修改系统环境(master/slave/onepxory节点)

echo 1 > /proc/sys/net/ipv4/tcp\_tw\_reuse

echo 1 > /proc/sys/net/ipv4/tcp\_tw\_recycle

ulimit -n 65535

echo "echo 1 > /proc/sys/net/ipv4/tcp\_tw\_reuse" >> /etc/profile

echo "echo 1 > /proc/sys/net/ipv4/tcp\_tw\_recycle" >> /etc/profile

echo "ulimit -n 65535" >> /etc/profile

iptables -I INPUT -m state --state NEW -p tcp --dport 3307 -j ACCEPT

iptables -I INPUT -m state --state NEW -p tcp --dport 4041 -j ACCEPT

iptables -I INPUT -m state --state NEW -p tcp --dport 8080 -j ACCEPT

service iptables save

四、启动oneproxy

cd /usr/local/oneproxy

chmod 755 demo.sh

./demosh

./oneproxy.service restart

![416dd58ae565011344bc639183d1064e.png][]

五、访问oneproxy管理界面

(oneproxy不必安装web服务)

![a0b6a3e4fb0f40b7551bb54327822c7b.png][]

六、读写分离检验

在master或slave或其它任意一台非集群的机器执行：

mysql -utest -ptest -h192.168.0.183 -P3307 -e"select @@hostname;"       //往oneproxy提交查询操作，显示的是slave主机名

![4390417a5e227a5aaaf0cc3495319d4d.png][]

mysql -utest -ptest -h192.168.0.183 -P3307 -e"begin;select @@hostname;commit;"      //往oneproxy提交写操作，显示的是master主机名

![64b81a22bb4420cb6016140d33343d97.png][]

可以进一步校验：

在master或slave或其它任意一台机，或者其它连接mysql的工具(Navicat)，执行如下命令：

mysql -uuser01 -puser01 -P3307 -h192.168.0.183 -e"insert into mydata.test01 values (5,'aaaaa')"

在master和slave的数据库中可以查询到以下结果：

![614cd675d3b344760cae16e291c14152.png][]

![fb02496d4af478826008d25c7bedfdfb.png][]

当执行：drop table test01时出现报错：

ERROR 1044 (42000): Access denied due to security policy, DDL disabled or DML restricted!

![db815efe28eab833c2899579001ec1cc.png][]

解释：由于OneProxy为了安全起见，默认是禁止DDL语句的。

解决办法：从OneProxy管理端口，运行如下命令：

mysql -uadmin -pOneProxy -P4041 -h192.168.0.183 -e"set gaccess server1 0;"      //0表示允许创建/删除表，1表示不允许创建/删除

此时就可以删除或者创建表了。

登录管理界面，可看到有相应记录：

![40b4521776139b95cefe046df6944327.png][]

![8729176ed61e903310e9d3372ffe5976.png][]

![9cb552023618279105d58a5efd9b7b27.png][]

可见，当往oneproxy写数据时，会自动同步到master上，而master通过主从复制功能将数据同步到slave。

检查后端数据库节点状态：

mysql -uadmin -pOneProxy -P4041 -h192.168.0.183 --protocol=TCP       //进入管理端口(默认端口为4041)，IP为部署了oneproxy的IP

list help;     //显示帮助

list backend;      //输入此内容，回车

![30d481af6255f7c66b70359df168f643.png][]

IS\_M：表示这台机器是否是一台Master

IS\_S：表示是否为一台Slave，

MFile：表示主库上的Binlog位置

DFile：表示备机上IO线程

RFile：SQL线程处理的位置

部署完成。

如有错误，可查看log/proxy.log日志。

[5a394c001ef859df12322309f7dbe137.png]: /images/20221024/d10a815a1a9144389beff77e7f667dad.png
[f1fc8c8bb6e21fa083b49ed6e1ac98f6.png]: /images/20221024/21ca7058b60c468cab002abb267e0b4a.png
[416dd58ae565011344bc639183d1064e.png]: /images/20221024/ee370a43f356474686fed41e46d68cb7.png
[a0b6a3e4fb0f40b7551bb54327822c7b.png]: /images/20221024/2ff2f727f7ab47b0a69316bb8f0d2de6.png
[4390417a5e227a5aaaf0cc3495319d4d.png]: /images/20221024/cdb1aa9d318b4e5d88ecd1850e75cd53.png
[64b81a22bb4420cb6016140d33343d97.png]: /images/20221024/9c309b0c66e247298962533cd4169cf4.png
[614cd675d3b344760cae16e291c14152.png]: /images/20221024/54d8e4a4bb5a4d808cff6288885c1969.png
[fb02496d4af478826008d25c7bedfdfb.png]: /images/20221024/7190289fbea145a884260ffbb7898dc4.png
[db815efe28eab833c2899579001ec1cc.png]: /images/20221024/41ec62ebcd6b42088e68175af35a0f09.png
[40b4521776139b95cefe046df6944327.png]: /images/20221024/9623022c220a4a7e8cec7aa6c6927fa2.png
[8729176ed61e903310e9d3372ffe5976.png]: /images/20221024/d906d43ee0f349939f9dabeca3769ed4.png
[9cb552023618279105d58a5efd9b7b27.png]: /images/20221024/d2b5cc4f49f041dda602b29b4e1be6e8.png
[30d481af6255f7c66b70359df168f643.png]: /images/20221024/67b1dc3d04b14cc7871854eba2a6a6cb.png