1. 환경
Centos7 minimal / mariadb 10.6.9 / mha 0.57
- yum repository : Centos7 everyting
[Master] : 192.168.2.215
[Manager] : 192.168.2.216
[Slave] : 192.168.2.217
2. host 등록
[Manager, Master , Slave]
# vi /etc/hosts
192.168.2.215 manager --Manager IP
192.168.2.216 master --Master IP
192.168.2.217 slave --Slave IP3. 필요파일
[Manager, Master , Slave]
- mha-manager , mha-node
mha download : https://github.com/lzimd/mha-rpms/find/master
- 인터넷이 안되는 환경에서 진행하므로
추가적인 rpm을 모아둔 파일
*rpm dependency파일
4. 필요 패키지 설치
[Manager, Master , Slave]
설치에 필요한 rpm을 한 dir에 모아 local repository로 사용하여
yum 설치시 종속 rpm들을 같이 설치하게함
1. local repositroy 등록시 createrepo 필요
- centos7-minimal.iso mount했을때 minmal 에는 없어 Centos-everyting 으로 repository 만듬.
# repo 디렉토리생성
# mkdir repo
# dependency tar 압축 해제
# tar -xvf dependency.tar -C /repo
# createrepo설치
# yum install createrepo -y
# repository 등록
# createrepo /repo/dependency
vi /etc/yum.repo.d/dependency.repo
[rpmRepo]
name=localRepo
baseurl=file:///repo/dependency
enabled=1
gpgcheck=0
# yum clean all
# repository 등록 확인
# yum repolist2. 패키지 설치
# yum install net-tools sysstat lrzsz lsof htop iftop rsync bzip2 unzip patch syslog -y
# yum install perl-devel perl-CPAN perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Module-Install -y 5. replication 설정
[Master , Slave]
1. 유저생성
# mariadb -u root -p
# rep 유저생성
MariaDB [(none)]> CREATE USER 'rep'@'%' IDENTIFIED BY 'rep';
# 권한 부여
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rep';
MariaDB [(none)]> flush privileges;2. server id 확인
[Master]
# mariadb -u root -p
MariaDB [(none)]> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.001 sec)[Slave]
# mariadb -u root -p
MariaDB [(none)]> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.001 sec)3. replication 설정
- master서버의 로그 파일 , 위치, port 확인
[Master]
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 5037 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show variables like 'port' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.001 sec)
- slave 서버에 설정
[Slave]
MariaDB [(none)]> change master to master_host ='192.168.2.216',
-> master_user='rep',
-> master_password='rep',
-> master_port=3306,
-> master_log_file='mysql-bin.000002'
-> ,master_log_pos=5037,
-> master_connect_retry=10;
Query OK, 0 rows affected (0.278 sec)
#slave 시작
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)
#slave 상태확인
MariaDB [(none)]> show slave status \G;
Slave_* 세 항목 확인
- Master 서버에서 slave 세션 접속 확인
[Master]
#MariaDB [(none)]> show processlist;- Master 서버에 database 와 테이블을 생성하여 slave에 replica되었는지 확인해보기
※ error 발생시
* Error 1
```java
2022-09-07 15:25:35 0 [ERROR] Error reading master configuration
2022-09-07 15:25:35 0 [ERROR] Failed to initialize the master info structure- 해결
# slave 설정 reset
[Slave]
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.000 sec)
- Error 2
masterd의 로그파일 명 및 위치가 잘못 잘못되어있을떄 발생
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
...
Slave_IO_Running: No
...
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
- 해결
[Master}
MariaDB [(none)]> reset master;
MariaDB [(none)]> show master status;
[Slave]
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.000 sec)
# master status에서 바뀐 로그의 파일과 위치를 적용해서
다시 change master to ... 실행
MariaDB [(none)]> start slave;6. MHA
1. 설정
- mariadb mha 계정생성
replication 설정을 해놨으므로 Master에서 생성하면 Slave에서도 자동 복제됨
[Master]
MariaDB [(none)]> create user 'mha'@'%' identified by 'mha';
Query OK, 0 rows affected (0.059 sec)
MariaDB [(none)]> grant all privileges on *.* to 'mha'@'%' identified by 'mha';
Query OK, 0 rows affected (0.064 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.040 sec)- server mha 계정생성
[Manager, Master, Slave]
# groupadd mysql
# useradd -g mysql -d /home/mhauser -m -s /bin/bash mhauser
# passwd mhauser
-g : 그룹지정
-d : 홈디렉토리 지정
-s :사용할 shell 지정
- mha 폴더생성
[Manager, Master, Slave]
# mkdir -p /mha/scripts
# chown -R mhauser:mysql /mha- MHA node 설치
[Manager, Master, Slave]
mha4mysql-node-0.57.tar.gz 파일이 있는곳으로 이동
# 원하는곳에 압축 해제
# tar xvzf mha4mysql-node-0.57.tar.gz -C /apps/utils/mariadb
# cd /apps/utils/mariadb/mha4mysql-node-0.57
# perl Makefile.PL
# make
# make install- MHA Manager 설치
[Manager]
mha4mysql-manager-0.57.tar.gz 파일이 있는곳으로 이동
# tar xvzf mha4mysql-manager-0.57.tar.gz -C /apps/utils/mariadb/
# cd /apps/utils/mariadb/mha4mysql-manager-0.57
# perl Makefile.PL
# make
# make install- mha 설정파일 및 스크립트 복사
[Manager]
#스크립트
# cp /apps/utils/mariadb/mha4mysql-manager-0.57/samples/scripts/* /mha/scripts
# 설정파일
# cp /apps/utils/mariadb/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/mha.cnf
# chown -R mhauser.mysql /etc/mha.cnf
- log 폴더 생성및 권한 부여
[Manager, Master, Slave]
# mkdir -p /var/log/mariadb/mha
# chown -R mhauser.mysql /var/log/mariadb/mha- 링크생성
[root@ ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@ ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
2. SSL 연결 설정
MHA는 ssh로 노드를 연결하고 scp로 로그를 전송함
MHA모니터링과 Failover를 수행하기 위해선 각각의 서버들이 서로 간에 비밀번호 없이 SSH 접속할 수 있어야 함
- failover 후 활성화/비활성와 명령 실행을 위한 sudo 파일설정
[Manager, Master, Slave]
root 계정으로 실행
#[root] visudo
#하단에 추가
mhauser ALL=(ALL) NOPASSWD:/sbin/ifconfig- 각 서버에 키 생성
[Manager, Master, Slave]
MHA는 mhauser 계정으로 ssh 접속하므로 mhauser에서 수행
[root@manager ~]# su - mhauser
# 키생성
[mhauser@manager ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/mhauser/.ssh/id_rsa):[엔터]
Created directory '/home/mhauser/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:[엔터]
Your identification has been saved in /home/mhauser/.ssh/id_rsa.
Your public key has been saved in /home/mhauser/.ssh/id_rsa.pub.
The key fingerprint is:[엔터]
SHA256:FIytgETffEpLuvNACDPYhdgX0hQWpBRygwAWsvqG0R8 mhauser@manager.mariadb
The key's randomart image is:[엔터]
+---[RSA 2048]----+
|BOXBO+ +. |
|**+B+o. o. |
|* o...=.o |
|.= . +.= |
|o o E o S |
| + o o |
|. o = |
| . + |
| . |
+----[SHA256]-----+- 각 서버에 키 복사
[Manager]
# manager -> master
[mhauser@manager ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@master
# manager -> slqve
[mhauser@manager ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@slave[Master]
# master -> manager
[mhauser@master ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@manager
# master -> slave
[mhauser@master ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@slave[Slave]
# slave -> manager
[mhauser@slave ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@manager
# slave -> master
[mhauser@slave ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@master- 접속 테스트
[Manager, Master, Slave]
각 서버에서 다른서버로 ssh 연결해보기
ssh manger
ssh master
ssh slave- 파일 내용이 변경되지 않도록 권한 수정
[Manager, Master, Slave]
# su - mhauser
# cd ~/.ssh
# chmod 400 authorized_keys
3. MHA Manager 설정
- MHA manager 명령어 커스터마이징(MHA user)
[Manager]
# su - mhauser
# vi ~/.bash_profile
아래 내용 추가
set -o vi
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/mariadb/mha/manager.log'
저장후
# source ~/.bash_profile
- MHA 설정 파일 (mha.cnf)
[Manager]
# vi /etc/mha.cnf
아래 내용 입력
[server default]
#mysql
user=mha
password=mha
#ssl
ssh_user=mhauser
#replica
repl_user=rep
repl_password=rep
#mha log
manager_workdir=/mha
manager_log=/var/log/mariadb/mha/manager.log
#remote mha log
remote_workdir=/mha
#binary bin log path
master_binlog_dir=/var/log/mariadb/mha
master_ip_online_change_script=/mha/scripts/master_ip_online_change
master_ip_failover_script=/mha/scripts/master_ip_failover
[server1]
hostname=192.168.2.216
candidate_master=1
[server2]
hostname=192.168.2.217
candidate_master=1
- Script 수정
[Manager]
- master_ip_online_change_script
스크립트를 옴겨둔 /mha/scripts 에서
150,151,152, 245,246,247,248 행을 주석 처리한다.
# vi /mha/scripts/master_ip_online_change
:set nu
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);
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();
- master_ip_failover_script
스크립트를 옴겨둔 /mha/scripts 에서
87,88,89,90,93 행을 주석 처리한다.
# vi /mha/scripts/ master_ip_failover
:set nu
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;- 설정 확인
[Manager]
- ssh 설정확인 (mhauser)
# su - mhauser
# sshcheck
OR
# masterha_check_ssh --conf=/etc/mha.cnf- repl 설정확인 (mhauser)
# su - mhausr
# replcheck
OR
# masterha_check_repl --conf=/etc/mha.cnf
※ error 발생시
Can't exec "mysqlbinlog": 그런 파일이나 디렉터리가 없습니다 at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
at /usr/local/bin/apply_diff_relay_logs line 493.
Tue Sep 13 11:21:44 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Tue Sep 13 11:21:44 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Tue Sep 13 11:21:44 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48.
Tue Sep 13 11:21:44 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Sep 13 11:21:44 2022 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
- 해결
로그상 문제가있는 서버에서 링크를 걸어준다.
[root@ ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@ ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
7. 테스트
1. Master / Slave switch test
- masterha_master_switch 명령어 중에서 --orig_master_is_new_slave 옵션을 사용
- 명령어를 사용하여 수동으로 교체하므로 mha가 정지 상태여야함
- master_ip_online_change_script 가 작성되어 있어야함
[Manager]
#mha 종료상태 확인
[mhauser@manager ~]$ status
mha is stopped(2:NOT_RUNNING).
slave 를 master 로 변경 :
[mhauser@manager ~]$ masterha_master_switch --master_stat=alive --conf=/etc/mha.cnf --orig_master_is_new_slave
Mon Sep 5 15:11:00 2022 - [info] MHA::MasterRotate version 0.57.
Mon Sep 5 15:11:00 2022 - [info] Starting online master switch..
...
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching.
Is it ok to execute on 192.168.2.216(192.168.2.216:3306)? (YES/no): yes
...
From:
192.168.2.216(192.168.2.216:3306) (current master)
+--192.168.2.217(192.168.2.217:3306)
To:
192.168.2.217(192.168.2.217:3306) (new master)
+--192.168.2.216(192.168.2.216:3306)
Starting master switch from 192.168.2.216(192.168.2.216:3306) to 192.168.2.217(192.168.2.217:3306)? (yes/NO): yes
...
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually,
applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
...
Mon Sep 5 15:11:41 2022 - [info] 192.168.2.217: Resetting slave info succeeded.
Mon Sep 5 15:11:41 2022 - [info] Switching master to 192.168.2.217(192.168.2.217:3306) completed successfully.
[Master]
#master가 slave 로 설정됨
[mhauser@master ~]$ mariadb -u root -p
Enter password:
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON | --> ON 으로 바뀜
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.217
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 342
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Exec_Master_Log_Pos: 342
Relay_Log_Space: 866
Until_Condition: None
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
...[Slave]
#slave 가 master로 설정됨
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF | -->OFF 로 바뀜
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> show slave status \G;
Empty set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]> show processlist ;
# master , slave 되돌리기
[mhauser@manager ~]$ masterha_master_switch --master_stat=alive --conf=/etc/mha.cnf --orig_master_is_new_slave 2. Failover test
- master에 대해 health check 실패시 자동으로 failover 여부확인
[Manager]
#MHA 실행
[mhauser@manager /]$ start
[1] 6766
[mhauser@manager /]$ Mon Sep 5 16:24:26 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Sep 5 16:24:26 2022 - [info] Reading application default configuration from /etc/mha.cnf..
Mon Sep 5 16:24:26 2022 - [info] Reading server configuration from /etc/mha.cnf..
##MHA 상태확인
[mhauser@manager /]$ status
mha (pid:6766) is running(0:PING_OK), master:192.168.2.216
#MHA log(tail -f)
[mhauser@manager /]$ log
+--192.168.2.217(192.168.2.217:3306)
Mon Sep 5 16:24:33 2022 - [info] Checking master_ip_failover_script status:
Mon Sep 5 16:24:33 2022 - [info] /mha/scripts/master_ip_failover --command=status --ssh_user=mhauser --orig_master_host=192.168.2.216 --orig_master_ip=192.168.2.216 --orig_master_port=3306
Mon Sep 5 16:24:33 2022 - [info] OK.
Mon Sep 5 16:24:33 2022 - [warning] shutdown_script is not defined.
Mon Sep 5 16:24:33 2022 - [info] Set master ping interval 3 seconds.
Mon Sep 5 16:24:33 2022 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Sep 5 16:24:33 2022 - [info] Starting ping health check on 192.168.2.216(192.168.2.216:3306)..
Mon Sep 5 16:24:33 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
[Master]
master 서비스 종료
# service mysql stop
참고
개발자가 되려는 개발자