Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to implement MySQL read-write separation and load balancing on the basis of OneProxy. The editor thinks it is very practical, so I hope you can get something after reading this article.

Brief introduction

Part1: write at the front

The distributed data access layer developed by OneProxy civilian software helps users quickly build distributed database middleware that supports sub-database and sub-table on MySQL/PostgreSQL cluster. It is also a SQL firewall software with SQL whitelist (anti-SQL injection) and IP whitelist functions. Using the reverse protocol output mode consistent with MySQL Proxy, it is very simple, transparent and easy to use for applications, and makes complex schemes such as database failover (Failover), read-write separation (Read/Write Split), sub-database sub-table (Horizontal Partitioning) that users fear become extremely simple and controllable! Based on Libevent mechanism, a single instance can achieve 250000 SQL forwarding capacity, and a single OneProxy node can drive the entire MySQL cluster and contribute to business development.

Part2: introduction to the environment

HE1:192.168.1.248 slave1

HE2:192.168.1.249 slave2

HE3:192.168.1.250 Master

HE4:192.168.1.251 Oneproxy

Environment construction

Part1: installing Oneproxy

[root@HE4 ~] # tar xvf oneproxy-rhel5-linux64-v5.8.5-ga.tar.gz [root@HE4 oneproxy] # lsbin conf demo.sh log oneproxy.service README testadmin.sql testautocommit.sql testproxy.sql trantest.sql

Part2: building a master-slave environment

The architecture of this paper is one master and two slaves, and HE3 is used as Master,HE1,HE2 as Slave. The construction of master and slave is not the focus of this article, but needs to be moved:

How to quickly use mysqlreplicate to build MySQL Master and Slave

Part3: configuring Oneproxy

The demo in the directory is the initial startup script, and oneproxy.service is the start-stop script. In the new version of oneproxy, the proxy.cnf of the conf folder is the configuration file.

[root@HE4 oneproxy] # cat demo.sh # / bin/bash#export ONEPROXY_HOME=/root/oneproxy# valgrind-- leak-check=full\ ${ONEPROXY_HOME} / bin/oneproxy-- defaults-file=$ {ONEPROXY_HOME} / conf/proxy.conf

We changed the ONEPROXY_HOME in demo.sh to the directory where we extracted the oneproxy.

[root@HE4 oneproxy] # cat oneproxy.service #! / bin/bash# chkconfig:-30 2 hours description: OneProxy service.# Source Function Library. / etc/init.d/functions# OneProxy SettingsONEPROXY_HOME=/root/oneproxyONEPROXY_SBIN= "${ONEPROXY_HOME} / bin/oneproxy" ONEPROXY_CONF= "${ONEPROXY_HOME} / conf/proxy.conf" ONEPROXY_PID= "${ONEPROXY_HOME} / log/oneproxy.pid" RETVAL=0prog= "OneProxy" start () {echo-n $"Starting $prog..." Daemon $ONEPROXY_SBIN-- defaults-file=$ONEPROXY_CONFRETVAL=$?echo} stop () {echo-n $"Stopping $prog..." if [- e ${ONEPROXY_PID}]; thendaemon kill-INT $(cat ${ONEPROXY_PID}) RETVAL=$?fiecho} restart () {stopsleep 1 start} case "$1" in start) start;; stop) stop; restart) restart;; *) echo $"Usage: $0 {start | stop | restart}" RETVAL=1esacexit $RETVAL

At the same time, change the ONEPROXY_HOME in oneproxy.service to the directory when decompressing.

Enter the bin directory in oneproxy and use mysqlpwd to encrypt the password

[root@HE4 oneproxy] # cd bin/ [root@HE4 bin] # lsmysqlpwd oneproxy [root@HE4 bin] #. / mysqlpwd MANAGER1C6D087BA5D2607A27DECB2F2AFE247E911E877A

Edit the content in proxy.cnf

[root@HE4 conf] # cat proxy.conf [oneproxy] keepalive = 1event-threads = 4proxy-group-policy.2 = test:read-slavelog-file = log/oneproxy.logpid-file = log/oneproxy.pidlck-file = log/oneproxy.lckproxy-auto-readonly = 1proxy-forward-clientip = 1proxy-trans-debug = 1proxy-address =: 3307mysql-version = 5.7.16proxy-master-addresses.1 = 192.168.1.250:3306@testproxy-slave-addresses.2 = 192.168.1.248 : 3306@testproxy-slave-addresses.3 = 192.168.1.249:3306@testproxy-user-list = sys_admin/1C6D087BA5D2607A27DECB2F2AFE247E911E877A@testproxy-user-group = test:sys_admin/1C6D087BA5D2607A27DECB2F2AFE247E911E877A@testproxy-part-template = conf/template.txtproxy-part-tables.1 = conf/part.txtproxy-part-tables.2 = conf/part2.txtproxy-part-tables.3 = conf/cust1.txtproxy-charset = utf8_binproxy-secure-client = 127.0.0.The proxy-license = 32C54560E06EFF3Eproxy-httpserver =: 8080proxy-httptitle = OneProxy Monitor

Part4: start Oneproxy

[root@HE4 oneproxy] #. / demo.sh [root@HE4 oneproxy] #. / oneproxy.service restartStopping OneProxy... [OK] Starting OneProxy... [OK]

test

Part1:Oneproxy status check

When the browser opens port 192.168.1.251 8080, you can see the management page of oneproxy.

You can see all kinds of status information of the master and slave here.

Part2: load balancing and read-write separation verification

[root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e "select @ @ hostname;" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE1 | +-+ [root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e "select @ @ hostname" "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE2 | +-+ [root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e" select @ @ hostname "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE1 | +-+ [root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e" select @ @ hostname "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE2 | +-+ [root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e" select @ @ hostname "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE1 | +-+ [root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e" begin;select @ @ hostname;commit "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE3 | +-+ [root@HE1 ~] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307-e" begin;select @ @ hostname;commit "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | HE3 | +-+

You can see that there is no problem with HE1,HE2 and slave as load balancer and HE3 as write server.

This is the end of the article on "how to implement MySQL read-write separation and load balancing on the basis of OneProxy". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.