close
프로필 배경
프로필 로고

JH 개인 블로그

DataBase/MySQL & MariaDB · 2023. 2. 5. fullscreen 넓게보기

[MySQL - Orchestrator VIP설정 part 3 use. Keepalived]

Orchestrator 나 MHA 와 같은 HA 툴의 경우 Failover 등의 가용성 기능을 제공을 하지만 VIP 리소스 에 대한 기능은 제공하지 않아  별도의 스크립트 등으로 구현 해서 사용 해야 합니다.
물론 hooking이 되는 파라미터 나 스크립트 내 호출 함수 등에 대한 정보, 사용시 수정 해야할 사항 등은 정보가 제공되지만 VIP 를 Assign 하고 Relocate 하는 등의 실제 동작하는 부분에 해당하는 스크립트나 프로그램은 별도로 작성이 필요로 합니다.
따라서 저같은 경우에는 keepalived 툴을 사용하여 VIP 관리 할 예정입니다.

 

 

Keepalived란?
'로드밸런싱'과 '고가용성(HA)'를 제공하는 프레임워크.
-. 로드밸런싱은 L4 수준의 로드 밸런싱(HAProxy와 함께 사용하면, L7 로드밸런싱도 가능)
-. 고가용성(HA)은 VRRP 프로토콜을 사용한 VIP로 고가용성이 가능.

 

 
[Keepalived의 고가용성(HA) 동작방식]

 

 

사전 작업

VIP 생성 (Master,Slave)

[root@master network-scripts]# vi ifcfg-eth1:0

DEVICE=eth1:0
IPADDR=192.168.100.40
NETMASK=255.255.255.0
BROADCAST=127.255.255.255
ONBOOT=NO

 

 
■ keepalived 설치(Master, Slave)
[root@master ~]# yum -y install keepalived

 

 

■ Config 설정
router_id : 서버별 다르게 임의의 이름 지정.
state :  MASTER와 BACKUP을 지정 가능. VIP를 가질 MASTER 서버는 MASTER로 그 외 MASTER가 죽었을 때 대체될 서버는 BACKUP으로 지정.
interface : 가상 IP를 부여할 이더넷 장치
virtual_router_id : 일종의 그룹ID로 이 값은 가상 IP가 할당될 MASTER와 BACKUP모두 동일한 값으로 기재
priority : 여러대의 서버가 묶여 있을 때 우선적으로 가상 ip를 할당할 우선순위를 지정하는 값으로 가장 높은 값을 가진 쪽이 우선으로합니다. MASTER 쪽에 가장 높은 값을 그 외 SLAVE서버에 순차적으로 작은 값을 지정. => failover이후에 원래 Slave였던 서버를 Master로 쓸 계획이기 때문에 저는 동일한 값을 주었습니다.
virtual_ipaddress : 가상ip를 지정

 

 

<Master> 

[root@master keepalived]# vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MySQL_0
}

vrrp_script chk_mysql_port {
    script "/mysql/chk_mysql.sh"
    interval 2
    weight -5
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    mcast_src_ip 192.168.100.35
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.100.40/24
    }

track_script {
   chk_mysql_port
}
}

 

 

<Slave> 

[root@slave keepalived]# vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MySQL_0
}

vrrp_script chk_mysql_port {
    script "/mysql/chk_mysql.sh"
    interval 2
    weight -5
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    mcast_src_ip 192.168.100.36
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.100.40
    }

track_script {
   chk_mysql_port
}
}

 

 

 

■ mysql 모니터링 스크립트 생성 (Master, Slave)
[root@master ~]# vi /mysql/chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi

 

 

 

■ keepalived 실행 (Master, Slave)
설정이 완료되면 MySQL 데이터베이스를 시작하고 keepalive를 시작합니다. Keepalive는 MySQL이 시작된 후 실행 상태를 감지하므로 MySQL을 먼저 시작하고 keepalive를 시작하는지 주의해야 합니다. MySQL이 비정상적으로 실행되면 keepalive가 자동으로 종료됩니다. 
[root@master ~]# systemctl start keepalived.service

[root@master ~]# systemctl status keepalived.service
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2023-02-01 12:42:08 KST; 5min ago
  Process: 14189 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 14190 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─14190 /usr/sbin/keepalived -D
           ├─14191 /usr/sbin/keepalived -D
           └─14192 /usr/sbin/keepalived -D

Feb 01 12:42:10 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:10 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:10 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:10 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:15 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:15 master Keepalived_vrrp[14192]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on eth1 for 192.168.100.40
Feb 01 12:42:15 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:15 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:15 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
Feb 01 12:42:15 master Keepalived_vrrp[14192]: Sending gratuitous ARP on eth1 for 192.168.100.40
 

 

 
■ VIP 확인
현재 Master에 할당되어 있다.
[root@master ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:20:cd:e2:5f:a4 brd ff:ff:ff:ff:ff:ff
    inet 10.41.134.200/23 brd 10.41.135.255 scope global dynamic eth0
       valid_lft 946067001sec preferred_lft 946067001sec
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:d7:cf:db:25:ed brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.35/24 brd 127.255.255.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet 192.168.100.40/24 scope global secondary eth1
       valid_lft forever preferred_lft forever




[root@slave ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:20:cd:2e:e6:ca brd ff:ff:ff:ff:ff:ff
    inet 10.41.174.129/23 brd 10.41.175.255 scope global dynamic eth0
       valid_lft 945881359sec preferred_lft 945881359sec
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:d7:cf:cc:bb:f0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.36/24 brd 127.255.255.255 scope global eth1
       valid_lft forever preferred_lft forever

 

 

 

 

Failover Test (VIP relocate test)

■ MySQL kill 

[root@master ~]# ps -ef | grep mysql
mysql    28461 24284  0 15:40 pts/1    00:00:01 mysqld --user=mysql
root     29346 24284  0 15:43 pts/1    00:00:00 grep --color=auto mysql


[root@master ~]# kill -9 28461

 

 

■ Master VIP 확인 => vip없음 

[root@master ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:20:cd:e2:5f:a4 brd ff:ff:ff:ff:ff:ff
    inet 10.41.134.200/23 brd 10.41.135.255 scope global dynamic eth0
       valid_lft 946056559sec preferred_lft 946056559sec
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:d7:cf:db:25:ed brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.35/24 brd 127.255.255.255 scope global eth1
       valid_lft forever preferred_lft forever

 

 

■ Slave VIP확인
Master의 VIP가 Slave서버에 할당되어 있다.
[root@slave ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:20:cd:2e:e6:ca brd ff:ff:ff:ff:ff:ff
    inet 10.41.174.129/23 brd 10.41.175.255 scope global dynamic eth0
       valid_lft 945881675sec preferred_lft 945881675sec
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:d7:cf:cc:bb:f0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.36/24 brd 127.255.255.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet 192.168.100.40/32 scope global eth1
       valid_lft forever preferred_lft forever

 

 

■ keepalived 상태 확인 
=> mysql을 모니터링하는 스크립트가 mysql이 내려갔음을 감지하고 keepalived를 끈다.
[root@master ~]# systemctl status keepalived.service
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

Feb 01 15:40:20 master Keepalived_vrrp[28537]: Sending gratuitous ARP on eth1 for 192.16...40
Feb 01 15:40:20 master Keepalived_vrrp[28537]: Sending gratuitous ARP on eth1 for 192.16...40
Feb 01 15:40:20 master Keepalived_vrrp[28537]: Sending gratuitous ARP on eth1 for 192.16...40
Feb 01 15:43:22 master Keepalived[28535]: Stopping
Feb 01 15:43:22 master systemd[1]: Stopping LVS and VRRP High Availability Monitor...
Feb 01 15:43:22 master Keepalived_vrrp[28537]: VRRP_Instance(VI_1) sent 0 priority
Feb 01 15:43:22 master Keepalived_vrrp[28537]: VRRP_Instance(VI_1) removing protocol VIPs.
Feb 01 15:43:22 master Keepalived_healthcheckers[28536]: Stopped
Feb 01 15:43:23 master Keepalived_vrrp[28537]: Stopped
Feb 01 15:43:23 master systemd[1]: Stopped LVS and VRRP High Availability Monitor.
Hint: Some lines were ellipsized, use -l to show in full.

 

 

 

■ Log 

[root@master ~]# tail -200f /var/log/messages
...
Feb  1 15:43:22 localhost Keepalived[28535]: Stopping
Feb  1 15:43:22 localhost systemd: Stopping LVS and VRRP High Availability Monitor...
Feb  1 15:43:22 localhost Keepalived_vrrp[28537]: VRRP_Instance(VI_1) sent 0 priority
Feb  1 15:43:22 localhost Keepalived_vrrp[28537]: VRRP_Instance(VI_1) removing protocol VIPs.
Feb  1 15:43:22 localhost Keepalived_healthcheckers[28536]: Stopped
Feb  1 15:43:23 localhost Keepalived_vrrp[28537]: Stopped
Feb  1 15:43:23 localhost Keepalived[28535]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Feb  1 15:43:23 localhost systemd: Stopped LVS and VRRP High Availability Monitor.

 

 

 

 

 

OS 다운 failover test

■ Master서버 down 

[root@master ~]# shutdown
Shutdown scheduled for Wed 2023-02-01 15:55:13 KST, use 'shutdown -c' to cancel.
[root@master ~]#
Broadcast message from root@master (Wed 2023-02-01 15:54:13 KST):

The system is going down for power-off at Wed 2023-02-01 15:55:13 KST!

 

 

■ orchestrator 장애감지

 

 

■ Slave서버 Master로 승격

 

 

■ VIP 절체 확인 (Slave) 

[root@slave ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:20:cd:2e:e6:ca brd ff:ff:ff:ff:ff:ff
    inet 10.41.174.129/23 brd 10.41.175.255 scope global dynamic eth0
       valid_lft 945880850sec preferred_lft 945880850sec
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether f2:d7:cf:cc:bb:f0 brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.36/24 brd 127.255.255.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet 192.168.100.40/32 scope global eth1
       valid_lft forever preferred_lft forever




[root@slave ~]# mysql -uorchestrator -porchestrator -h 192.168.100.40
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 201
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave      |
+------------+
1 row in set (0.00 sec)

 

 

해당 테스트는 NCP 클라우드에서 진행 되었습니다, (Classic 환경) 

Keepalived는 vip alias 방식으로 통제를 하여 VPC환경에서는 사용이 불가능합니다..VPC환경에서는 pacemaker + corosync를 사용하시는 것을 권장드립니다,ㅎㅎ

저작자표시

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

[MySQL & MariaDB - Partition Exchange]  (0) 2024.01.04
[MySQL - 로컬 컴퓨터 to MySQL 서버 데이터 이관] use.MySQL workbench  (0) 2023.02.10
[MySQL - Orchestrator Takeover & Failover 테스트] part 2  (0) 2023.02.05
[MySQL - Orchestrator 구축] part 1  (2) 2023.02.05
[MySQL - Read/Write Split 부하분산 구성] use. ProxySQL  (0) 2023.01.27
DataBase/MySQL & MariaDB 관련 글
더 보기

티스토리툴바