Mysql-Oneproxy讀寫分離

標籤：技術   ike   分享   blog   安裝   system   lis   目錄   ddr   

Oneproxy讀寫分離

一、Oneproxy讀寫分離

主伺服器（Master）：192.168.32.150
從伺服器（slave）：192.168.32.129
oneproxy中介軟體伺服器：192.168.32.150

1、首先要配置好主從複製並且在主伺服器上建立test庫

mysql>create database test;

mysql> show databases;

添加oneproxy的串連使用者會自動replication到slave資料庫

mysql>grant select,delete,insert,update,create on test.* to [email protected]‘%‘ identified by ‘123456‘;

2、關閉三台伺服器的防火牆或者配置防火牆策略

Mysql：3306

oneproxy串連連接埠3307

oneproxy管理連接埠4041

[[email protected] ~]# systemctl stop firewalld.service

①開啟oneproxy自動識別讀寫伺服器（從伺服器配置）

[[email protected] ~]# vim /etc/my.cnf

添加：read_only = 1

②重啟mysql服務

[[email protected] ~]# systemctl restart mysqld

mysql> show variables like "%read_only%";

或者 mysql>set global read_only = 1; //臨時開啟

3、配置oneproxy中介軟體（oneproxy伺服器）

①安裝oneproxy

[[email protected] ~]# wget http://www.onexsoft.com/software/oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz

[[email protected] ~]# tar zxf oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz -C /usr/local/

②在開機前指令碼中修改oneproxy目錄

[[email protected] ~]# vim /usr/local/oneproxy/demo.sh

~

#/bin/bash

export ONEPROXY_HOME=/usr/local/oneproxy

ulimit -c unlimited

valgrind --leak-check=full \

${ONEPROXY_HOME}/bin/oneproxy --defaults-file=${ONEPROXY_HOME}/conf/proxy.conf

③修改啟動指令碼oneproxy目錄

[[email protected] ~]#vim /usr/local/oneproxy/oneproxy.service

............................

OneProxy Settings

ONEPROXY_HOME=/usr/local/oneproxy

ONEPROXY_SBIN="${ONEPROXY_HOME}/bin/oneproxy"

ONEPROXY_CONF="${ONEPROXY_HOME}/conf/proxy.conf"

ONEPROXY_PID="${ONEPROXY_HOME}/log/oneproxy.pid"

RETVAL=0

prog="OneProxy"

④擷取oneproxy加密後的密文密碼

[[email protected] ~]# cd /usr/local/oneproxy/bin/

[[email protected] bin]# ls

[[email protected] bin]# ./mysqlpwd 123456

9D7E55EAF8912CCBF32069443FAC452794F8941B

⑤修改oneproxy主配檔案

[[email protected] ~]# mysql --version

[[email protected] bin]# vim /usr/local/oneproxy/conf/proxy.conf

[oneproxy]

#proxy-license = A2FF461456A67F28,D2F6A5AD70C9042D

keepalive = 1

event-threads = 4

proxy-group-policy = test:read-slave

log-file = log/oneproxy.log

pid-file = log/oneproxy.pid

lck-file = log/oneproxy.lck

proxy-auto-readonly = 1

proxy-forward-clientip = 1

proxy-trans-debug = 1

mysql-version = 5.7.21

proxy-master-addresses.1 = 192.168.32.150:[email protected]

proxy-slave-addresses.2 = 192.168.32.129:[email protected]

proxy-user-list = oneproxy/[email protected]

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-charset = utf8_bin

proxy-secure-client = 127.0.0.1

proxy-httpserver = :8080

proxy-httptitle = OneProxy Monitor

proxy-auto-readonly：自動切換讀寫角色
proxy-group-policy： 預定義策略，對應真實要管理的資料庫
proxy-user-list： 指明串連資料庫使用者密文密碼要與真實資料庫使用者密碼對應@符後指的是資料庫

啟動oneproxy並查看連接埠

[[email protected] bin]# cd ..

[[email protected] oneproxy]# chmod +x demo.sh

[[email protected] oneproxy]# ./demo.sh

[[email protected] oneproxy]# ./oneproxy.service start

[[email protected] oneproxy]# ss -anpt | grep 3307

[[email protected] oneproxy]# ss -anpt | grep 4041

5、驗證

由於oneproxy為了安全起見，初始靜止了DDl語句，需要去到4041管理連接埠開啟底層資料庫許可權（從伺服器）

[[email protected] ~]# mysql -uadmin -pOneProxy -P4041 -h 192.168.32.149

mysql> set gaccess test 0 ;

使用用戶端登入oneproxy串連連接埠3307（主伺服器）

[[email protected] ~]# mysql -uoneproxy -p123456 -h 192.168.32.149 -P3307

mysql> create table test.tb (id int);

mysql> insert into test.tb values(1);

mysql> select * from test.tb;

觀察管理網頁master和slave中query的變化,讀負載的增加與寫負載的增加，驗證成功

訪問oneproxy管理網頁
格式：輸入http://192.168.32.149:8080
預設使用者名admin，密碼OneProxy

Mysql-Oneproxy讀寫分離