HAProxy + MariaDB Galera Cluster 구성을 하고 테스트를 진행하니 문제가 발생해서 글을 남겨본다.
HAProxy
haproxy.cfg 설정파일에서 아래 옵션을 사용
option mysql-check user haproxy
이 옵션은 TCP 포트로의 접속가능 여부만 판단해서 health check를 한다. Galera Cluster의 경우 노드가 복구될 때 쿼리를 하면 아래 메시지가 출력된다.
WSREP has not yet prepared node for application use
따라서 이 경우는 HAProxy에서 노드를 살려줘서는 안된다.
MariaDB Galera Cluster
MariaDB [(none)] > show global status where variable_name='wsrep_local_state';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| wsrep_local_state | 4 |
+-------------------+-------+
우분투 20.04 에서 진행한다. 패키지관리자로 설치가 가능하다. SSL을 지원하는 2.x 버전이 설치된다.
apt install -y haproxy
root@haproxy1:~# haproxy --version
HA-Proxy version 2.0.29-0ubuntu1 2022/08/26 - https://haproxy.org/
Usage : haproxy [-f <cfgfile|cfgdir>]* [ -vdVD ] [ -n <maxconn> ] [ -N <maxpconn> ]
[ -p <pidfile> ] [ -m <max megs> ] [ -C <dir> ] [-- <cfgfile>*]
-v displays version ; -vv shows known build options.
-d enters debug mode ; -db only disables background mode.
-dM[<byte>] poisons memory with <byte> (defaults to 0x50)
-V enters verbose mode (disables quiet mode)
-D goes daemon ; -C changes to <dir> before loading files.
-W master-worker mode.
-Ws master-worker mode with systemd notify support.
-q quiet mode : don't display messages
-c check mode : only check config files and exit
-n sets the maximum total # of connections (uses ulimit -n)
-m limits the usable amount of memory (in MB)
-N sets the default, per-proxy maximum # of connections (0)
-L set local peer name (default to hostname)
-p writes pids of all children to this file
-de disables epoll() usage even when available
-dp disables poll() usage even when available
-dS disables splice usage (broken on old kernels)
-dG disables getaddrinfo() usage
-dR disables SO_REUSEPORT usage
-dr ignores server address resolution failures
-dV disables SSL verify on servers side
-sf/-st [pid ]* finishes/terminates old pids.
-x <unix_socket> get listening sockets from a unix socket
-S <bind>[,<bind options>...] new master CLI
router_id 값은 각 노드마다 다르다. priority 값은 마스터가 값이 더 커야 한다. virtual_ipaddress 값은 VIP로 마스터가 이 VIP를 바인딩하다 장애가 발생하면 다른 노드가 이 VIP를 바인딩해서 이중화가 가능하다.
서비스 재시작
systemctl restart keepalived.service
네트워크 확인
1번 노드에 VIP가 바인딩된 것이 보인다. 1번노드에서 haproxy 서비스를 중지시키면 2번 노드에 VIP가 바인딩되는 것을 확인할 수 있다.
root@haproxy1:/etc/haproxy# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether xx:xx:xx:xx:xx:xx brd ff:ff:ff:ff:ff:ff
inet 172.16.0.10/24 brd 172.16.0.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.16.0.5/32 scope global eth0
valid_lft forever preferred_lft forever
root@haproxy2:/etc/haproxy# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether xx:xx:xx:xx:xx:xx brd ff:ff:ff:ff:ff:ff
inet 172.16.0.11/24 brd 172.16.0.255 scope global eth0
valid_lft forever preferred_lft forever
TLS사용을 위해서 키를 생성한다. smtpserver.xml, imapserver.xml에서 사용한다.
/usr/local/james 위치에 설치기준
keytool -genkey -alias james -keyalg RSA -storetype PKCS12 -keystore /usr/local/james/conf/keystore
Enter keystore password:
Re-enter new password:
What is your first and last name?
[Unknown]: Jongwan Kim
What is the name of your organizational unit?
[Unknown]: None
What is the name of your organization?
[Unknown]: None
What is the name of your City or Locality?
[Unknown]: Seoul
What is the name of your State or Province?
[Unknown]: Guro-gu
What is the two-letter country code for this unit?
[Unknown]: KR
Is CN=Jongwan Kim, OU=None, O=None, L=Seoul, ST=Guro-gu, C=KR correct?
[no]: y
3. conf/smtpserver.xml
메일 발송을 위한 SMTP 설정을 한다. 25번 포트와 465번 포트설정이 각각 smtpserver 항목으로 있다.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2098 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> stop slave;
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='###.###.###.###',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='1234',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=2098;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G;
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: ###.###.###.###
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2098
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 906
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2098
Relay_Log_Space: 1215
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 3
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]>
