[SequoiaDB] Tech | SequoiaDB SQL instance high available load balancing practice

1

Preface

 

In the application program, the IP address and port number of the database connected by the application configuration are fixed. When the server of the IP address is down, it needs to manually change the IP address to switch the database server. At the same time, when the application receives thousands of concurrent http requests, it will cause the server to consume a lot of system resources, reduce the response speed, and even cause downtime.

 

In order to make full and reasonable use of server resources and improve the performance and stability of data services, under the premise of low cost, ensure that the normal operation of the business will not be affected in the case of partial server downtime or failure. This paper mainly introduces the high availability scheme and practice of nginx + preserved connection to sequoiadb MySQL instance.

 

2

Introduction to SequoiaDB database

 

SequoiaDB Jushan database is a fully self-developed financial level distributed database product, which is composed of a database instance layer and a database storage engine layer with a computing and storage separation architecture. The database instance layer is responsible for parsing the request and forwarding it to the database storage engine layer for processing. At the same time, it will feed back the response results of the database storage engine layer to the application layer. The database instance layer supports MySQL instances, PostgreSQL instances, SparkSQL instances for structured data, and S3 and PosixFS for unstructured data The object storage instance of the file system, and the database storage engine layer is composed of the coordination node, catalog node and data node of SequoiaDB giant fir database. The database cluster architecture can facilitate users to realize seamless migration from traditional database to Jushan database, and reduce the development and learning costs of application developers.

 

2.1

 

Relationship between SequoiaDB MySQL instances

 

As shown in the figure is the logic architecture diagram of SequoiaDB database cluster. Users can see from the figure that the application program actually connects and accesses the database through the instance node of the computing layer. For example, as shown in the figure, the application can directly connect and access the SequoiaDB database cluster through MySQL's JDBC driver.

 

The storage layer of SequoiaDB database is a distributed storage cluster that can support distributed transactions and multi center disaster recovery.

 

The distributed storage layer of SequoiaDB includes three types of role nodes: coordination node, catalog node and database node. All nodes in the distributed storage layer support multi node deployment, distributed level expansion and high availability disaster recovery.

The connection between MySQL instance and distributed storage engine in the computing layer can achieve high availability and avoid single point of failure through the load balancing function of multiple coordination nodes. In traditional applications, when an application connects to MySQL through JDBC, it usually only fills in the IP address and port number of a server. In this architecture, in order to solve the single point of failure of MySQL connection, a layer of load balancing service will be built between the application server and the database server to realize the high availability switch between the application program and the database connection.

 

2.2

 

Traditional solutions

 

In the past, in order to solve the problem of high availability of database, load balancers (such as F5) were usually installed between the database server and the external network, so as to avoid business impact due to a server outage.

 

Although this kind of hardware load balancing technology is relatively mature and has been deployed in important systems of enterprises in the past, it needs to purchase additional expensive load balancing equipment, and the cost is relatively high.

 

3

Nginx + preserved solution

 

3.1

 

Overall framework

 

The idea of this solution is the same as that of the traditional solution. The only difference is that the load balancer is used in the traditional solution, and nginx + preserved is used in this solution to achieve load balancing. This solution adopts the way of pure software, realizes the database server of multiple machines to fix an IP address and service port uniformly, and the deployment way is flexible.

The specific deployment architecture is as shown in the figure below. For the MySQL and load instances of the three database servers of Nginx reverse proxy, port 3306 is mapped to port 3307, and the application server will connect the MySQL instances of the three database servers through the Nginx reverse proxy port 3307 of the preserved virtual IP address.

VRRP technology can virtualize two or more physical router devices into a virtual router, which provides external services through virtual IP (192.168.81.100).

 

Inside the virtual router, there is only one physical router providing external services at the same time. This physical router is called the Master. Generally speaking, the Master is generated through the election algorithm. It has the virtual IP for external services and provides various network functions. Other physical routers do not have external virtual IP, only receive the Master's VRRP status notification information. This part of router is called the backup router (Slave).

 

When the main router fails, the backup router is re elected, and a new router is generated to become the master. As shown in the figure below, when the main router (Master) goes down, the backup router (Slave) elects to be the master, then takes over the virtual IP, and continues to provide external services. For the application server, the whole switching process is transparent to the application without any impact.

 

3.2

 

Nginx product introduction

 

Nginx (engine x) is a high-performance HTTP and reverse proxy web server, and also provides IMAP/POP3/SMTP services. Nginx is a lightweight web server / reverse proxy server and email (IMAP/POP3) proxy server, which is distributed under BSD like protocol. In fact, the concurrency of nginx is better in the same type of web server.

 

Nginx has a strong proxy function, but there is still a single point of failure in the deployment of nginx services on only one server. Therefore, in the high availability load balancing solution, nginx needs to solve the single point of failure with the preserved software.

 

Nginx + preserved dual computers realize the high availability of nginx reverse proxy service to ensure that any server downtime, or any nginx service is unexpectedly stopped, will not affect the normal operation of the business.

 

3.3

 

Keepalived product introduction

 

The purpose of the preserved software is to detect the state of the server.

 

In the software deployed with Keepalived service, if a server goes down unexpectedly or fails to work, Keepalived software will detect the problem server and remove the failed server from the system.

 

At the same time, the preserved software will automatically use other servers to replace the problem server. When the problem server works normally again, the maintained software will automatically add the original problem server to the server list. The whole high availability switching work is automatically sensed and switched by Keepalived software without any manual interference.

 

4

Nginx + preserved installation and deployment

 

Assuming that the following server environment exists, the following content will introduce how to realize the highly available deployment of MySQL instance of SequoiaDB database through the maintained + nginx software:

 

4.1

 

Pre deployment environment check

 

Check if selinux status is disabled

 

sestatus

 

Open the / etc/selinux/config file with root permission

 

vi /etc/selinux/config

 

Adjust SELINUX to disabled

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:#     enforcing - SELinux security policy is enforced.#     permissive - SELinux prints warnings instead of enforcing.#     disabled - No SELinux policy is loaded.SELINUX=disabled# SELINUXTYPE= can take one of three two values:#     targeted - Targeted processes are protected,#     minimum - Modification of targeted policy. Only selected processes are protected. #     mls - Multi Level Security protection.SELINUXTYPE=targeted 

 

Restart the operating system for SELINUX settings to take effect

reboot

 

Install dependent plug-ins

yum install -y gcc pcre pcre-devel openssl openssl-devel gd gd-devel net-snmp-agent-libs

 

4.2

Nginx installation and deployment

 

4.2.1 Nginx installation

 

Download the Nginx installation package with the following command

wget https://nginx.org/download/nginx-1.16.0.tar.gz

 

Unzip and install

tar -zxvf nginx-1.16.0.tar.gz

cd nginx-1.16.0./configure --prefix=/usr/local/nginx --with-streammake && make install

 

4.2.2 Nginx deployment

 

Open Nginx configuration file

vi /usr/local/nginx/conf/nginx.conf

 

Modify the nginx configuration file and add the following content. The configuration of stream segment should be in the same directory as http segment. The new content indicates that the 3306 services of 134, 135 and 136 servers are added to the load balance of Nginx, and Nginx publishes the service ports of 3307 to the public.

stream {    upstream sequoiadb-mysql {        server 192.168.81.134:3306 weight=1;        server 192.168.81.135:3306 weight=1;        server 192.168.81.136:3306 weight=1;        }
    server {        listen 3307;        proxy_connect_timeout 5s;        proxy_timeout 300s;        proxy_pass sequoiadb-mysql;    }}

 

Add global command

ln -s /usr/local/nginx/sbin/nginx /usr/bin/nginx

 

Test installation

nginx -V

 

Expected return results

nginx version: nginx/1.16.0built by gcc 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) configure arguments: --prefix=/usr/local/nginx

 

New Nginx service script

vi /etc/init.d/nginx

 

Add the following

#!/bin/sh# nginx - this script starts and stops the nginx daemin## chkconfig:   - 85 15# description:  Nginx is an HTTP(S) server, HTTP(S) reverse \#               proxy and IMAP/POP3 proxy server# processname: nginx# config:      /usr/local/nginx/conf/nginx.conf# pidfile:     /usr/local/nginx/logs/nginx.pid# Source function library.. /etc/rc.d/init.d/functions# Source networking configuration.. /etc/sysconfig/network# Check that networking is up.[ "$NETWORKING" = "no" ] && exit 0nginx="/usr/local/nginx/sbin/nginx"prog=$(basename $nginx)NGINX_CONF_FILE="/usr/local/nginx/conf/nginx.conf"lockfile=/var/lock/subsys/nginxstart() {    [ -x $nginx ] || exit 5    [ -f $NGINX_CONF_FILE ] || exit 6    echo -n $"Starting $prog: "    daemon $nginx -c $NGINX_CONF_FILE    retval=$?    echo    [ $retval -eq 0 ] && touch $lockfile    return $retval}
stop() {    echo -n $"Stopping $prog: "    killproc $prog -QUIT    retval=$?    echo    [ $retval -eq 0 ] && rm -f $lockfile    return $retval} restart() {    configtest || return $?    stop    start}
reload() {    configtest || return $?    echo -n $"Reloading $prog: "    killproc $nginx -HUP    RETVAL=$?    echo}
force_reload() {    restart} configtest() {  $nginx -t -c $NGINX_CONF_FILE} rh_status() {    status $prog} rh_status_q() {    rh_status >/dev/null 2>&1}
case "$1" in    start)        rh_status_q && exit 0        $1        ;;    stop)        rh_status_q || exit 0        $1        ;;    restart|configtest)        $1        ;;    reload)        rh_status_q || exit 7        $1        ;;    force-reload)        force_reload        ;;    status)        rh_status        ;;    condrestart|try-restart)        rh_status_q || exit 0            ;;    *)        echo $"Usage: $0 {start|stop|status|restart|condrestart|try-restart|reload|force-reload|configtest}"        exit 2esac

 

Grant nginx service script permission

chmod 755 /etc/init.d/nginx

 

Add Nginx to system service

chkconfig --add nginx

 

Start Nginx service

systemctl start nginx.service

 

Set Nginx to power on and start automatically

 

systemctl enable nginx.service

 

Check Nginx service

 

systemctl status nginx.service

 

Expected return results

 

● nginx.service - SYSV: Nginx is an HTTP(S) server, HTTP(S) reverse proxy and IMAP/POP3 proxy server   Loaded: loaded (/etc/rc.d/init.d/nginx; bad; vendor preset: disabled)   Active: active (running) since Fri 2020-01-17 01:03:12 PST; 36s ago     Docs: man:systemd-sysv-generator(8) Main PID: 407 (nginx)   CGroup: /system.slice/nginx.service           ├─407 nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf           └─409 nginx: worker process
Jan 17 01:03:12 sdb2 systemd[1]: Starting SYSV: Nginx is an HTTP(S) server, HTTP(S) reverse proxy and IMAP/POP3 proxy server...Jan 17 01:03:12 sdb2 nginx[394]: Starting nginx: [  OK  ]Jan 17 01:03:12 sdb2 systemd[1]: PID file /usr/local/nginx/logs/nginx.pid not readable (yet?) after start.Jan 17 01:03:12 sdb2 systemd[1]: Started SYSV: Nginx is an HTTP(S) server, HTTP(S) reverse proxy and IMAP/POP3 proxy server.

 

Log in to MySQL instance through Nginx

mysql -h 192.168.81.134 -uroot -P 3307 -proot

 

Expected return results

mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.25 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

 

4.3

 

Keepalived installation and deployment

 

 

4.3.1 preserved installation

 

The user can download the preserved installation package through the following command

wget https://www.keepalived.org/software/keepalived-2.0.19.tar.gz

 

Unzip and install

tar -zxvf keepalived-2.0.19.tar.gz

cd keepalived-2.0.19./configure --prefix=/usr/local/keepalivedmake && make install

 

4.3.2 maintained deployment

 

Add global command

ln -s /usr/local/keepalived/sbin/keepalived /usr/bin/keepalived

 

Test installation

keepalived -v

 

Expected return results

Keepalived v2.0.19 (10/19,2019)

Copyright(C) 2001-2019 Alexandre Cassen, <acassen@gmail.com>Built with kernel headers for Linux 3.10.0Running on Linux 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017configure options: --prefix=/usr/local/keepalivedConfig options:  LVS VRRP VRRP_AUTH OLD_CHKSUM_COMPAT FIB_ROUTINGSystem options:  PIPE2 SIGNALFD INOTIFY_INIT1 VSYSLOG EPOLL_CREATE1 IPV6_ADVANCED_API RTA_ENCAP RTA_EXPIRES FRA_TUN_ID RTAX_CC_ALGO RTAX_QUICKACK FRA_OIFNAME IFA_FLAGS IP_MULTICAST_ALL NET_LINUX_IF_H_COLLISION LIBIPTC_LINUX_NET_IF_H_COLLISION VRRP_VMAC IFLA_LINK_NETNSID CN_PROC SOCK_NONBLOCK SOCK_CLOEXEC O_PATH GLOB_BRACE INET6_ADDR_GEN_MODE SO_MARK SCHED_RT SCHED_RESET_ON_FORK

 

New Nginx check script

vi /usr/local/keepalived/nginx_check.sh

 

Add the following

#!/bin/bashA=`ps -C nginx --no-header |wc -l`if [ $A -eq 0 ];then     /usr/sbin/nginx    sleep 2    if [ `ps -C nginx --no-header |wc -l` -eq 0 ];then        killall keepalived    fifi

 

Give check script executable permission

chmod +x /usr/local/keepalived/nginx_check.sh

 

Open the preserved profile

vi /usr/local/keepalived/etc/keepalived/keepalived.conf

 

Modify the configuration file of the primary kept

! Configuration File for keepalived
global_defs {   router_id sdb1}
vrrp_script chk_nginx {     script "/usr/local/keepalived/nginx_check.sh"     interval 3     weight -20 }
vrrp_instance VI_1 {    state MASTER    interface ens33    virtual_router_id 66    priority 100    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    track_script {        chk_nginx    }    virtual_ipaddress {        192.168.81.100    }}

 

Modify the configuration file of the standby kept

! Configuration File for keepalived
global_defs {   router_id sdb2}
vrrp_script chk_nginx {     script "/usr/local/keepalived/nginx_check.sh"     interval 3     weight -20 }
vrrp_instance VI_1 {    state BACKUP    interface ens33    virtual_router_id 66    priority 90    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    track_script {        chk_nginx    }    virtual_ipaddress {        192.168.81.100    }}

 

Modify the configuration file path VI / usr / local / preserved / etc / sysconfig / preserved

KEEPALIVED_OPTIONS="-D -f /usr/local/keepalived/etc/keepalived/keepalived.conf"

 

Start the preserved service

systemctl start keepalived.service

 

Set Keepalived to power on and start automatically

systemctl enable keepalived.service

 

Check the maintained service

systemctl status keepalived.service

 

Expected return results

keepalived.service - LVS and VRRP High Availability Monitor   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)   Active: active (running) since Fri 2020-01-17 07:52:10 PST; 1min 45s ago  Process: 76416 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS) Main PID: 76417 (keepalived)   CGroup: /system.slice/keepalived.service           ├─76417 /usr/local/keepalived/sbin/keepalived -D -f /usr/local/keepalived/etc/keepalived/keepalived.conf           └─76418 /usr/local/keepalived/sbin/keepalived -D -f /usr/local/keepalived/etc/keepalived/keepalived.conf

 

Check the virtual IP address of Master and Backup

ip addr show ens33

 

The expected return result of Master partially intercepts inet 192.168.81.134/24 brd 192.168.81.255 scope global ens33

       valid_lft forever preferred_lft foreverinet 192.168.81.100/32 scope global ens33       valid_lft forever preferred_lft forever

 

Backup expected return result, partial interception

inet 192.168.81.135/24 brd 192.168.81.255 scope global ens33       valid_lft forever preferred_lft forever

 

Log in MySQL instance through virtual IP address

mysql -h 192.168.81.100 -uroot -P 3307 -proot

 

Expected return results

mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.25 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

 

4.4

Keep alive + nginx for load balancing and high availability

 

The user connects to MySQL database through Navicat, IP uses the virtual IP address provided by Keepalived, and the port of MySQL instance is the 3307 service port provided by Nginx.

Because the preserved deployed by sdb1 server is Master, Navicat is connected to this machine. You can confirm it by executing netstat command

[root@sdb1 ~]# netstat -nap|grep 3307tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      1393/nginx: master  tcp        0      0 192.168.81.100:3307     192.168.81.1:53385      ESTABLISHED 1395/nginx: worker  tcp        0      0 192.168.81.100:3307     192.168.81.1:53380      ESTABLISHED 1395/nginx: worker  

 

If the sdb1 server is shut down actively

shutdown -h now

 

Then continue to check the virtual IP address of sdb2

ip addr show ens33

 

Expected return result, partial truncation

inet 192.168.81.135/24 brd 192.168.81.255 scope global ens33       valid_lft forever preferred_lft foreverinet 192.168.81.100/32 scope global ens33       valid_lft forever preferred_lft forever

 

If the MySQL service of sdb2 server is shut down actively at this time

 

service sequoiasql-mysql stop

 

Continue to query the data of a table in Navicat, and the query is successful.

 

81.136:3306     192.168.81.135:49646    ESTABLISHED 1388/mysqld         unix  2      [ ACC ]     STREAM     LISTENING     22202    1388/mysqld          /opt/sequoiasql/mysql/database/3306/mysqld.sock

 

Through the above verification steps, it can be proved that the Keepalived software can realize the automatic drift of virtual IP address among multiple servers, and the Nginx service can automatically realize the load balance of MySQL instance. Users have used Keepalived software and Nginx services, and finally realized the load balancing and high availability function of multiple MySQL instances of SequoiaDB database cluster under the condition of zero impact on the application.

 

5

summary

This paper introduces how to realize the high availability of database instances through nginx + preserved. Users can access multiple MySQL instances through one IP address and port number through the nginx + preserved solution to ensure that the application can also access the SequoiaDB database based on the same IP address when any machine is down or MySQL service is stopped

Tags: Database Nginx MySQL SELinux

Posted on Tue, 24 Mar 2020 09:00:20 -0700 by vomitbomb