HA 구성 - (4) MHA

DB & MHA 구성

 

 

1. 호스트 설정(manager-master-slave)

$ cat /etc/hosts
172.16.173.138 manager
172.16.173.130 master
172.16.173.132 slave1
172.16.173.133 slave2
172.16.173.134 slave3

 

2. mha 다운로드

mha4mysql-node-0.57.tar.gz : manager, master, slave 설치

mha4mysql-manager-0.57.tar.gz : manager 설치

 

3. 패키지 설치(manager-master-slave)

$ yum install -y net-tools sysstat lrzsz lsof htop iftop rsync bzip2 unzip patch syslog
$ yum install -y perl-devel perl-CPAN perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Module-Install
$ yum update

 

# 패키지 설치 오류 발생

패키지 설치 오류 화면

 

# CentOS 9 Stream Repository 추가로 해결 할 수 있습니다.

$ dnf config-manager --set-enabled crb
$ dnf install epel-release epel-next-release

 

패키지 설치화면

 

4. OS mhauser 생성(manager-master-slave)

$ useradd -g mysql -d /home/mhauser -m -s /bin/bash mhauser
$ passwd mhauser

 

5. DB mhauser 생성(master-slave)

MariaDB [test]> create user mhauser@'172.16.173.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> create user mhauser@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> grant all privileges on . to 'mhauser'@'192.168.56.%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> grant all privileges on . to 'mhauser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

 

6. 폴더 생성(manager-master-slave)

[root@mac18-03 ~]# mkdir -p /mysql/mha
[root@mac18-03 ~]# mkdir -p /mha/scripts
[root@mac18-03 ~]# mkdir -p /mha/log
[root@mac18-03 ~]# chown -R mhauser:mysql /mha

 

7. 설치

# mha4mysql-node 압축풀기 및 설치(manager-master-slave)
$ tar -xvf mha4mysql-node-0.57.tar.gz -C /mysql/mha
$ cd /mysql/mha/mha4mysql-node-0.57
$ perl Makefile.PL
$ make
$ make install

# mha4mysql-manager 압축풀기 및 설치(manager)
$ tar -xvf mha4mysql-manager-0.57.tar.gz -C /mysql/mha
$ cd /mysql/mha/mha4mysql-manager-0.57
$ perl Makefile.PL
$ make
$ make install

설치 화면

8. 설정

(1) 설정 파일 복사(manager)

# 설정파일
$ cp /mysql/mha/mha4mysql-manager-0.57/samples/scripts/* /mha/scripts/.
$ cp /mysql/mha/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/mha.cnf
$ chown -R mhauser:mysql /etc/mha.cnf

 

(2) ln 설정(manager-master-slave)

[root@mha01 mha]# ln -s /mysql/local/mysql_8033/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@mha01 mha]# ln -s /mysql/local/mysql_8033/bin/mysql /usr/local/bin/mysql

 

(3) sudo 설정(manager-master-slave)

$ visudo
# 마지막줄에 기입
mhauser ALL=(ALL) NOPASSWD:/sbin/ifconfig

 

9. mha.cnf

$ vi /etc/mha.cnf

[server default]

#mysql
user=mhauser
password=123456

#ssl
ssh_user=mhauser

#replica
repl_user=replicator
repl_password=1234

#mha log
manager_workdir=/home/mhauser
manager_log=/home/mhauser/mha-manager.log

#remote mha log
remote_workdir=/home/mhauser

#binary bin log path
master_binlog_dir=/data/mysql_data
master_ip_online_change_script=/mha/scripts/master_ip_online_change
master_ip_failover_script=/mha/scripts/master_ip_failover

# master
[server1]
hostname=172.16.173.130
candidate_master=1

# slave1
[server2]
hostname=172.16.173.132
candidate_master=1

# slave2
[server3]
hostname=172.16.173.133
candidate_master=1

[server4] # slave3
hostname=172.16.173.134
candidate_master=1

- " [server4] # slave3 " 처럼 설정값과 주석이 공존 하는 라인은 주석을 삭제해 줍니다. 그렇지 않으면 에러가 발생됩니다.

 

10. SSL 설정(manager-master-slave)

(1) ssh-keygen

$ su - mhauser
$ ssh-keygen -t rsa

 

(2) manager

# manager->master
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.130

# manager->slave
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.132
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.133
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.134

 

(3) master

# master->manager
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.138

# manager->slave
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.132
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.133
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.134

 

(4) slave 1~3

# slave->manager
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.138

# slave->master
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.130

# slave->slave
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.132
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.133
$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@172.16.173.134

실행화면

 

(5) 권한 수정(manater-master-slave)

[mhauser@mha01 bin]$ cd ~/.ssh/
[mhauser@mha01 .ssh]$ chmod 400 authorized_keys

 

11. 스크립트  수정(manager)

$ vi /mha/scripts/master_ip_online_change

 

$ vi /mha/scripts/master_ip_failover

 

12. 테스트(manager)

(1) check ssh

[mhauser@mha01 ~]$ /usr/local/bin/masterha_check_ssh --conf=/etc/mha.cnf

check ssh 실행화면

 

(2) check repl

 - 테스트를 하면서 여러 오류가 있었습니다. 링크를 참고해 주세요.

[mhauser@mha01 ~]$ /usr/local/bin/masterha_check_repl --conf=/etc/mha.cnf

.

.

.

check repl 실행 화면

 

(3) switch master

[mhauser@mha01 ~]$ masterha_master_switch --master_stat=alive --conf=/etc/mha.cnf --orig_master_is_new_slave

.

.

.

switch master 실행 화면

 

(4) failover ( 실제 작동여부 테스트 )

# mha 실행

# status 확인
[mhauser@mha01 conf]$ /usr/local/bin/masterha_check_status --conf=/etc/mha.cnf
mha is stopped(2:NOT_RUNNING).

# mha 실행
[mhauser@mha01 conf]$ /usr/local/bin/masterha_manager --conf=/etc/mha.cnf &
[1] 77644
[mhauser@mha01 conf]$ Thu Jan 18 01:40:44 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jan 18 01:40:44 2024 - [info] Reading application default configuration from /etc/mha.cnf..
Thu Jan 18 01:40:44 2024 - [info] Reading server configuration from /etc/mha.cnf..

# status 확인
[mhauser@mha01 conf]$ /usr/local/bin/masterha_check_status --conf=/etc/mha.cnf
mha (pid:77644) is running(0:PING_OK), master:172.16.173.132

 

# stop master

[root@centOS09-02 ~]# mysql.server stop

 

# vi /home/mhauser/mha-manager.log

 

.

.

.

failover 로그

 

 

 

# CentOS stream 9에 mha 설치 관련한 마땅한 내용들을 찾지는 못했으나 전체적인 흐름과 설치는 다른 버전과 동일한 것으로 보였습니다. 다만 관련 패키지들 설치가 제대로 이뤄지지 않았고, 여러 다양한 오류에 대해 대부분 명확한 설명을 찾을 수가 없어서 많은 시간을 소비했던 것 같습니다.