전산쟁이의 기억노트

keepalived기반의 DB이중화 구현 본문

mysql&mariadb

keepalived기반의 DB이중화 구현

잘나가는전산쟁이 2022. 7. 25. 00:20
728x90
반응형
SMALL

시스템 구성도

  1. 서버정보
  2. 로드밸런싱 종류
    알고리즘 종류 알고리즘 설명 설정값
    라운드 로빈 하나씩 분배 rr
    가중치 라운드 로빈 가중치가 큰 서버로 분배 wrr
    최소 연결 접속수가 적은 서버로 분배 lc
    가중치 최소연결 (접속수+1)/가중치 로 최소값 서버로 분배 wlc
    지역 최소연결 접속수가 가중치를 넘기기전까지 한서버만 분배 lblc
    복제기반의 지역 최소연결 가중치를 넘고 있을때 접속수가 가장 적은 서버 선택 (기본작동은 lblc와 동일) lblcr
    목적지 해시 목적지 IP로 부터 해시값 계산해서 분배 dh
    소스 해시 소스 IP로 부터 해시값 계산해서 분배 sh
    응답속도 응답속도(Short Expected Delay)가 가장 짧은 서버로 분배
    (실제로는 접속수가 가장 적은 서버로 분배)    		 sed
    큐 없음 Active 접속수r가 0인 서버를 우선 분배(기본작동은 sed알고리즘과 동일) nq

시스템 설치(전체서버)

  1. 패키지 설치
    $> yum install keepalived ipvsadm -y

     
  2. keepalived 설정
    
$> vi /etc/keepalived/keepalived.conf
global_defs {
  router_id MARIA_HA
  script_user root
}

vrrp_instance DB_HA {
  state BACKUP
  interface eth0
  virtual_router_id 10
  priority 100
  lvs_sync_daemon_interface eth0

  authentication {
    auth_type PASS
    auth_pass mariadb
 }

  virtual_ipaddress {
   172.21.115.207
 }
}

virtual_server 172.21.115.217 3306 {
  delay_loop 3
  lb_algo dh
  protocol TCP
  real_server 172.21.115.218 3306 {
    weight 100
    MISC_CHECK {
      misc_path "/svc/mysql_check.sh 172.21.115.218"
      misc_timeout 3
   }
 }
  real_server 172.21.115.215 3306 {
    weight 100
    MISC_CHECK {
      misc_path "/svc/mysql_check.sh 172.21.115.215"
      misc_timeout 3
   }
 }
}

     
  3. 모니터 스크립트 설정 
    $> vi /svc/mysql_check.sh

#!/bin/bash

/svc/mysql/bin/mysql -u monitor -pmonitor -h $1 -Nse 'select now()'

$> chmod 755 /svc/mysql_check.sh
SMALL

시스템 설치(DB#1서버)

  1. 접근을 위한 db계정설정 
    MariaDB [(none)]> grant process on *.* to  monitor@'172.21.115.215' identified by 'monitor' with grant option;
MariaDB [(none)]> grant process on *.* to  monitor@'172.21.115.218' identified by 'monitor' with grant option;
MariaDB [(none)]> flush privileges;
  2. 커널 파라미터 수정 후 적용 
    $> vi /etc/sysctl.conf
...
net.ipv4.ip_forward = 1
...
$> sysctl -p
  3. 서비스 실행 후 VIP할당여부 확인 
    $> systemctl start keepalived
$> ip addr show | grep 32
    inet 172.21.115.217/32 scope global eth0   #실제로 Active된 노드에서만 VIP 할당됨
     
     

keepalived 운영

  1. 모니터링 방법
    $> ipvsadm -Ln

IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  172.21.115.217:3306 sh
  -> 172.21.115.215:3306          Masq    100    2          0
  -> 172.21.115.218:3306          Masq    100    50         0

     
  2. ​​​​​​현재 연결된 세션 정보 확인 
    $> ipvsadm -Lnc
IPVS connection entries
pro expire state       source             virtual            destination
TCP 14:56  ESTABLISHED 172.21.114.22:52950 172.21.115.217:3306 172.21.115.218:3306
TCP 14:56  ESTABLISHED 172.21.114.22:52968 172.21.115.217:3306 172.21.115.218:3306
TCP 14:56  ESTABLISHED 172.21.114.22:52984 172.21.115.217:3306 172.21.115.218:3306
TCP 14:54  ESTABLISHED 172.21.114.22:52928 172.21.115.217:3306 172.21.115.218:3306
TCP 14:56  ESTABLISHED 172.21.114.22:52980 172.21.115.217:3306 172.21.115.218:3306
     
     

장애발생시 조치

  1. 장애구현 (DB#1 Mariadb 접근 불가시)
     
     
    # ipvsadm에서 장애발생서버 제외
    $> ipvsadm
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  172.21.115.217:3306 sh
  -> 172.21.115.215:3306          Masq    100    33         13
     
     
  2. # Keepalived에서 Mysql 접근 불가 확인 $> vi /var/log/messages ... Mar 11 14:36:43 dbms01 Keepalived_healthcheckers[666145]: Misc check for [[172.21.115.218]:tcp:3306 VS [172.21.115.217]:tcp:3306] by [/svc/mysql_check.sh] timed out with retry disabled. Mar 11 14:36:43 dbms01 Keepalived_healthcheckers[666145]: Removing service [172.21.115.218]:tcp:3306 to VS [172.21.115.217]:tcp:3306 ...
  3. 장애구현 (DB#1 서버 장애시)
    #master 서버
$> vi /var/log/messages
Mar 11 14:19:21 dbms01 systemd[1]: Stopping LVS and VRRP High Availability Monitor...
Mar 11 14:19:21 dbms01 Keepalived[664420]: Stopping
Mar 11 14:19:21 dbms01 Keepalived_healthcheckers[664421]: Shutting down service [172.21.115.218]:tcp:3306 from VS [172.21.115.217]:tcp:3306
Mar 11 14:19:21 dbms01 kernel: IPVS: stopping master sync thread 664516 ...
Mar 11 14:19:21 dbms01 Keepalived_vrrp[664422]: (DB_HA) sent 0 priority
Mar 11 14:19:21 dbms01 Keepalived_vrrp[664422]: (DB_HA) removing VIPs.
Mar 11 14:19:21 dbms01 Keepalived_healthcheckers[664421]: Shutting down service [172.21.115.215]:tcp:3306 from VS [172.21.115.217]:tcp:3306
Mar 11 14:19:21 dbms01 Keepalived_healthcheckers[664421]: Stopped - used 0.011017 user time, 0.141106 system time
Mar 11 14:19:22 dbms01 Keepalived_vrrp[664422]: Stopped - used 0.011218 user time, 0.026105 system time
Mar 11 14:19:22 dbms01 Keepalived[664420]: CPU usage (self/children) user: 0.000000/1.222181 system: 0.001086/1.218784
Mar 11 14:19:22 dbms01 Keepalived[664420]: Stopped Keepalived v2.1.5 (07/13,2020)
Mar 11 14:19:22 dbms01 systemd[1]: keepalived.service: Succeeded.
Mar 11 14:19:22 dbms01 systemd[1]: Stopped LVS and VRRP High Availability Monitor.
1
2
3
4
5
6
7
8

#Standby 서버
$> vi /var/log/messages
Mar 11 14:20:41 dbms02 Keepalived_vrrp[666146]: (DB_HA) Backup received priority 0 advertisement
Mar 11 14:20:42 dbms02 Keepalived_vrrp[666146]: (DB_HA) Receive advertisement timeout
Mar 11 14:20:42 dbms02 Keepalived_vrrp[666146]: (DB_HA) Entering MASTER STATE
Mar 11 14:20:42 dbms02 Keepalived_vrrp[666146]: (DB_HA) setting VIPs.
Mar 11 14:20:42 dbms02 Keepalived_vrrp[666146]: (DB_HA) Sending/queueing gratuitous ARPs on eth0 for 172.21.115.217
Mar 11 14:20:42 dbms02 Keepalived_vrrp[666146]: Sending gratuitous ARP on eth0 for 172.21.115.217
     

reference

728x90
반응형
LIST
저작자표시

'mysql&mariadb' 카테고리의 다른 글

centos7 galera cluster설치  (0) 2022.07.25
corosync / pacemaker 기반의 DB이중화 구현  (0) 2022.07.25
extrabackup을 이용한 mysql 백업 수행  (0) 2022.07.25
my.cnf를 이용한 mysql바이너리 로그 관리  (0) 2022.07.24
centos6에 mysql 5.7설치하기  (0) 2022.07.24
'mysql&mariadb' Related Articles more
Comments