编程知识 cdmana.com

Glacier, can you talk about how to realize the unlimited expansion of MySQL data storage?

In the high concurrency scenario, the author has developed it , Simple to offer 、 Stable 、 Extensible delayed message queuing framework , It has precise timing task and delay queue processing function . Since the open source for more than half a year , It has successfully provided precise timing scheduling scheme for more than ten small and medium-sized enterprises , It has withstood the test of production environment . In order to benefit more children's shoes , Now give the open source framework address :

https://github.com/sunshinelyz/mykit-delay

PS: Welcome to Star Source code , It's fine too pr Your blazing code .

Write it at the front

With the rapid development of Internet , More and more data has been deposited in enterprises , This requires more and more scalability of data storage layer . In today's Internet companies , Most businesses use MySQL To store relational data . How to achieve MySQL The high scalability of data storage layer has become a problem that Internet enterprises must solve . that , How to realize the real meaning MySQL Unlimited expansion ? today , Glacier will tell you how to realize it from a practical point of view MySQL Unlimited expansion of database . The article has been included in :https://github.com/sunshinelyz/technology-binghe and https://gitee.com/binghe001/technology-binghe, Friends, don't forget to give a little star ~~

summary

This article is in 《 How to guarantee under the massive data structure Mycat High availability ?》 On the basis of this paper, we further expand , So as to realize the high availability of each link of data storage layer , So as to achieve MySQL Unlimited expansion of .

The problem to be solved

stay 《 How to guarantee under the massive data structure Mycat High availability ?》 In the article , Our architecture is as follows :

As can be seen from the figure above ,HAProxy There is a single point of danger , Once this HAProxy The service outage , Then the entire service architecture will not be available . that , How to solve HAProxy What about the single point hidden trouble ? This is what this post is about .

Software version

  • operating system :CentOS-6.8-x86_64
  • JDK edition :jdk1.8
  • HAProxy edition :haproxy-1.5.19.tar.gz
  • Mycat edition :Mycat-server-1.6( Download the source code and compile it by yourself )
  • keepalived edition :keepalived-1.2.18.tar.gz
  • MySQL edition :mysql-5.7.tar.gz

The deployment plan

High availability load balancing cluster deployment architecture

The above diagram simplifies the architectural details of the data storage part . for example , For every part of the architecture , We can all expand on our own , To provide services independently in clusters , There will be no single point of failure .

Illustration :

(1) HAProxy Realized Mycat Multi node cluster high availability and load balancing , and HAProxy Its high availability can be achieved through Keepalived To achieve . therefore , HAProxy The host computer should be installed with HAProxy and Keepalived, Keepalived Responsible for preempting the server vip( fictitious ip, In the picture 192.168.209.130), Seize vip after , Access to the host can be through the original ip(192.168.209.135) visit , It can also be directly passed through vip(192.168.209.130) visit .

(2) Keepalived preemption vip Priority level , stay keepalived.conf The configuration of the priority Attribute decision . But generally, which host is Keepalived If the service starts first, it will seize vip, Even if it's slave, As long as you start first, you can also grab ( Be careful not to Keepalived The problem of resource preemption of ).

(3) HAProxy Responsibility will be for vip The request for is distributed to Mycat On the cluster node , Play the role of load balancing . meanwhile HAProxy It can also be detected Mycat Survival , HAProxy It will only forward requests to live Mycat On .

(4) If Keepalived+HAProxy One server in the high availability cluster is down , On another server in the cluster Keepalived It's going to grab vip And take over the services , At this time, they snatched vip Of HAProxy Nodes can continue to provide services .

(5) If one Mycat Server down , HAPorxy When forwarding a request, it will not be forwarded to the down time Mycat On , therefore Mycat Still available .

Sum up :Mycat The high availability and load balancing of the HAProxy To achieve , and HAProxy High availability , from Keepalived To achieve .

HAProxy node 2 Deployment of

HAProxy host 2(liuyazhuang136, 192.168.209.136) Please refer to the post for installation and deployment of 《 How to guarantee under the massive data structure Mycat High availability ?》, Pay attention to the configuration file adjustment : When deploying multiple nodes haproxy.cfg In the configuration file node 、 description The value of the configuration should be adjusted accordingly .

HAProxy host 2(liuyazhuang136, 192.168.209.136) Upper HAProxy The configuration is as follows :

## global The parameters in the configuration are process level parameters , It's usually about the operating system it's running on 

global

log 127.0.0.1 local0 info ##  Defining the global syslog The server , At most, you can define 2 individual 

### local0 It's a log device , Corresponding to /etc/rsyslog.conf Configuration in , Default recycle info Log level of 

#log 127.0.0.1 local1 info

chroot /usr/share/haproxy ##  modify HAProxy To the specified directory and execute before relinquishing permissions 

### chroot()  operation , Can improve  haproxy  Security level of 

group haproxy ##  Same as gid, But here is the specified user group name 

user haproxy ##  Same as uid, But this is the username 

daemon ##  Set up haproxy Run in the form of background daemons 

nbproc 1 ##  Specify the enabled haproxy Several processes ,

###  Only used in daemon mode haproxy; Start by default 1 A process ,

###  In general, multi process mode is only used in fields where a single process can open only a few file descriptors 

maxconn 4096 ##  Set each haproxy The maximum number of concurrent connections a process can accept ,

###  This is equivalent to a command-line option "-n","ulimit-n" The result of automatic calculation is formally referred to from the parameter setting 

# pidfile /var/run/haproxy.pid ##  Process documents ( The default path  /var/run/haproxy.pid)

node liuyazhuang136 ##  Defines the name of the current node , be used for HA Many in the scene haproxy Process sharing IP Address time 

description liuyazhuang136 ##  Description information for the current instance 

## defaults: Used to provide default parameters for all other configuration segments , This default configuration parameter can be set by the next "defaults" To reset 

defaults

log global ##  Inherit global in log The definition of 

mode http ## mode: The pattern being processed  (tcp: four layers  , http: Seven layers  , health: Status check , Will only return OK)

### tcp:  The instance runs on pure tcp Pattern , A full duplex connection will be established between the client and server sides ,

####  And not to 7 Layer messages do any type of checking , This is the default mode 

### http: The instance runs on http Pattern , Client requests are analyzed in depth before being forwarded to the back-end server ,

####  All is not with RFC Pattern compatible requests are rejected 

### health: The instance runs on health Pattern , It only responds to inbound requests “OK” Message and close the connection ,

####  No log information is recorded  , This request is used to monitor the corresponding state of the component 

option httplog

retries 3

option redispatch ## serverId The corresponding server is down , Force a redirect to another healthy server 

maxconn 2000 ##  The maximum number of concurrent connections to the front end ( The default is 2000)

###  It can't be used for backend section , For large sites , You can increase this value as much as possible so that haproxy Managing connection queues ,

###  In order to avoid being unable to respond to user requests . Of course , This maximum value cannot exceed “global” The definition in paragraph .

###  Besides , What needs to be noted is ,haproxy Two buffers are maintained for each connection , The size of each cache is 8KB,

###  Plus other data , Each connection will take up approximately 17KB Of RAM Space , This means that after proper optimization  ,

###  with 1GB Of the available RAM Space will be maintained when 40000-50000 Concurrent connections .

###  If an excessive value is specified , In extreme situations , It may eventually take up more space than the available memory of the current host ,

###  This can lead to unexpected results , therefore , Set it to an acceptable value as wise absolute , His tacit view is that 2000

timeout connect 5000ms ##  Connection timeout ( The default is milliseconds , Units can be set us,ms,s,m,h,d)

timeout client 50000ms ##  Client timeout 

timeout server 50000ms ##  Server timeout 

## HAProxy Status information statistics page of 

listen admin_stats

bind :48800 ##  Binding port 

stats uri /admin-status ## Statistics page 

stats auth admin:admin ##  Set the user and password of Statistics page authentication , If you want to set more than one , Write on another line 

mode http

option httplog ##  Enable logging HTTP request 

## listen:  Used to define by association “ front end ” and “ Back end ” A complete agent , Usually only to TCP Traffic is useful 

listen mycat_servers

bind :3307 ##  Binding port 

mode tcp

option tcplog ##  Record TCP Request log 

option tcpka ##  Is it allowed to go to server and client send out keepalive

option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www ##  Back end service state detection 

###  Back end server 48700 port ( The port value is passed through on the back-end server xinetd To configure ) send out  OPTIONS  request 

### ( Please refer to HTTP agreement ) ,HAProxy It will judge whether the back-end service is available according to the returned content .

### 2xx  and  3xx  The response code of the system indicates the state of health , Other response codes or no response indicate server failure .

balance roundrobin ##  Define a load balancing algorithm , Can be used for "defaults"、"listen" and "backend" in , The default is polling 

server mycat_01 192.168.209.133:8066 check port 48700 inter 2000ms rise 2 fall 3 weight 10

server mycat_02 192.168.209.134:8066 check port 48700 inter 2000ms rise 2 fall 3 weight 10

##  Format :server <name> <address>[:[port]] [param*]

### serser  Declare a server, It can only be used for listen and backend section .

### <name> The internal name specified for this server , It will appear in logs and warning messages 

### <address> Of this server IPv4 Address , Resolvable hostnames are also supported , But to start, you need to resolve the host name to the responding IPV4 Address 

### [:[port]] Specify the target port to send client connection requests to this server , This is optional 

### [param*] So server Set a series of parameters , All are optional , There are many parameters , The following are just a few commonly used parameters :

#### weight: The weight , The default is 1, The maximum value is 256,0 Means not participating in load balancing 

#### backup: Set as standby server , Only in other load balancing scenarios server None of these can be enabled server

#### check: To start this server Perform monitoring status checks , It can be set more precisely with the help of additional parameters 

#### inter: Set the time interval for monitoring status check , The unit is millisecond , The default is 2000,

#####  You can also use fastinter and downinter To optimize this event delay based on server-side themes 

#### rise: Set up server The number of times it needs to be checked to change from offline state to normal state ( Without setting , The default value is 2)

#### fall: Set up server The number of times it needs to be checked to switch from normal state to offline state ( Without setting , The default value is 3)

#### cookie: For a given server Set up cookie value , The value specified here will be checked when the request is inbound ,

#####  Selected for this value the first time server Will be selected by subsequent requests , Its purpose is to achieve the functionality of persistent connections 

#### maxconn: Specifies the maximum number of concurrent connections that this server can accept , If the number of connections to this server is higher than the value specified here ,

##### It will be placed on the request queue , To wait for other connections to be released 

HAProxy node 1 Status information page of :http://192.168.209.135:48800/admin-status

HAProxy node 2 Status information page of :http://192.168.209.136:48800/admin-status

Keepalived Introduce

Official website :http://www.keepalived.org/

Keepalived Is a high performance server high availability or hot standby solution , Keepalived Can be used to prevent the occurrence of server single point of failure , Through cooperation Haproxy Can achieve web High availability of front end services .Keepalived With VRRP Protocol is the basis of implementation , use VRRP Protocol to achieve high availability (HA).VRRP(Virtual Router Redundancy Protocol) The protocol is used to implement router redundancy , VRRP The protocol virtualizes two or more router devices into one device , External virtual router IP( One or more ), And inside the router group , If you actually own this IP If the router works properly, it will be MASTER, Or by algorithm .MASTER Implementation for virtual router IP Various network functions of , Such as ARP request , ICMP, And data forwarding, etc ; Other devices do not own the virtual IP, Status is BACKUP, In addition to receiving MASTER Of VRRP Out of status announcement information , Do not perform external network functions . When the main engine fails , BACKUP Will take over the original MASTER The network function of .VRRP The protocol uses multicast data to transmit VRRP data , VRRP Data uses special virtual sources MAC Address sends data instead of its own network card MAC Address , VRRP At runtime, only MASTER The router sends VRRP Announcement information , Express MASTER Working well and virtual router IP( Group ), BACKUP Only receive VRRP data , Don't send data , If you don't receive MASTER The announcement information of , various BACKUP Will declare oneself to be MASTER, Send an announcement message , Re engage MASTER Election status .

Keepalived Installation

Be careful : Need to be in 192.168.209.135、 192.168.209.136 Install... On both servers Keepalived.

Keepalived (http://www.keepalived.org/download.html )

Upload or download keepalived

Upload or download keepalived(keepalived-1.2.18.tar.gz) To /usr/local/src Catalog

Unpack the installation

install keepalived Need to use openssl

# yum install gcc gcc-c++ openssl openssl-devel
# cd /usr/local/src
# tar -zxvf keepalived-1.2.18.tar.gz
# cd keepalived-1.2.18
# ./configure --prefix=/usr/local/keepalived
# make && make install

take keepalived Install as Linux system service

Because I didn't use keepalived The default path to install ( The default is /usr/local) , After installation , Some work needs to be done Copy the default configuration file to the default path

# mkdir /etc/keepalived
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

Copy keepalived Service script to default address

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
# ln -s /usr/local/keepalived/sbin/keepalived /sbin/

Set up keepalived Service startup

# chkconfig keepalived on

modify Keepalived The configuration file

(1) MASTER Node profile (192.168.209.135)

! Configuration File for keepalived
global_defs {
## keepalived  The self-contained email reminder needs to be opened  sendmail  service . Independent monitoring or a third party is recommended  SMTP
 router_id liuyazhuang135 ##  String identifying this node , Usually it is  hostname
}
## keepalived  Will execute the script regularly and analyze the results of script execution , Dynamic adjustment  vrrp_instance  The priority of the .
##  If the script execution results in  0, also  weight  The value of the configuration is greater than  0, Then the priority will increase accordingly .
##  If the script execution result is not  0, also  weight  The value of the configuration is less than  0, The priority will be reduced accordingly .
##  Other situations , Maintain the priority of the original configuration , In the configuration file  priority  Corresponding value .
vrrp_script chk_haproxy {
 script "/etc/keepalived/haproxy_check.sh" ##  testing  haproxy  Script path to state 
 interval 2 ##  Test interval 
 weight 2 ##  If the condition holds , The weight +2
}
##  Define virtual routes , VI_1  Is the identifier of the virtual route , Define your own name 
vrrp_instance VI_1 {
 state BACKUP ##  Default master device (priority  Worth a lot of money ) And spare equipment (priority  Low value ) Set to  BACKUP,
 ##  from  priority  To control the default active / standby mode when the system is started at the same time , Otherwise, the main device that starts first 
 interface eth3 ##  Bind virtual  IP  Network interface , With this machine  IP  The network interface where the address is located is the same , My is  eth3
 virtual_router_id 35 ##  Virtual routed  ID  Number , The settings of the two nodes must be the same , Optional  IP  The last paragraph uses ,
 ##  same  VRID  As a group , He will decide what to multicast  MAC  Address 
 priority 120 ##  Node priority , Range of values  0-254, MASTER  than  BACKUP  high 
 nopreempt ##  Main equipment (priority  Worth a lot of money ) Configuration must add  nopreempt, Otherwise, non preemption will not work 
 advert_int 1 ##  Multicast information sending interval , The settings of the two nodes must be the same , Default  1s
 ##  Set validation information , The two nodes must be the same 
 authentication {
  auth_type PASS
  auth_pass 1111 ##  Real production , According to the demand, we should come here 
 }
 ##  take  track_script  Block join  instance  Configuration block 
 track_script {
  chk_haproxy ##  Check  HAProxy  Whether the service survives 
 }
 ##  fictitious  IP  pool ,  The settings of the two nodes must be the same 
 virtual_ipaddress {
  192.168.209.130 ##  fictitious  ip, Multiple can be defined , Each row of a 
 }
}

(2)BACKUP Node profile (192.168.209.136)

! Configuration File for keepalived
global_defs {
 router_id liuyazhuang136
}
vrrp_script chk_haproxy {
 script "/etc/keepalived/haproxy_check.sh"
 interval 2
 weight 2
}
vrrp_instance VI_1 {
 state BACKUP
 interface eth3
 virtual_router_id 35
 priority 110
 advert_int 1
 authentication {
  auth_type PASS
  auth_pass 1111
 }
 track_script {
  chk_haproxy
 }
 virtual_ipaddress {
  192.168.209.130
 }
}

Particular attention : If non preemptive mode doesn't work , stay Keepalived After the recovery of the failed node, it will lead and preempt again vip, As a result vip The risk of switching and flashing ( Video commentary ). According to the above configuration , Configured with Keepalived Non preemptive mode , The configuration and points for attention are as follows :(1) Main equipment 、 From the device state Set to BACKUP (2) Main equipment 、 Do not configure from the device mcast_src_ip ( This machine IP Address ) (3) Default master device (priority Worth a lot of money Keepalived node ) Configuration must add nopreempt, Otherwise, preemption doesn't work (4) Firewall configuration allows multicast ( Lord 、 Both standby devices need to be configured , keepalived Use 224.0.0.18 As Master and Backup Health check communication IP)

# iptables -I INPUT -i eth3 -d 224.0.0.0/8 -p vrrp -j ACCEPT
# iptables -I OUTPUT -o eth3 -d 224.0.0.0/8 -p vrrp -j ACCEPT
(eth3  Name of the network card device of the host , The server in production environment can handle multicast and heartbeat detection with independent network card )
# service iptables save
 service iptables restart :
# service iptables restart

To write Haproxy State detection script

The script we wrote is /etc/keepalived/haproxy_check.sh ( Already in keepalived.conf Middle configuration ) Script requirements : If haproxy Stop running , Try to start , If it doesn't start, kill the machine's keepalived process ,keepalied Virtual ip Bound to the BACKUP On the machine .

The contents are as follows :

# mkdir -p /usr/local/keepalived/log
# vi /etc/keepalived/haproxy_check.sh

haproxy_check.sh The script is as follows :

#!/bin/bash
START_HAPROXY="/etc/rc.d/init.d/haproxy start"
STOP_HAPROXY="/etc/rc.d/init.d/haproxy stop"
LOG_FILE="/usr/local/keepalived/log/haproxy-check.log"
HAPS=`ps -C haproxy --no-header |wc -l`
date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
echo "check haproxy status" >> $LOG_FILE
if [ $HAPS -eq 0 ];then
echo $START_HAPROXY >> $LOG_FILE
$START_HAPROXY >> $LOG_FILE 2>&1
sleep 3
if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
echo "start haproxy failed, killall keepalived" >> $LOG_FILE
killall keepalived
fi
fi

After the save , Give the script permission to execute :

# chmod +x /etc/keepalived/haproxy_check.sh

start-up Keepalived

# service keepalived start
Starting keepalived: [ OK ]

Keepalived Service management command :

 stop it :service keepalived stop
 start-up :service keepalived start
 restart :service keepalived restart
 Check the status :service keepalived status

High availability test

(1) close 192.168.209.135 Medium Haproxy, Keepalived Will restart it

# service haproxy stop

(2) close 192.168.209.135 Medium Keepalived, VIP(192.168.209.130) Will be 192.168.209.136 preemption

# service keepalived stop

It can be seen from the above figure :Keepalived After the stop , 192.168.209.135 Node's network interface VIP(192.168.209.130) Will disappear

here , It can be seen from the above figure : stay 192.168.209.136 In the network interface of the node VIP(192.168.209.130).

Check this out VIP Corresponding MAC, Windows Next use CMD Command view :

That at this time VIP Has drifted to the physical host 192.168.209.136 Yes

Re pass VIP(192.168.209.130) To visit Haproxy colony , What we visited is also 192.168.209.136

(3) Restart 192.168.209.135 Medium Keepalived

Restart 192.168.209.135 Medium Keepalived, vip(192.168.209.130) Keep in 192.168.209.136 On a host , There will be no 135 Start preemption vip The situation of .

# service keepalived start

(4) Simulation preempts vip The node of (192.168.209.136) Medium HAProxy Failure or start failure

The way : hold 192 Nodes in the haproxy.cfg Rename file to haproxy.cfg_bak, And put haproxy The service is carried out kill fall , here keepalived Will try to start haproxy, It will fail to start because the configuration file cannot be found , At this point haproxy_check.sh Script killall keepalived command , end keepalived Conduct . And then there was 192.168.209.135 Node re preemption vip

That at this time VIP Has drifted to the physical host 192.168.209.135 Yes

Re pass VIP(192.168.209.130) To visit Haproxy colony , What we visited is also 192.168.209.135

Verify database access

adopt vip Access database 、 verification vip Database access after switching

(1) Command line access to the database

(2)Navicat Access database

thus ,Mycat Implementation of high availability load balancing cluster (HAProxy + Keepalived + Mycat) Set up

You can go to the link http://download.csdn.net/detail/l1028386804/9915621 Download build Mycat Implementation of high availability load balancing cluster (HAProxy + Keepalived + Mycat) The use of Keepalived

This article is from WeChat official account. - Glacier Technology (hacker-binghe)

The source and reprint of the original text are detailed in the text , If there is any infringement , Please contact the [email protected] Delete .

Original publication time : 2020-12-19

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[glacier]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224160737104q.html

Scroll to Top