close
프로필 사진

이상한금요일의 IT세상

github: @denev6
📁 Database/MySQL & MariaDB · 2024. 7. 29. fullscreen 넓게보기

[MariaDB] MHA 구성(Part 3. MHA 설정)

728x90

이전 포스트

 

[MariaDB] MHA 구성(Part 2. Replication 설정)

[MariaDB] MHA 구성(Part 1. MariaDB 10.11.8 설치) 2. Replication 설정basedir, datadir 설정 확인(master, slave)MariaDB [(none)]> select @@basedir;+------------------+| @@basedir |+------------------+| /usr/local/mysql |+------------------+1 row in

khyup.tistory.com

3. MHA 구성

VIP 생성(master)
  • vip는 mhamanager(192.168.100.118), maria1(192.168.100.119), maria2(192.168.100.120)와 중복되지 않는 IP로 지정
  • master, slave 모두 동일하게 만들고 master 쪽의 vip만 up시킵니다.
# ifconfig
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.119  netmask 255.255.255.0  broadcast 192.168.137.255
        inet6 fe80::20c:29ff:feee:af03  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:ee:af:03  txqueuelen 1000  (Ethernet)
        RX packets 529557  bytes 750163057 (715.4 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 70521  bytes 5432167 (5.1 MiB)
        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
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 117  bytes 14004 (13.6 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 117  bytes 14004 (13.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099<UP,BROADCAST,MULTICAST>  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:d7:d4:02  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

# cd /etc/sysconfig/network-scripts/

# vi ifcfg-ens160:1
DEVICE=ens160:1
BOOTPROTO=static
IPADDR=192.168.100.121
PREFIX=24
ONBOOT=no
ONPARENT=no

# ifconfig ens160:1 192.168.100.121 up

# ifconfig
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.119  netmask 255.255.255.0  broadcast 192.168.137.255
        inet6 fe80::20c:29ff:feee:af03  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:ee:af:03  txqueuelen 1000  (Ethernet)
        RX packets 529965  bytes 750197873 (715.4 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 70788  bytes 5460701 (5.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.121  netmask 255.255.255.0  broadcast 192.168.100.255
        ether 00:0c:29:ee:af:03  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 117  bytes 14004 (13.6 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 117  bytes 14004 (13.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099<UP,BROADCAST,MULTICAST>  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:d7:d4:02  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
VIP 생성(slave)
  • vip는 mhamanager(192.168.100.118), maria1(192.168.100.119), maria2(192.168.100.120)와 중복되지 않는 IP로 지정
  • master, slave 모두 동일하게 만들고 master 쪽의 vip만 up시킵니다.
# cd /etc/sysconfig/network-scripts/
# vi ifcfg-ens160:1

DEVICE=ens160:1
BOOTPROTO=static
IPADDR=192.168.100.121
PREFIX=24
ONBOOT=no
ONPARENT=no

 

SSH 공개키 생성(manager, master, slave)
  • root 로그인 허용 여부 확인
# cat /etc/ssh/sshd_config | grep PermitRootLogin

PermitRootLogin yes
# the setting of "PermitRootLogin without-password".
  • 공개키 생성
# ssh-keygen -t rsa

Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:I41wPuoAeP05bOCdC/1uyXiLnESk4bbzwFZcG9utSvo root@maria1
The key's randomart image is:
+---[RSA 3072]----+
|                 |
|                 |
|    o o o        |
|.  o O + = .     |
|o . * O S . .    |
| o + @ = . .     |
|  . X @o...      |
|   + O.O=.       |
|    . B=E.       |
+----[SHA256]-----+
  • 공개키 생성 확인
# ls -al ~/.ssh/

total 12
drwx------  2 root root   38 Feb 20 15:47 .
dr-xr-x---. 7 root root 4096 Feb 20 15:47 ..
-rw-------  1 root root 2590 Feb 20 15:47 id_rsa
-rw-r--r--  1 root root  565 Feb 20 15:47 id_rsa.pub
  • ssh 디렉토리 mode 설정 및 authorized_keys 생성
# 
chmod 700 ~/.ssh
chmod 600 ~/.ssh/id_rsa
chmod 644 ~/.ssh/id_rsa.pub
cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys
  • authorized_keys 생성 확인
# cat authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCgBuYrApVSqEK0es34EuAWeeGyJ3RZVKcPI02Axzrema4CoZcg3+JIKtG3OKJ1CDRZivE1ic0sVV2r5NBHKtD2qS4VzRQBQbV8lCNSQFe2aLUeN/vwvmwrCqBLyASqaIAAX/oBPc74qzJHlT0oadmatooYOSAFqBdKORMAimutqaRi66d3aAr42wOwTHaVaThAjXkXVMvgtQvdQbc+20NsFaH2KA1hLJ/5wkr8sYHy8xEbUQauK461U48DXW/9dg/HwjRySxFE3jnDClXAPJdGGFQvGbM/SL6YqvArJjoR60yyysgH6WjchpT0m1liEHcBTIBqSnDm2qrRfPAOgk9ez2FYg8fgFT3Elu29XNfY7wFOQlLvcK5iA6Zon4TcSckvpy/oHlykGBW0ndNR1G6tg02nXl00hT2LNX3n5ULYMsaLluW42rIlL6GUmdpNuIDDj+jByfX0Pt9SD0v7AkKhlQ8AwaYqRoF+hZhCsLYI4eAwP7AxTsPDkgpXnBkUuHU= root@maria1
SSH 공개키 공유(manager, master, slave)
  • manager, master, slave 서버의 authorized_keys 파일에 모든 서버의 ssh-rsa(공개키)를 취합
    • manager 서버의 공개키 -> master, slave에 추가
    • master 서버의 공개키 -> slave, manager에 추가
    • slave 서버의 공개키 -> master, manager에 추가
# cat authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC0Q1kJXdrJtmJ383/+szSXnUqNa6WdQw88lK7FXdcqWCWjptQZIRl1PJirvze9hZyo0sz5+ZgPGZY5l9YXpISaYEs7vBK805mOyMKXFzIzXuQEGf8PV0lVWMb/zAru1Gh20xwrvyU4IjIWwBVBrwQwGH/gfBIMO2/KQ9dukdmBgEGjIpEY2gRoj7xC/wGCfunsMTqbPb3i7wbEylss/P8rdFVNan6xJiyHkRXFWsLuS6RjOIa120f3Z2z/eBi7Knja6R3O+Rx4Ua7VWOH1rc0pV+KofTXUFO4dNA9yO/vRBKeJLYVtIHLrKsurP0ZOJ/OLUq9I0pyqJiDSEKl+tjeCviSbOWQmGSLHqe4ji4x1jwtaS28kz2MYGrWka2ArbaC2H++CtUFoNGEjwMr597CaQZVrG4iNUjGldxOzz7cDsvYWK3fZ3IT8eMJ0dwk9u2dYes6RVR5GToOxdSMdDyqrSnyHykQvY7fjLibiKJkAu/b3eGnvZ6wy7n/a1AFQa/c= root@mhamanager

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCgBuYrApVSqEK0es34EuAWeeGyJ3RZVKcPI02Axzrema4CoZcg3+JIKtG3OKJ1CDRZivE1ic0sVV2r5NBHKtD2qS4VzRQBQbV8lCNSQFe2aLUeN/vwvmwrCqBLyASqaIAAX/oBPc74qzJHlT0oadmatooYOSAFqBdKORMAimutqaRi66d3aAr42wOwTHaVaThAjXkXVMvgtQvdQbc+20NsFaH2KA1hLJ/5wkr8sYHy8xEbUQauK461U48DXW/9dg/HwjRySxFE3jnDClXAPJdGGFQvGbM/SL6YqvArJjoR60yyysgH6WjchpT0m1liEHcBTIBqSnDm2qrRfPAOgk9ez2FYg8fgFT3Elu29XNfY7wFOQlLvcK5iA6Zon4TcSckvpy/oHlykGBW0ndNR1G6tg02nXl00hT2LNX3n5ULYMsaLluW42rIlL6GUmdpNuIDDj+jByfX0Pt9SD0v7AkKhlQ8AwaYqRoF+hZhCsLYI4eAwP7AxTsPDkgpXnBkUuHU= root@maria1

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCwR9wiD4povpgR5TL4JFGWdD2rCxZ05HGeUD1nH4XrMXarKSZQmRgCu5Ox0tEiH/rCjPJBKjTz8GMYOcuYFeLdwThPp4eILianngNCPnkXCIrQKPyDDJRPjEsh7/3QF4sj8C3o4lb6xsrx0uqc0YavugMfM6FDo+dYVZM0TsNBkN648wFtl1GLFc72cPlcRQy+t2X3yHGKK5h+GP/Cu/edd2s/GqJzHQ659lHVL9hnWSQFTtFmJLlEEpYmQAivWnoWcG8ADYip5B1rtbMPM7tGFVVVkqGEAzk7Q3PQCfhMhKI3wGaDktXAHssQjHvFIgQ//tRXPqH2eeAUHVuuWHikz7nPTteK9vT5+h1DMe0QNkmz6rWoEK954AWMydGDoxI5asmO2datHh2L4poNurw7jICKacFJKyZ0EIJ7dMB/6j/qAZJAZuaMtlgsqLPQd86MC+XTj2XXZpO4BuLJ0tcYxSneaUNfhPBm5vS/AL/T2avRTTfsflROqRScgGHDATM= root@maria2
  • ssh 데몬 재시작
# systemctl restart sshd
SSH 접속 확인(manager, master, slave)
  • 각 서버 간에 ssh 접속 확인
# ssh 192.168.100.118

The authenticity of host '192.168.137.170 (192.168.137.170)' can't be established.
ECDSA key fingerprint is SHA256:kQ0ImgtPRMdDLOaURI+WhGwCVmIk2AzS6bAb8ZlnZgU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.137.170' (ECDSA) to the list of known hosts.
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Tue Feb 20 13:23:02 2024 from 192.168.137.1


# ssh 192.168.100.119

The authenticity of host '192.168.137.171 (192.168.137.171)' can't be established.
ECDSA key fingerprint is SHA256:kQ0ImgtPRMdDLOaURI+WhGwCVmIk2AzS6bAb8ZlnZgU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.137.171' (ECDSA) to the list of known hosts.
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Tue Feb 20 13:23:02 2024 from 192.168.137.1


# ssh 192.168.100.120

The authenticity of host '192.168.137.172 (192.168.137.172)' can't be established.
ECDSA key fingerprint is SHA256:OigBv8WyP9wznS/LcqVrtAq63KhybNkvF368nr74m64.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.137.172' (ECDSA) to the list of known hosts.
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Tue Feb 20 13:15:11 2024 from 192.168.137.1

 

mha4mysql-node 컴파일 & 인스톨(master, master, slave)
  • 압축 풀기
# tar -zxvf mha4mysql-node-0.58.tar.gz
  • 컴파일
# cd mha4mysql-node-0.58/
# perl Makefile.PL

*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.641)
- DBD::mysql ...loaded. (4.046)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Generating a Unix-style Makefile
Writing Makefile for mha4mysql::node
Writing MYMETA.yml and MYMETA.json
  • 인스톨
# make && make install

cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/purge_relay_logs blib/script/purge_relay_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/save_binary_logs blib/script/save_binary_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
Manifying 4 pod documents
Manifying 4 pod documents
Installing /usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing /usr/local/share/perl5/MHA/BinlogManager.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing /usr/local/share/perl5/MHA/NodeConst.pm
Installing /usr/local/share/perl5/MHA/NodeUtil.pm
Installing /usr/local/share/perl5/MHA/SlaveUtil.pm
Installing /usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing /usr/local/share/man/man1/apply_diff_relay_logs.1
Installing /usr/local/share/man/man1/filter_mysqlbinlog.1
Installing /usr/local/share/man/man1/purge_relay_logs.1
Installing /usr/local/share/man/man1/save_binary_logs.1
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/purge_relay_logs
Installing /usr/local/bin/save_binary_logs
Appending installation info to /usr/lib64/perl5/perllocal.pod

 

mha4mysql-manager 컴파일 & 인스톨(manager)
  • 압축 풀기
# tar -zxvf mha4mysql-manager-0.58.tar.gz
  • 필요 패키지 설치
# 
cpan YAML 
cpan Parallel::ForkManager
 
# 
perl -MCPAN -e "install File::Remove"
perl -MCPAN -e "install Build"
...
중간에 y 2번
...
 
# 
perl -MCPAN -e "install Module::Install"
perl -MCPAN -e "install Net::Telnet"
perl -MCPAN -e "install Log::Dispatch"
  • 컴파일
# cd mha4mysql-manager-0.58/
# perl Makefile.PL

*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.641)
- DBD::mysql            ...loaded. (4.046)
- Time::HiRes           ...loaded. (1.9758)
- Config::Tiny          ...loaded. (2.24)
- Log::Dispatch         ...loaded. (2.71)
- Parallel::ForkManager ...loaded. (2.02)
- MHA::NodeConst        ...loaded. (0.58)
*** Module::AutoInstall configuration finished.
Generating a Unix-style Makefile
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
  • 인스톨
# make && make install

cp lib/MHA/ManagerUtil.pm blib/lib/MHA/ManagerUtil.pm
cp lib/MHA/ManagerConst.pm blib/lib/MHA/ManagerConst.pm
cp lib/MHA/DBHelper.pm blib/lib/MHA/DBHelper.pm
cp lib/MHA/MasterFailover.pm blib/lib/MHA/MasterFailover.pm
cp lib/MHA/Server.pm blib/lib/MHA/Server.pm
cp lib/MHA/ManagerAdminWrapper.pm blib/lib/MHA/ManagerAdminWrapper.pm
cp lib/MHA/ManagerAdmin.pm blib/lib/MHA/ManagerAdmin.pm
cp lib/MHA/MasterMonitor.pm blib/lib/MHA/MasterMonitor.pm
cp lib/MHA/SSHCheck.pm blib/lib/MHA/SSHCheck.pm
cp lib/MHA/Config.pm blib/lib/MHA/Config.pm
cp lib/MHA/HealthCheck.pm blib/lib/MHA/HealthCheck.pm
cp lib/MHA/FileStatus.pm blib/lib/MHA/FileStatus.pm
cp lib/MHA/MasterRotate.pm blib/lib/MHA/MasterRotate.pm
cp lib/MHA/ServerManager.pm blib/lib/MHA/ServerManager.pm
cp bin/masterha_check_repl blib/script/masterha_check_repl
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_repl
cp bin/masterha_check_ssh blib/script/masterha_check_ssh
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_ssh
cp bin/masterha_check_status blib/script/masterha_check_status
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_status
cp bin/masterha_conf_host blib/script/masterha_conf_host
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_conf_host
cp bin/masterha_manager blib/script/masterha_manager
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_manager
cp bin/masterha_master_monitor blib/script/masterha_master_monitor
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_monitor
cp bin/masterha_master_switch blib/script/masterha_master_switch
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_switch
cp bin/masterha_secondary_check blib/script/masterha_secondary_check
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_secondary_check
cp bin/masterha_stop blib/script/masterha_stop
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_stop
Manifying 9 pod documents
Manifying 9 pod documents
Installing /usr/local/share/perl5/MHA/ManagerUtil.pm
Installing /usr/local/share/perl5/MHA/ManagerConst.pm
Installing /usr/local/share/perl5/MHA/DBHelper.pm
Installing /usr/local/share/perl5/MHA/MasterFailover.pm
Installing /usr/local/share/perl5/MHA/Server.pm
Installing /usr/local/share/perl5/MHA/ManagerAdminWrapper.pm
Installing /usr/local/share/perl5/MHA/ManagerAdmin.pm
Installing /usr/local/share/perl5/MHA/MasterMonitor.pm
Installing /usr/local/share/perl5/MHA/SSHCheck.pm
Installing /usr/local/share/perl5/MHA/Config.pm
Installing /usr/local/share/perl5/MHA/HealthCheck.pm
Installing /usr/local/share/perl5/MHA/FileStatus.pm
Installing /usr/local/share/perl5/MHA/MasterRotate.pm
Installing /usr/local/share/perl5/MHA/ServerManager.pm
Installing /usr/local/share/man/man1/masterha_check_repl.1
Installing /usr/local/share/man/man1/masterha_check_ssh.1
Installing /usr/local/share/man/man1/masterha_check_status.1
Installing /usr/local/share/man/man1/masterha_conf_host.1
Installing /usr/local/share/man/man1/masterha_manager.1
Installing /usr/local/share/man/man1/masterha_master_monitor.1
Installing /usr/local/share/man/man1/masterha_master_switch.1
Installing /usr/local/share/man/man1/masterha_secondary_check.1
Installing /usr/local/share/man/man1/masterha_stop.1
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_manager
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_master_switch
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_stop
Appending installation info to /usr/lib64/perl5/perllocal.pod

 

MHA 관련 alias 설정(manager)
  • root의 profile에 MHA 관련 alias 설정
# vi .bash_profile

...
alias sshcheck='/usr/local/bin/masterha_check_ssh --conf=/etc/mha.cnf'
 
alias replcheck='/usr/local/bin/masterha_check_repl --conf=/etc/mha.cnf'
 
alias start='/usr/local/bin/masterha_manager --conf=/etc/mha.cnf &'
 
alias stop='/usr/local/bin/masterha_stop --conf=/etc/mha.cnf' 
 
alias status='/usr/local/bin/masterha_check_status --conf=/etc/mha.cnf'
 
alias log='tail -f /var/log/masterha/app1/app1.log'
...
# . .bash_profile

 

mha.cnf 파일 생성(manager)
# cp /root/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/mha.cnf
# mkdir /mha
  • 아래 내용 입력
# vi /etc/mha.cnf

user=mha
password=mha
ssh_user=root
 
repl_user=rep
repl_password=rep
 
manager_workdir=/mha
manager_log=/mha/manager.log
remote_workdir=/mha
 
master_binlog_dir=/data/mariadb/log-bin
 
master_ip_online_change_script=/mha/scripts/master_ip_online_change
master_ip_failover_script=/mha/scripts/master_ip_failover
 
ping_interval=5
 
[server1]
hostname=maria1
port=3306
candidate_master=1
ignore_fail=1
 
[server2]
hostname=maria2
port=3306
candidate_master=1
ignore_fail=1

 

remote_workdir 및 MHA 유저 생성(master, slave)
  • /etc/mha.cnf 파일 내용 중 remote_workdir 파라미터 경로 생성
# mkdir -p /mha

 

mha 전용 user 생성(master, slave)
MariaDB [(none)]> grant all privileges on *.* to 'mha'@'%' identified by 'mha';
MariaDB [(none)]> grant all privileges on *.* to 'mha'@'localhost' identified by 'mha';
MariaDB [(none)]> grant all privileges on *.* to 'mha'@'192.168.137.170' identified by 'mha';
MariaDB [(none)]> grant all privileges on *.* to 'mha'@'192.168.137.171' identified by 'mha';
MariaDB [(none)]> grant all privileges on *.* to 'mha'@'192.168.137.172' identified by 'mha';
MariaDB [(none)]> flush privileges;
  • user 생성 확인
MariaDB [(none)]> use mysql
MariaDB [mysql]> select host, user, password from user;
+-----------+-------------+-------------------------------------------+
| Host      | User        | Password                                  |
+-----------+-------------+-------------------------------------------+
| localhost | mariadb.sys |                                           |
| localhost | root        | invalid                                   |
| localhost | maria       | invalid                                   |
|           | PUBLIC      |                                           |
| localhost |             |                                           |
| maria1    |             |                                           |
| %         | rep         | *9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C |
| %         | mha         | *F4C9AC49A736981AE2739FC2F4A1FD92B4F07929 |
| localhost | mha         | *F4C9AC49A736981AE2739FC2F4A1FD92B4F07929 |
+-----------+-------------+-------------------------------------------+
9 rows in set (0.001 sec)

 

VIP 변경 스크립트 설정(manager)
# mkdir /mha/scripts
# cp /root/mha4mysql-manager-0.58/samples/scripts/master_ip_online_change /mha/scripts/master_ip_online_change
# vi /mha/scripts/master_ip_online_change
150, 151, 152, 245, 246, 247, 248 라인에 주석 추가
 
:set number
:150
 
    149       ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
    150       # $orig_master_handler->disable_log_bin_local();
    151       # print current_time_us() . " Drpping app user on the orig master..\n";
    152       # FIXME_xxx_drop_app_user($orig_master_handler);
 
:245
 
    244       ## Creating an app user on the new master
    245       # print current_time_us() . " Creating app user on the new master..\n";
    246       # FIXME_xxx_create_app_user($new_master_handler);
    247       # $new_master_handler->enable_log_bin_local();
    248       # $new_master_handler->disconnect();
 
248 라인 밑에 아래 스크립트 추가 후 저장
      if ( $new_master_ip eq "192.168.100.119" ) {
        system("/bin/sh /mha/scripts/master_vip_up.sh");
      }
      elsif ( $new_master_ip eq "192.168.100.120" ) {
        system("/bin/sh /mha/scripts/slave_vip_up.sh");
      }
      else {}

 

Failover 스크립트 설정(manager)
# cp /root/mha4mysql-manager-0.58/samples/scripts/master_ip_failover /mha/scripts/master_ip_failover
# vi /mha/scripts/master_ip_failover
87, 88, 89, 90, 93 라인 주석 처리
 
:set number
:87
 
     86       ## Creating an app user on the new master
     87       # print "Creating app user on the new master..\n";
     88       # FIXME_xxx_create_user( $new_master_handler->{dbh} );
     89       # $new_master_handler->enable_log_bin_local();
     90       # $new_master_handler->disconnect();
     91
     92       ## Update master ip on the catalog database, etc
     93       # FIXME_xxx;
 
93 라인 밑에 아래 스크립트 추가 후 저장
      if ( $new_master_ip eq "192.168.137.171" ) {
        system("/bin/sh /mha/scripts/master_vip_up.sh");
      }
      elsif ( $new_master_ip eq "192.168.137.172" ) {
        system("/bin/sh /mha/scripts/slave_vip_up.sh");
      }
      else {}

 

master_vip_up_sh 생성(manager)
# vi /mha/scripts/master_vip_up.sh
 
#!/bin/sh
# master : 192.168.100.119
# slave : 192.168.100.120
# VIP network ens160:1
ssh root@192.168.100.120 sudo /usr/sbin/ifconfig ens160:1 192.168.100.121 down
ssh root@192.168.100.119 sudo /usr/sbin/ifconfig ens160:1 192.168.100.121 up

 

slave_vip_up_sh 생성(manager)
# vi /mha/scripts/slave_vip_up.sh
 
#!/bin/sh
# master : 192.168.100.119
# slave : 192.168.100.120
# VIP network ens160:1
ssh root@192.168.100.119 sudo /usr/sbin/ifconfig ens160:1 192.168.137.121 down
ssh root@192.168.100.120 sudo /usr/sbin/ifconfig ens160:1 192.168.137.121 up

 

SSH 확인(manager)
# sshcheck

Tue Feb 20 17:33:51 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Feb 20 17:33:51 2024 - [info] Reading application default configuration from /etc/mha.cnf..
Tue Feb 20 17:33:51 2024 - [info] Reading server configuration from /etc/mha.cnf..
Tue Feb 20 17:33:51 2024 - [info] Starting SSH connection tests..
Tue Feb 20 17:33:52 2024 - [debug]
Tue Feb 20 17:33:51 2024 - [debug]  Connecting via SSH from root@maria1(192.168.137.171:22) to root@maria2(192.168.137.172:22)..
Tue Feb 20 17:33:52 2024 - [debug]   ok.
Tue Feb 20 17:33:52 2024 - [debug]
Tue Feb 20 17:33:52 2024 - [debug]  Connecting via SSH from root@maria2(192.168.137.172:22) to root@maria1(192.168.137.171:22)..
Tue Feb 20 17:33:52 2024 - [debug]   ok.
Tue Feb 20 17:33:52 2024 - [info] All SSH connection tests passed successfully.
Use of uninitialized value in exit at /usr/local/bin/masterha_check_ssh line 44.

 

Replication check 사전 설정(master, slave)
  • replcheck 전 심볼릭 링크 걸어주기
    (/usr/local/mysql/는 basedir입니다)
# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
replication check 수행(manager)
  • 오류 발생!
# replcheck
Tue Feb 20 17:34:03 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Feb 20 17:34:03 2024 - [info] Reading application default configuration from /etc/mha.cnf..
Tue Feb 20 17:34:03 2024 - [info] Reading server configuration from /etc/mha.cnf..
Tue Feb 20 17:34:03 2024 - [info] MHA::MasterMonitor version 0.58.
Tue Feb 20 17:34:04 2024 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. Redundant argument in sprintf at /usr/local/share/perl5/MHA/NodeUtil.pm line 201.
Tue Feb 20 17:34:04 2024 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Feb 20 17:34:04 2024 - [info] Got exit code 1 (Not master dead).
 
MySQL Replication Health is NOT OK!
  • mariadb에서는 super_read_only 파라미터를 사용하지 않아 오류 발생
    → 관련 스크립트 주석 처리(work around 방법)
  • manager 서버에 대해 아래와 같이 수정
# chmod +w /usr/local/share/perl5/MHA/NodeUtil.pm
# vi /usr/local/share/perl5/MHA/NodeUtil.pm

194 201 아래 줄에 195, 202, 203와 같이 추가
:set number
    193 sub parse_mysql_version($) {
    194   my $str = shift;
    195   ($str) = $str =~ m/^[^-]*/g;
    196   my $result = sprintf( '%03d%03d%03d', $str =~ m/(\d+)/g );
    197   return $result;
    198 }
    199
    200 sub parse_mysql_major_version($) {
    201   my $str = shift;
    202   ($str) = $str =~ m/^[^-]*/g;
    203   my $result = sprintf( '%03d%03d%03d', $str =~ m/(\d+)/g );
    204   #my $result = sprintf( '%03d%03d', $str =~ m/(\d+)/g );
    205   return $result;
    206 }
  • 모든 서버(manager, master, slave)에 대해 아래와 같이 수정
# chmod +w /usr/local/share/perl5/MHA/SlaveUtil.pm
# vi /usr/local/share/perl5/MHA/SlaveUtil.pm

244, 262, 275행 수정
  :244
    #my $sth = $dbh->prepare("SELECT \@\@global.super_read_only as Value");
    my $sth = $dbh->prepare("SELECT 0 as Value");
  :262
    #$dbh->do("SET GLOBAL super_read_only=off;");
  :275
    #$dbh->do("SET GLOBAL super_read_only=on;");
    $dbh->do("SET GLOBAL read_only=on;");
  • replication 재수행(manager)
    → 정상 수행
# replcheck
Wed Feb 21 13:40:07 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Feb 21 13:40:07 2024 - [info] Reading application default configuration from /etc/mha.cnf..
Wed Feb 21 13:40:07 2024 - [info] Reading server configuration from /etc/mha.cnf..
Wed Feb 21 13:40:07 2024 - [info] MHA::MasterMonitor version 0.58.
Wed Feb 21 13:40:08 2024 - [info] GTID failover mode = 0
Wed Feb 21 13:40:08 2024 - [info] Dead Servers:
Wed Feb 21 13:40:08 2024 - [info] Alive Servers:
Wed Feb 21 13:40:08 2024 - [info]   maria1(192.168.137.171:3306)
Wed Feb 21 13:40:08 2024 - [info]   maria2(192.168.137.172:3306)
Wed Feb 21 13:40:08 2024 - [info] Alive Slaves:
Wed Feb 21 13:40:08 2024 - [info]   maria2(192.168.137.172:3306)  Version=10.11.7-MariaDB-log (oldest major version between slaves) log-bin:enabled
Wed Feb 21 13:40:08 2024 - [info]     Replicating from 192.168.137.171(192.168.137.171:3306)
Wed Feb 21 13:40:08 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Feb 21 13:40:08 2024 - [info] Current Alive Master: maria1(192.168.137.171:3306)
Wed Feb 21 13:40:08 2024 - [info] Checking slave configurations..
Wed Feb 21 13:40:08 2024 - [warning]  relay_log_purge=0 is not set on slave maria2(192.168.137.172:3306).
Wed Feb 21 13:40:08 2024 - [info] Checking replication filtering settings..
Wed Feb 21 13:40:08 2024 - [info]  binlog_do_db= , binlog_ignore_db=
Wed Feb 21 13:40:08 2024 - [info]  Replication filtering check ok.
Wed Feb 21 13:40:08 2024 - [info] GTID (with auto-pos) is not supported
Wed Feb 21 13:40:08 2024 - [info] Starting SSH connection tests..
Wed Feb 21 13:40:10 2024 - [info] All SSH connection tests passed successfully.
Wed Feb 21 13:40:10 2024 - [info] Checking MHA Node version..
Wed Feb 21 13:40:10 2024 - [info]  Version check ok.
Wed Feb 21 13:40:10 2024 - [info] Checking SSH publickey authentication settings on the current master..
Wed Feb 21 13:40:10 2024 - [info] HealthCheck: SSH to maria1 is reachable.
Wed Feb 21 13:40:10 2024 - [info] Master MHA Node version is 0.58.
Wed Feb 21 13:40:10 2024 - [info] Checking recovery script configurations on maria1(192.168.137.171:3306)..
Wed Feb 21 13:40:10 2024 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mariadb/log-bin --output_file=/mha/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000006
Wed Feb 21 13:40:10 2024 - [info]   Connecting to root@192.168.137.171(maria1:22)..
  Creating /mha if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mariadb/log-bin, up to mysql-bin.000006
Wed Feb 21 13:40:11 2024 - [info] Binlog setting check done.
Wed Feb 21 13:40:11 2024 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Feb 21 13:40:11 2024 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=maria2 --slave_ip=192.168.137.172 --slave_port=3306 --workdir=/mha --target_version=10.11.7-MariaDB-log --manager_version=0.58 --relay_log_info=/data/mariadb/master/relay-log.info  --relay_dir=/data/mariadb/master/  --slave_pass=xxx
Wed Feb 21 13:40:11 2024 - [info]   Connecting to root@192.168.137.172(maria2:22)..
  Checking slave recovery environment settings..
    Opening /data/mariadb/master/relay-log.info ... ok.
    Relay log found at /data/mariadb/log-bin, up to relay_log.000002
    Temporary relay log file is /data/mariadb/log-bin/relay_log.000002
    Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    Testing mysql connection and privileges..
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Feb 21 13:40:11 2024 - [info] Slaves settings check done.
Wed Feb 21 13:40:11 2024 - [info]
maria1(192.168.137.171:3306) (current master)
 +--maria2(192.168.137.172:3306)
 
Wed Feb 21 13:40:11 2024 - [info] Checking replication health on maria2..
Wed Feb 21 13:40:11 2024 - [info]  ok.
Wed Feb 21 13:40:11 2024 - [info] Checking master_ip_failover_script status:
Wed Feb 21 13:40:11 2024 - [info]   /mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=maria1 --orig_master_ip=192.168.137.171 --orig_master_port=3306
Wed Feb 21 13:40:11 2024 - [info]  OK.
Wed Feb 21 13:40:11 2024 - [warning] shutdown_script is not defined.
Wed Feb 21 13:40:11 2024 - [info] Got exit code 0 (Not master dead).
 
MySQL Replication Health is OK.

 

MHA 시작(manager)
# start

# ps -ef | grep mha

avahi        806       1  0 05:45 ?        00:00:00 avahi-daemon: running [mhamanager.local]
root       78745   74968  1 13:43 pts/1    00:00:00 perl /usr/local/bin/masterha_manager --conf=/etc/mha.cnf
root       78783   74968  0 13:43 pts/1    00:00:00 grep --color=auto mha

# status

mha (pid:78745) is running(0:PING_OK), master:maria1

다음 포스트

 

728x90

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

[MySQL/MariaDB] 데이터베이스/테이블별 용량 확인 쿼리  (0) 2025.11.18
[MariaDB] MHA 구성(Part 4, 5. Failover Test, Switchover Test)  (0) 2024.07.30
[MariaDB] MHA 구성(Part 1. MariaDB 10.11.8 설치)  (0) 2024.07.26
[MySQL] LRU 리스트, Flush 리스트, Free 리스트  (0) 2024.07.22
[MySQL] InnoDB 스토리지 엔진 아키텍처  (0) 2024.07.10
📁 Database/MySQL & MariaDB 관련 글
더 보기
SKIN: Copyright © 쭈미로운 생활 All rights reserved. Designed by JJuum.
and Current skin "dev-roo" is modified by Jin.

티스토리툴바