Mysql-oneproxy Read/write separation

Oneproxy Read/write separation

I. Oneproxy read and write separation

Primary server (Master): 192.168.32.150
Slave server (slave): 192.168.32.129
Oneproxy Middleware Server: 192.168.32.150

1. First Configure master-slave replication and create test libraries on the master server

Mysql>create database test;

mysql> show databases;

Add oneproxy connection user automatically replication to slave database

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

2. Shut down the firewall of the three servers or configure the firewall policy

mysql:3306

Oneproxy Connection Port 3307

Oneproxy Management Port 4041

[Email protected] ~]# Systemctl stop Firewalld.service

① Open Oneproxy automatically recognize read-write server (from server configuration)

[Email protected] ~]# VIM/ETC/MY.CNF

Added: Read_Only = 1

② Restart MySQL Service

[Email protected] ~]# systemctl restart mysqld

Mysql> Show variables like "%read_only%";

or Mysql>set global read_only = 1; Temporarily open

3. Configuring Oneproxy Middleware (Oneproxy server)

① installation 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/

② Modifying the Oneproxy directory in a pre-boot script

[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

③ Modifying the startup script Oneproxy directory

[[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"

④ get oneproxy Encrypted ciphertext password

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

[[email protected] bin]# ls

[Email protected] bin]#/mysqlpwd 123456

9d7e55eaf8912ccbf32069443fac452794f8941b

⑤ modifying Oneproxy Master Files

[[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: Automatically switch between read and write roles
Proxy-group-policy: Predefined policies that correspond to the real database to be managed
Proxy-user-list: Indicates that the connection database user ciphertext password to the real database user password corresponding to the @ character refers to the database

Start Oneproxy and view ports

[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. Verification

Since Oneproxy initially quiesce the DDL statement for security reasons, you need to go to the 4041 Admin port to open the underlying database permissions (from the server)

[Email protected] ~]# mysql-uadmin-poneproxy-p4041-h 192.168.32.149

Mysql> set gaccess test 0;

Connect port 3307 with Client login Oneproxy (primary server)

[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;

Observe the change of query in Admin Web page master and slave, increase of read load and increase of write load, verify success

Visit the Oneproxy Administration Web page
Format: Input http://192.168.32.149:8080
Default User name admin, password Oneproxy

Mysql-oneproxy Read/write separation