kjh00n의 기록저장소
Rocky Linux9 + MySQL 8로 MHA 구성 (SSL 있는 ver.) root계정 사용 본문
[서버 구성]
| MHA SSL Manager | Rocky Linux 9 + MySQL 8 + OpenSSL 3.2.2 | 192.168.50.10 |
| MHA SSL Master | Rocky Linux 9 + MySQL 8 + OpenSSL 3.2.2 | 192.168.50.20 |
| MHA SSL Slave 1 | Rocky Linux 9 + MySQL 8 + OpenSSL 3.2.2 | 192.168.50.30 |
| MHA SSL Slave 2 | Rocky Linux 9 + MySQL 8 + OpenSSL 3.2.2 | 192.168.50.40 |
★★★SSL 먼저 적용하고 MHA 적용하면 된다.★★★
[모든 서버] (필요한 패키지 설치)
1. CPAN을 사용하기 위한 Perl 패키지 설치
# Perl의 CPAN (Comprehensive Perl Archive Network) 모듈을 관리할 수 있게 해주는 패키지를 설치합니다.
yum -y install perl-CPAN
2. 시스템 패키지 업데이트 (커널 포함) (필수!!!!!!!!!!)
# 시스템 전체 패키지 업데이트, 여기에는 커널도 포함되어 최신 버전으로 업데이트합니다.
dnf update -y
3. Git 설치 (소스 코드 관리 도구)
# Git을 설치하여 소스 코드 관리 및 버전 관리를 할 수 있습니다.
yum -y install git-core
4. Perl 개발 관련 패키지 설치
# Perl 모듈을 개발하는 데 필요한 도구들을 설치합니다. 예: 컴파일러, 헤더 파일 등
yum -y install perl-devel
5. MariaDB와 연결을 위한 Perl DBI 드라이버 설치
# Perl에서 MySQL(MariaDB)과 연결할 수 있게 해주는 DBI 드라이버 설치
yum -y install perl-DBD-MySQL
6. CPAN에서 Perl 모듈 설치 (Config::Tiny)
# Perl 설정 파일을 쉽게 읽고 쓸 수 있도록 도와주는 모듈 설치
cpan install Config::Tiny
7. CPAN에서 Perl 모듈 설치 (Log::Dispatch)
# Perl 애플리케이션의 로그를 다양한 방식으로 처리하고 출력할 수 있도록 하는 모듈 설치
cpan install Log::Dispatch
8. CPAN에서 Perl 모듈 설치 (Parallel::ForkManager)
# Perl에서 병렬 프로세스를 관리하고, 동시에 여러 작업을 처리할 수 있게 해주는 모듈 설치
cpan install Parallel::ForkManager
9. CPAN에서 Perl 모듈 설치 (Module::Install)
# Perl 모듈의 설치와 빌드를 쉽게 할 수 있도록 도와주는 도구 설치
cpan install Module::Installvim /etc/ssh/sshd_config
Port 22 주석 해제하기
PermitRootLogin yes 추가하기
systemctl restart sshd[Master와 Slave 구성 및 이중화]
1. [mysql 설치] (Master / Slave1,2)
yum -y install mysql-server
systemctl enable mysqld
systemctl start mysqld2. [mysql 접속해서 replication 계정 생성] (Master / Slave1,2)
create user 'repl'@'%' identified by 'repl';
grant replication slave on *.* to 'repl'@'%';
flush privileges;
[SSL을 사용하지 않기 위해 설정]
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
FLUSH PRIVILEGES;3. [mha 계정 생성] (Master / Slave1,2)
create user 'mha'@'%' identified by 'mha';
grant all privileges on *.* to 'mha'@'%';
flush privileges;4. [master server의 my.cnf.d/mysql-server.cnf] (Master)
[mariadb]
server_id=1
log-bin=mysql-bin5. [slave server의 my.cnf.d/mysql-server.cnf] (Slave1,2)
[mariadb]
server_id=2
log-bin=mysql-bin
relay_log_purge=0[mysqld]
server_id=3
log-bin=mysql-bin
relay_log_purge=06. [master와 slave의 mysql 재시작] (Master / Slave1,2)
systemctl restart mysqld7. [master와 slave 이중화] (Master)
7-1. [slave에서 master 연결] (Slave1,2)
CHANGE MASTER TO
MASTER_HOST='192.168.50.20',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157,
MASTER_SSL=1;
MASTER_SSL_CA='/etc/mysql/ssl/server-cert.pem',
MASTER_SSL_CERT='/etc/mysql/ssl/server-cert.pem',
MASTER_SSL_KEY='/etc/mysql/ssl/server-key.pem';
start slave;
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.50.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 157
Relay_Log_File: localhost-relay-bin.000006
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000010
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: 348
Relay_Log_Space: 889
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/ssl/server-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/mysql/ssl/server-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mysql/ssl/server-key.pem
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: 2
Master_UUID: 875bfb84-e9e8-11ef-80c2-005056309e4a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:위처럼 Last_IO_Errno와 Last_IO_Error가 없어야 하고
Slave_IO_Running과 Slave_SQL_Running이 YES로 설정되어 있어야 한다
[방화벽 설정]
(모든 서버)
systemctl start firewalld.service
firewall-cmd --list-ports (처음에는 아무런 결과도 나오지 않음)
firewall-cmd --zone=public --add-port=22/tcp --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-ports
[root@localhost ~]# firewall-cmd --list-ports
22/tcp 3306/tcp
↑ 위 결과처럼 나옴[MHA 설치]
1. MHA 프로그램이 사용할 디렉토리 생성 (모든 서버)
mkdir /mha2. MHA Node 설치 (모든 서버)
mkdir /source
cd /source
git clone https://github.com/yoshinorim/mha4mysql-node.git
cd /source/mha4mysql-node
perl Makefile.PL
make
make install3. MHA Manager 설치 (Manager)
cd /source
git clone https://github.com/yoshinorim/mha4mysql-manager.git
cd /source/mha4mysql-manager
perl Makefile.PL
make
make install4. 각 서버에서 다른 서버들에게 공개키를 줘야 함.
(MHA Manager -> Master DB, Slave DB / Master DB -> Slave DB, MHA Manager / Slave DB -> MasterDB, MHA Manager)
키 생성 :
(모든 Server에서)
ssh-keygen -t rsa [엔터 3번]
키 복사 :
(Manager Server에서)
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.50.20
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.30
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.40
(Master Server에서)
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.50.10
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.30
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.40
(Slave Server에서)
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.50.10
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.20
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.40
(Slave Server 2에서)
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.50.10
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.20
ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.30
연결 테스트 :
ssh 192.168.50.10
ssh 192.168.50.20
ssh 192.168.50.30
ssh 192.168.50.405. MHA Manager 명령어 커스텀마이징 (Manager)
sudo vim .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 /mha/manager.log'
저장 후
source .bash_profile6. MHA 사용할 config 파일 생성 (Manager)
[server default]
user=mha
password=mha
ssh_user=root
repl_user=repl
repl_password=repl
manager_workdir=/mha
manager_log=/mha/manager.log
remote_workdir=/mha
master_binlog_dir=/var/lib/mysql
↓ 얘네는 스크립트 적용하고 나서 설정하는 것
master_ip_online_change_script=/mha/scripts/master_ip_online_change
master_ip_failover_script=/mha/scripts/master_ip_failover
[server1]
hostname=192.168.50.20
candidate_master=1
[server2]
hostname=192.168.50.30
candidate_master=1
[server3]
hostname=192.168.50.40[MHA 설정 확인]
(Manager)
sshcheck
replcheck[root@localhost scripts]# replcheck
Tue Feb 18 02:02:39 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Feb 18 02:02:39 2025 - [info] Reading application default configuration from /etc/mha.cnf..
Tue Feb 18 02:02:39 2025 - [info] Reading server configuration from /etc/mha.cnf..
Tue Feb 18 02:02:39 2025 - [info] MHA::MasterMonitor version 0.58.
Tue Feb 18 02:02:41 2025 - [info] GTID failover mode = 0
Tue Feb 18 02:02:41 2025 - [info] Dead Servers:
Tue Feb 18 02:02:41 2025 - [info] Alive Servers:
Tue Feb 18 02:02:41 2025 - [info] 192.168.50.20(192.168.50.20:3306)
Tue Feb 18 02:02:41 2025 - [info] 192.168.50.30(192.168.50.30:3306)
Tue Feb 18 02:02:41 2025 - [info] 192.168.50.40(192.168.50.40:3306)
Tue Feb 18 02:02:41 2025 - [info] Alive Slaves:
Tue Feb 18 02:02:41 2025 - [info] 192.168.50.30(192.168.50.30:3306) Version=8.0.36 (oldest major version between slaves) log-bin:enabled
Tue Feb 18 02:02:41 2025 - [info] Replicating from 192.168.50.20(192.168.50.20:3306)
Tue Feb 18 02:02:41 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Feb 18 02:02:41 2025 - [info] 192.168.50.40(192.168.50.40:3306) Version=8.0.36 (oldest major version between slaves) log-bin:enabled
Tue Feb 18 02:02:41 2025 - [info] Replicating from 192.168.50.20(192.168.50.20:3306)
Tue Feb 18 02:02:41 2025 - [info] Current Alive Master: 192.168.50.20(192.168.50.20:3306)
Tue Feb 18 02:02:41 2025 - [info] Checking slave configurations..
Tue Feb 18 02:02:41 2025 - [info] read_only=1 is not set on slave 192.168.50.30(192.168.50.30:3306).
Tue Feb 18 02:02:41 2025 - [info] read_only=1 is not set on slave 192.168.50.40(192.168.50.40:3306).
Tue Feb 18 02:02:41 2025 - [info] Checking replication filtering settings..
Tue Feb 18 02:02:41 2025 - [info] binlog_do_db= , binlog_ignore_db=
Tue Feb 18 02:02:41 2025 - [info] Replication filtering check ok.
Tue Feb 18 02:02:41 2025 - [info] GTID (with auto-pos) is not supported
Tue Feb 18 02:02:41 2025 - [info] Starting SSH connection tests..
Tue Feb 18 02:02:43 2025 - [info] All SSH connection tests passed successfully.
Tue Feb 18 02:02:43 2025 - [info] Checking MHA Node version..
Tue Feb 18 02:02:44 2025 - [info] Version check ok.
Tue Feb 18 02:02:44 2025 - [info] Checking SSH publickey authentication settings on the current master..
Tue Feb 18 02:02:44 2025 - [info] HealthCheck: SSH to 192.168.50.20 is reachable.
Tue Feb 18 02:02:44 2025 - [info] Master MHA Node version is 0.58.
Tue Feb 18 02:02:44 2025 - [info] Checking recovery script configurations on 192.168.50.20(192.168.50.20:3306)..
Tue Feb 18 02:02:44 2025 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/mha/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000003
Tue Feb 18 02:02:44 2025 - [info] Connecting to root@192.168.50.20(192.168.50.20:22)..
Creating /mha if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000003
Tue Feb 18 02:02:44 2025 - [info] Binlog setting check done.
Tue Feb 18 02:02:44 2025 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Feb 18 02:02:44 2025 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.50.30 --slave_ip=192.168.50.30 --slave_port=3306 --workdir=/mha --target_version=8.0.36 --manager_version=0.58 --relay_dir=/var/lib/mysql --current_relay_log=localhost-relay-bin.000002 --slave_pass=xxx
Tue Feb 18 02:02:44 2025 - [info] Connecting to root@192.168.50.30(192.168.50.30:22)..
Checking slave recovery environment settings..
Relay log found at /var/lib/mysql, up to localhost-relay-bin.000002
Temporary relay log file is /var/lib/mysql/localhost-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Feb 18 02:02:45 2025 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.50.40 --slave_ip=192.168.50.40 --slave_port=3306 --workdir=/mha --target_version=8.0.36 --manager_version=0.58 --relay_dir=/var/lib/mysql --current_relay_log=localhost-relay-bin.000002 --slave_pass=xxx
Tue Feb 18 02:02:45 2025 - [info] Connecting to root@192.168.50.40(192.168.50.40:22)..
Checking slave recovery environment settings..
Relay log found at /var/lib/mysql, up to localhost-relay-bin.000002
Temporary relay log file is /var/lib/mysql/localhost-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Feb 18 02:02:45 2025 - [info] Slaves settings check done.
Tue Feb 18 02:02:45 2025 - [info]
192.168.50.20(192.168.50.20:3306) (current master)
+--192.168.50.30(192.168.50.30:3306)
+--192.168.50.40(192.168.50.40:3306)
Tue Feb 18 02:02:45 2025 - [info] Checking replication health on 192.168.50.30..
Tue Feb 18 02:02:45 2025 - [info] ok.
Tue Feb 18 02:02:45 2025 - [info] Checking replication health on 192.168.50.40..
Tue Feb 18 02:02:45 2025 - [info] ok.
Tue Feb 18 02:02:45 2025 - [info] Checking master_ip_failover_script status:
Tue Feb 18 02:02:45 2025 - [info] /mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.50.20 --orig_master_ip=192.168.50.20 --orig_master_port=3306
Tue Feb 18 02:02:45 2025 - [info] OK.
Tue Feb 18 02:02:45 2025 - [warning] shutdown_script is not defined.
Tue Feb 18 02:02:45 2025 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.↑ replcheck라고 입력하면 밑처럼 MySQL Replication Health is OK가 출력되야 정상작동 가능
[Slave IP ↔ Master IP 변경 스크립트 설정]
이 스크립트는 마스터 서버의 장애 발생 시 슬레이브 서버를 새로운 마스터로 승격시키는 과정에서 슬레이브 서버의 IP가 새로운 마스터의 IP로 전환될 수 있도록 돕는 역할을 합니다.
(Manager)
mkdir /mha/scripts
cp /source/mha4mysql-manager-0.57/samples/scripts/master_ip_online_change /mha/scripts/master_ip_online_change
vim /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();[Failover 스크립트 설정]
cp /source/mha4mysql-manager-0.57/samples/scripts/master_ip_failover /mha/scripts/
vim /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;[결과]
지금은 Master와 Slave, Slave2가 SSL로 암호화되고 있음 (Master가 정상 작동중임)
Master (192.168.50.20)을 강제 중지시켰음
SSH 통신은 마스터 서버가 죽은 후 새로운 마스터를 설정하고, 복제 설정을 안전하게 업데이트하는 과정에서 발생한 것
↓ Manager에서 기록 확인하기 (tail -f /mha/manager.log) ↓
↓ Slave 2 (192.168.50.40)의 연결 상태가 새로 Master로 승격된 192.168.50.30 (前 Slave)가 Master로 지정됨
↓ 여전히 새로운 Master와 Slave 2는 SSL 암호화 통신을 진행중이다
※이 모든 작업을 root계정 기반으로 진행했음
root계정 말고 mhauser 계정을 만들어서 진행할 예정
'파이널 프로젝트' 카테고리의 다른 글
| Project A에 Galera Cluster 구축 (MariaDB) (0) | 2025.02.19 |
|---|---|
| Project B에 MHA + SSL 구축하기 (0) | 2025.02.18 |
| Mysql 8에 SSL 적용해서 이중화하기 (0) | 2025.02.14 |
| Rocky Linux9 + MySQL 8로 MHA 구성 (SSL 없는 ver.) (0) | 2025.02.13 |
| MariaDB Galera Cluster 구성 (0) | 2025.02.06 |
'파이널 프로젝트' Related Articles
more
