centos7搭建oneproxy实现mysql读写分离

post by rocdk890 / 2017-6-15 13:50 Thursday linux技术
发表评论
最近尝试了下使用oneproxy在centos7上实现mysql 5.7的读写分离,感觉这个软件功能是强大,可惜的是文档太少了,有问题的话只能找官方解决,网上搜解决问题的实在是太少了,今天给大家说下怎么在两台机器上用oneproxy实现mysql读写分离.
系统:centos 7.x(64位)
软件环境:server-jre-7u51-linux-x64.tar.gz
oneproxy-rhel5-linux64-v5.8.5-ga.tar.gz
mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
服务器ip:
oneproxy:192.168.12.254
master:192.168.12.254
slave:192.168.12.253

1.安装前准备
wget http://download.slogra.com/java/server-jre-7u51-linux-x64.tar.gz
wget http://www.onexsoft.cn/software/oneproxy-rhel6-linux64-v5.8.5-ga.tar.gz

2.安装jdk
tar zxf server-jre-7u51-linux-x64.tar.gz
mkdir /usr/java
mv jdk1.7.0_51 /usr/java/
ln -s jdk1.7.0_51/ latest
ln -s jdk1.7.0_51/ default
echo 'export JAVA_HOME=/usr/java/default' >> /etc/profile
echo 'export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar' >> /etc/profile
echo 'export PATH=$PATH:$JAVA_HOME/bin' >> /etc/profile
source /etc/profile

3.搭建mysql 5.7 gtid主从
大家可以使用我之前的shell脚本一键自动安装mysql 5.7来进行安装,自己改下GTID那部分和server_id,如下:
master:
server_id = 1

gtid_mode = on
enforce-gtid-consistency = true
master-info-repository=TABLE
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=localip

slave:
server_id = 10

gtid_mode = on
enforce-gtid-consistency = true
master-info-repository=TABLE
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=localip

剩下的怎么做就不用说了吧,什么?你不会,那么我简单说下设置主从同步的账号,去master记录file和position值,去slave服务器上配置slave与master通信,并启动slave查看状态,好了,这样应该清楚了吧,毕竟主从在这篇文章里不是重点,如果你还不会可以去看看我的mysql 5.5的主从操作步骤.

3.安装oneproxy并配置
tar zxf oneproxy-rhel6-linux64-v5.8.5-ga.tar.gz
mv oneproxy /usr/local/
cd /usr/local/oneproxy/
sed -i 's/data/usr\/local/g' demo.sh
sed -i 's/data/usr\/local/g' oneproxy.service
cp oneproxy.service /etc/init.d/oneproxy

创建个测试数据库:
create database emlog;
创建oneproxy登录账户:
GRANT ALL PRIVILEGES ON *.* TO 'amoeba'@'%' IDENTIFIED BY 'JLCBq4hXNp0S';

使用mysqlpwd对密码进行加密:
cd /usr/local/oneproxy/bin/
./mysqlpwd JLCBq4hXNp0S
88CD19F8ADF2A2A5CA51256ACE61AC46D0913486

cd /usr/local/oneproxy/conf/
vi proxy.conf
[oneproxy]
keepalive     = 1
event-threads = 4
log-file      = log/oneproxy.log
pid-file      = log/oneproxy.pid
lck-file      = log/oneproxy.lck
proxy-group-policy       = server1:read-slave
max-idle-conn		 = 500
min-idle-conn	         = 50

proxy-address            = :3307
proxy-socket-file        = /tmp/oneproxy.sock
mysql-version            = 5.7.17
proxy-master-addresses.1 = 192.168.12.254:3306@server1
proxy-slave-addresses.1  = 192.168.12.253:3306@server1

proxy-user-list          = amoeba/88CD19F8ADF2A2A5CA51256ACE61AC46D0913486@emlog

#proxy-memory-db         = test/test@127.0.0.1:3306:test

#proxy-part-template     = conf/template.txt
#proxy-part-tables.1     = conf/part.txt
#proxy-part-tables.2     = conf/part2.txt
#proxy-part-tables.3     = conf/cust1.txt
#proxy-part-tables.4     = conf/cobar.txt
proxy-charset            = utf8_general_ci

proxy-secure-client      = 127.0.0.1
#proxy-license            = A2FF461456A67F28,D2F6A5AD70C9042D
proxy-httpserver         = :8080
proxy-httptitle  = OneProxy Monitor

#repadmin-username = repadmin
#repadmin-password = B7E874C9971B394C2FFB8F65B9F22CA6B304855F
proxy-replication-check = 1
#proxy-auto-readonly	= 1
proxy-forward-clientip  = 1

ps:
proxy-auto-readonly=1这一行必须要注释掉,不然连接后不能在oneproxy终端上不能进行任何SQL操作

指定主服务器的地址，格式为IP:PORT@组名:
proxy-master-addresses.1 = 192.168.12.254:3306@server1
指定从服务器的地址:
proxy-slave-addresses.1  = 192.168.12.253:3306@server1
指定连接后端数据库的用户名，密码和数据库:
proxy-user-list          = amoeba/88CD19F8ADF2A2A5CA51256ACE61AC46D0913486@emlog
指定从服务器组的策略(这里指定为从服务器是读服务):
proxy-group-policy       = server1:read-slave

如果没有注释proxy-auto-readonly这一行,在执行SQL语句时会报Lost connection to MySQL server during query or no managed connection

启动oneproxy,并进入后台查看是否为UP:
mysql -u admin -P4041 -pOneProxy --protocol=TCP
list backend;
点击查看原图
可以看到oneproxy连接两台mysql正常,并且读写分离也配置好了.

测试读写分离的话,先把主从断开,分别在主从里插入一条数据,看oneproxy到底是读和写的那台mysql服务器,好了,教程就到这.
夜空- 本站版权
1、本站所有主题由该文章作者发表，该文章作者与夜空享有文章相关版权
2、其他单位或个人使用、转载或引用本文时必须同时征得该文章作者和夜空的同意
3、本帖部分内容转载自其它媒体，但并不代表本站赞同其观点和对其真实性负责
4、如本帖侵犯到任何版权问题，请立即告知本站，本站将及时予与删除并致以最深的歉意
5、原文链接：blog.slogra.com/post-701.html

标签: centos mysql centos7 oneproxy 读写 分离

评论:

 
Copyright © 2012- · slogra |蜀ICP备16018542号-1 |