centos7搭建oneproxy实现mysql读写分离
最近尝试了下使用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服务器,好了,教程就到这.
rocdk890
Michael
ade
西门堵车
朱祁镇
r1p
milton010
评论: