Implement MySQL read-write separation and load balancing based on OneProxy

Introduction

Part 1: Written at the beginning

OneProxy is a distributed data access layer developed completely independently by the civilian software. It helps users quickly build distributed database middleware that supports sharding on MySQL/PostgreSQL clusters. It is also an SQL firewall software with SQL whitelist (anti-SQL injection) and IP whitelist functions. It uses the reverse protocol output mode that is consistent with MySQL Proxy, which is very simple, transparent and easy to use for applications, making complex solutions such as database failover, read/write split, and horizontal partitioning that users fear extremely simple and controllable! Based on the Libevent mechanism, a single instance can achieve 250,000 SQL forwarding capabilities. One OneProxy node can drive the entire MySQL cluster and contribute to business development.

Part 2: 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

Part 1: Install Oneproxy

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

Part 2: Building a master-slave environment

The architecture of this article is one master and two slaves, HE3 is the Master, and HE1 and HE2 are the Slaves. The construction of master-slave is not the focus of this article, you can move if you need it:

How to quickly use mysqlreplicate to build MySQL master-slave

Part 3: Configure Oneproxy

The demo in the directory is the initial startup script, oneproxy.service is the start and stop script, in the new version of oneproxy, the proxy.cnf in 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 change ONEPROXY_HOME in demo.sh to the directory where oneproxy is unzipped

[root@HE4 oneproxy]# cat oneproxy.service 
#!/bin/bash
# chkconfig: -30 21
# description: OneProxy service.
# Source Function Library
. /etc/init.d/functions
# OneProxy Settings
ONEPROXY_HOME=/root/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"
start() {
 echo -n $"Starting $prog ... "
 daemon $ONEPROXY_SBIN --defaults-file=$ONEPROXY_CONF
RETVAL=$?
echo
}
stop() {
 echo -n $"Stopping $prog ... "
if [ -e ${ONEPROXY_PID} ]; then
daemon kill -INT $(cat ${ONEPROXY_PID})
 RETVAL=$?
fi
echo
}
restart(){
 stop
sleep 1
 start
}
case "$1" in
 start)
 start
 ;;
 stop)
 stop
 ;;
 restart)
 restart
 ;;
 *)
 echo $"Usage: $0 {start|stop|restart}"
 RETVAL=1
esac
exit $RETVAL

At the same time, change 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]# ls

mysqlpwd oneproxy

[root@HE4 bin]# ./mysqlpwd MANAGER

1C6D087BA5D2607A27DECB2F2AFE247E911E877A

Edit the contents of proxy.cnf

[root@HE4 conf]# cat proxy.conf 
[oneproxy]
keepalive = 1
event-threads = 4
proxy-group-policy.2 = 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
proxy-address = :3307
mysql-version = 5.7.16
proxy-master-addresses.1 = 192.168.1.250:3306@test
proxy-slave-addresses.2 = 192.168.1.248:3306@test
proxy-slave-addresses.3 = 192.168.1.249:3306@test
proxy-user-list = sys_admin/1C6D087BA5D2607A27DECB2F2AFE247E911E877A@test
proxy-user-group = test:sys_admin/1C6D087BA5D2607A27DECB2F2AFE247E911E877A@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-charset = utf8_bin
proxy-secure-client = 127.0.0.1
# proxy-license = 32C54560E06EFF3E
proxy-httpserver = :8080
proxy-httptitle = OneProxy Monitor

Part 4: Start Oneproxy

[root@HE4 oneproxy]# ./demo.sh

[root@HE4 oneproxy]# ./oneproxy.service restart

Stopping OneProxy ... [ OK ]

Starting OneProxy ... [ OK ]

test

Part 1: Oneproxy status verification

Open the browser to open port 192.168.1.251:8080 to see the oneproxy management page

Here you can see various status information of the master and slave.

Part 2: Load balancing and read-write separation verification

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

It can be seen that there is no problem with HE1 and HE2 being used as load balancers, and there is no problem with HE3 being used as a write server.

--Summarize--

OneProxy cooperates with MySQL to realize read-write separation and load balancing experiment and is successfully built. Oneproxy also has the function of sharding databases and tables, which will be further studied in the future. Due to the author's limited level and the rush to write the article, there are inevitably some errors or inaccuracies in the article. I sincerely ask readers to criticize and correct any inappropriateness.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

Detailed explanation of the implementation steps of MySQL dual-machine hot standby and load balancing
Method of using MySQL system database for performance load diagnosis
How to implement load balancing in MySQL
How to use nginx as a load balancer for mysql
Build a stable and highly available cluster based on mysql+mycat, load balancing, master-slave replication, read-write separation operation
Python implements MySQL read-write separation and load balancing
Keepalived+HAProxy to implement MySQL high availability load balancing configuration
Analyze the CPU load surge caused by indexes in MySQL
How to quickly increase the load capacity of MYSQL database connections
Deployment and implementation of MySQL server cluster with load balancing function
Troubleshooting MySQL high CPU load issues

<<: A brief summary of all encapsulation methods in Vue

>>: Linux bridge method steps to bridge two VirtualBox virtual networks