close
프로필 배경
프로필 로고

JH 개인 블로그

DataBase/MySQL & MariaDB · 2022. 3. 10. fullscreen 넓게보기

[Mysql - Haproxy 구성]

안녕하세요 이번글에선 개발사나 고객사측에서 실수하는일이 없도록 방지차원해서 db앞단에 haproxy 서버를 구성하였습니다.

 

Haproxy 서버를 생성하여 Haproxy의 public IP와 해당 지정 port로 접속하면 MHA구조의 vip로 붙도록 설정할 수 있습니다.
VIP로 DB에 접속하게 하여 MasterDB가 down되더라도 Slave로 자동 접속이 가능하도록 할 수 있습니다.
DB 앞단에 haproxy서버를 두어 vip를 통해 접속 시 master가 죽었는데 slave를 master로 착각하고 data를 넣는 일을 방지하기 위해 사용하였습니다.
 
 

 

Haproxy

-Load Balancer로  사용  할  수  있는  오픈소스 

-Active Health Check이  가능해  안정적인  운영가능

 

 

 

 

테스트 환경

Hostname
IP
vip
mysql
192.168.100.53
192.168.100.55
haproxy
192.168.100.52
 

 

 

 

 

 사전 작업

 

■ haproxy port open(1883)

 

 

■ mysql 서버 ip확인

[root@mysql ~]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.41.38.209  netmask 255.255.254.0  broadcast 10.41.39.255
        ether f2:20:cd:9e:27:66  txqueuelen 1000  (Ethernet)
        RX packets 86211  bytes 834772344 (796.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 48783  bytes 3752529 (3.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.53  netmask 255.255.255.0  broadcast 192.168.100.255
        ether f2:d7:cf:8b:19:1b  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 12  bytes 504 (504.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth1:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.55  netmask 255.255.255.0  broadcast 192.168.100.255
        ether f2:d7:cf:8b:19:1b  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 32  bytes 2592 (2.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 32  bytes 2592 (2.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
 
 
 
 
 
 

 테스트 시작

 

■ Haproxy install (haproxy 서버)

[root@haproxy ~]# yum install gcc pcre-static pcre-devel openssl-devel


[root@haproxy ~]# wget http://www.haproxy.org/download/2.2/src/haproxy-2.2.2.tar.gz
--2022-03-07 17:44:21--  http://www.haproxy.org/download/2.2/src/haproxy-2.2.2.tar.gz
Resolving www.haproxy.org (www.haproxy.org)... 51.15.8.218, 2001:bc8:35ee:100::1
Connecting to www.haproxy.org (www.haproxy.org)|51.15.8.218|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2869005 (2.7M) [application/x-tar]
Saving to: ‘haproxy-2.2.2.tar.gz’

100%[===================================================>] 2,869,005   1.52MB/s   in 1.8s   

2022-03-07 17:44:24 (1.52 MB/s) - ‘haproxy-2.2.2.tar.gz’ saved [2869005/2869005]



[root@haproxy ~]# tar -zxvf haproxy-2.2.2.tar.gz
[root@haproxy ~]# cd haproxy-2.2.2/
[root@haproxy haproxy-2.2.2]# make TARGET=linux-glibc USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_CRYPT_H=1 USE_LIBCRYPT=1
  CC      src/ev_poll.o
  CC      src/ev_epoll.o
  CC      src/ssl_sample.o
  CC      src/ssl_sock.o
  CC      src/ssl_crtlist.o
  CC      src/ssl_ckch.o
  CC      src/ssl_utils.o
  CC      src/cfgparse-ssl.o
  CC      src/namespace.o
  CC      src/mux_fcgi.o
  CC      src/mux_h1.o
  CC      src/mux_h2.o
  CC      src/backend.o
  CC      src/cfgparse.o
  CC      src/cli.o
  CC      src/cfgparse-listen.o
  CC      src/stats.o
  CC      src/http_ana.o
  CC      src/stream.o
  CC      src/check.o
  CC      src/sample.o
  CC      src/tools.o
  CC      src/server.o
  CC      src/listener.o
  CC      src/tcpcheck.o
  CC      src/pattern.o
  CC      src/log.o
  CC      src/stick_table.o
  CC      src/flt_spoe.o
  CC      src/stream_interface.o
  CC      src/filters.o
  CC      src/http_fetch.o
  CC      src/map.o
  CC      src/session.o
  CC      src/sink.o
  CC      src/flt_http_comp.o
  CC      src/debug.o
  CC      src/tcp_rules.o
  CC      src/haproxy.o
  CC      src/peers.o
  CC      src/flt_trace.o
  CC      src/queue.o
  CC      src/proxy.o
  CC      src/http_htx.o
  CC      src/dns.o
  CC      src/raw_sock.o
  CC      src/pool.o
  CC      src/http_act.o
  CC      src/http_rules.o
  CC      src/compression.o
  CC      src/cfgparse-global.o
...

 



[root@haproxy haproxy-2.2.2]# make install
‘haproxy’ -> ‘/usr/local/sbin/haproxy’
‘doc/haproxy.1’ -> ‘/usr/local/share/man/man1/haproxy.1’
install: creating directory ‘/usr/local/doc’
install: creating directory ‘/usr/local/doc/haproxy’
‘doc/configuration.txt’ -> ‘/usr/local/doc/haproxy/configuration.txt’
‘doc/management.txt’ -> ‘/usr/local/doc/haproxy/management.txt’
‘doc/seamless_reload.txt’ -> ‘/usr/local/doc/haproxy/seamless_reload.txt’
‘doc/architecture.txt’ -> ‘/usr/local/doc/haproxy/architecture.txt’
‘doc/peers-v2.0.txt’ -> ‘/usr/local/doc/haproxy/peers-v2.0.txt’
‘doc/regression-testing.txt’ -> ‘/usr/local/doc/haproxy/regression-testing.txt’
‘doc/cookie-options.txt’ -> ‘/usr/local/doc/haproxy/cookie-options.txt’
‘doc/lua.txt’ -> ‘/usr/local/doc/haproxy/lua.txt’
‘doc/WURFL-device-detection.txt’ -> ‘/usr/local/doc/haproxy/WURFL-device-detection.txt’
‘doc/proxy-protocol.txt’ -> ‘/usr/local/doc/haproxy/proxy-protocol.txt’
‘doc/linux-syn-cookies.txt’ -> ‘/usr/local/doc/haproxy/linux-syn-cookies.txt’
‘doc/SOCKS4.protocol.txt’ -> ‘/usr/local/doc/haproxy/SOCKS4.protocol.txt’
‘doc/network-namespaces.txt’ -> ‘/usr/local/doc/haproxy/network-namespaces.txt’
‘doc/DeviceAtlas-device-detection.txt’ -> ‘/usr/local/doc/haproxy/DeviceAtlas-device-detection.txt’
‘doc/51Degrees-device-detection.txt’ -> ‘/usr/local/doc/haproxy/51Degrees-device-detection.txt’
‘doc/netscaler-client-ip-insertion-protocol.txt’ -> ‘/usr/local/doc/haproxy/netscaler-client-ip-insertion-protocol.txt’
‘doc/peers.txt’ -> ‘/usr/local/doc/haproxy/peers.txt’
‘doc/close-options.txt’ -> ‘/usr/local/doc/haproxy/close-options.txt’
‘doc/SPOE.txt’ -> ‘/usr/local/doc/haproxy/SPOE.txt’
‘doc/intro.txt’ -> ‘/usr/local/doc/haproxy/intro.txt’
 
 
 
 

■ 디렉토리 생성 및 권한

[root@haproxy haproxy-2.2.2]# mkdir -p /etc/haproxy

[root@haproxy haproxy-2.2.2]# mkdir -p /var/lib/haproxy

[root@haproxy haproxy-2.2.2]# touch /var/lib/haproxy/stats

[root@haproxy haproxy-2.2.2]# ln -s /usr/local/sbin/haproxy /usr/sbin/haproxy

[root@haproxy haproxy-2.2.2]# cp examples/haproxy.init /etc/init.d/haproxy

[root@haproxy haproxy-2.2.2]# chmod 755 /etc/init.d/haproxy

[root@haproxy haproxy-2.2.2]# systemctl reload haproxy
Failed to reload haproxy.service: Unit not found.


[root@haproxy haproxy-2.2.2]# chkconfig haproxy on

[root@haproxy haproxy-2.2.2]# useradd -r haproxy

[root@haproxy haproxy-2.2.2]# haproxy -v
HA-Proxy version 2.2.2 2020/07/31 - https://haproxy.org/
Status: long-term supported branch - will stop receiving fixes around Q2 2025.
Known bugs: http://www.haproxy.org/bugs/bugs-2.2.2.html
Running on: Linux 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 25 17:23:54 UTC 2020 x86_64
 
 

 

■ config 수정 및 기동

[root@haproxy haproxy-2.2.2]# cd /etc/haproxy


[root@haproxy haproxy]# vim haproxy.cfg
#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------


#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
#    log         127.0.0.1 local2
    log         /dev/log local2 info
    log         /dev/log local2 notice
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats


#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000


#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
#frontend  main *:5000
#    acl url_static       path_beg       -i /static /images /javascript /stylesheets
#    acl url_static       path_end       -i .jpg .gif .png .css .js


#    use_backend static          if url_static
#    default_backend             app


#---------------------------------------------------------------------
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------
#backend static
#    balance     roundrobin
#    server      static 127.0.0.1:4331 check


#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
#backend app
#    balance     roundrobin
#    server  app1 127.0.0.1:5001 check
#    server  app2 127.0.0.1:5002 check
#    server  app3 127.0.0.1:5003 check
#    server  app4 127.0.0.1:5004 check

listen mysql-vip
       balance roundrobin
       bind *:1883
       log global
       mode tcp
       option tcplog
       server mysql-vip 192.168.100.55:3306           //VIP 입력


listen HAProxy-stats
       mode http
       bind *:8888
       stats enable
       stats scope   .
       stats uri     /admin?stats
       stats realm   HAProxy\ Statistics
       stats auth    admin:GDproxy
       stats refresh 30s

 
 

 

■ Haproxy 기동

[root@haproxy haproxy]# haproxy -f haproxy.cfg

[root@haproxy haproxy]# systemctl start haproxy.service

[root@haproxy haproxy]# systemctl status haproxy.service
● haproxy.service - SYSV: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments.
   Loaded: loaded (/etc/rc.d/init.d/haproxy; bad; vendor preset: disabled)
   Active: active (running) since Tue 2022-03-08 10:30:41 KST; 4s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1221 ExecStop=/etc/rc.d/init.d/haproxy stop (code=exited, status=0/SUCCESS)
  Process: 1230 ExecStart=/etc/rc.d/init.d/haproxy start (code=exited, status=0/SUCCESS)
Main PID: 1239 (haproxy)
   CGroup: /system.slice/haproxy.service
           └─1239 /usr/sbin/haproxy -D -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid

Mar 08 10:30:41 haproxy systemd[1]: Stopped SYSV: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments..
Mar 08 10:30:41 haproxy systemd[1]: Starting SYSV: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments....
Mar 08 10:30:41 haproxy haproxy[1230]: /etc/rc.d/init.d/haproxy: line 26: [: =: unary operator expected
Mar 08 10:30:41 haproxy haproxy[1238]: Proxy mysql-vip started.
Mar 08 10:30:41 haproxy haproxy[1238]: Proxy mysql-vip started.
Mar 08 10:30:41 haproxy haproxy[1230]: Starting haproxy: [  OK  ]
Mar 08 10:30:41 haproxy systemd[1]: Started SYSV: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments..
 
 
 
 

■ haproxy IP

[root@haproxy ~]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.41.42.68  netmask 255.255.254.0  broadcast 10.41.43.255
        ether f2:20:cd:98:33:65  txqueuelen 1000  (Ethernet)
        RX packets 21411  bytes 74198001 (70.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 10585  bytes 1811572 (1.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.52  netmask 255.255.255.0  broadcast 192.168.100.255
        ether f2:d7:cf:d3:f4:fa  txqueuelen 1000  (Ethernet)
        RX packets 12  bytes 552 (552.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 6  bytes 252 (252.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 32  bytes 2592 (2.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 32  bytes 2592 (2.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
 
 
 

■ DB user생성

mysql> create user kim@'%' identified by 'kim';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to kim@'%';
Query OK, 0 rows affected (0.01 sec)
 
 

 

■ connect test

haproxy서버의 public ip를 통해 DB접속(1883 port)

 

저작자표시

'DataBase > MySQL & MariaDB' 카테고리의 다른 글

[MariaDB - MHA Chain replication 구성을 통한 데이터 이관]  (0) 2022.03.10
[MySQL - MSR(Multi Source Replication)구성]  (2) 2022.03.10
[MariaDB - Mariabackup error] failed to copy enough redo log  (0) 2022.02.24
[MariaDB - Galera Cluster & sysbench 이용 동기화 테스트 ] part 3  (0) 2022.02.24
[MariaDB - Galera Cluster & Maxscale 구성 및 failover test] part 2  (0) 2022.02.24
DataBase/MySQL & MariaDB 관련 글
더 보기

티스토리툴바