본문 바로가기
리눅스

MySQL MHA(Master High Availability) 설치 및 구성하기

변군Dev 2023. 5. 19. 16:05
728x90

MySQL MHA 설치 및 구성하기

MySQL MHA(Master High Availability)는 MySQL 데이터베이스의 고가용성을 위한 도구입니다.

테스트 환경

역할 호스트 이름 운영체제 아이피 MHA 패키지 비고
VIP     192.168.20.200    
Master node1 ubuntu 22.04 192.168.20.211 perl, node  
Slave node2 ubuntu 22.04 192.168.20.212 perl, node  
Slave node3 ubuntu 22.04 192.168.20.213 perl, node  
Monitor node4 ubuntu 22.04 192.168.20.209 perl, manager, node  

** manager = mha4mysql-manager 패키지, node = mha4mysql-node 패키지

1. MySQL 설치

MySQL 사용자 및 그룹 생성

sudo groupadd -g 28 mysql
sudo useradd -m -c "MySQL Server" -d /usr/local/mysql -s /bin/false -u 28 -g mysql mysql

MySQL 바이너리 파일 다운로드

/usr/local/src
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.34-linux-glibc2.28-x86_64.tar
sudo tar xf mysql-8.0.*.tar
sudo tar xf mysql-8.0.*.tar.gz -C /usr/local/mysql --strip-components=1
sudo mkdir -p /usr/local/mysql/data
sudo chown -R mysql:mysql /usr/local/mysql
sudo chmod 750 /usr/local/mysql/data
vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
bind-address=0.0.0.0
port=3306
pid-file=/usr/local/mysql/data/mysqld.pid

### general log
general-log=TRUE
general-log-file=/usr/local/mysql/data/general.log

### error log
log-error-verbosity=1
log-error=/usr/local/mysql/data/error.log

### slow query log
slow-launch-time=5
slow-query-log=TRUE
slow-query-log-file=/usr/local/mysql/data/slow-query.log

#skip-grant-tables=FALSE
symbolic-links=FALSE
skip-name-resolve=TRUE

server-id=1
binlog-format=ROW
log-bin=/usr/local/mysql/data/mysql-bin
sync-binlog=1

relay-log=/usr/local/mysql/data/relay-log
relay-log-index=/usr/local/mysql/data/relay-log.index
relay-log-purge=TRUE
expire-logs-days=7
log-slave-updates=TRUE
/usr/local/mysql/bin/mysqld --help --verbose
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile
source /etc/profile
mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --user=mysql
cat /usr/local/mysql/data/error.log | grep "A temporary password is generated for root@localhost" | awk '{print $NF}'
mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &

MySQL root 패스워드 변경

mysqladmin -u root -p password 'new_password'
$ mysqladmin -u root -p password 'new_password'
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

MySQL 프롬프트

mysql -h localhost -uroot -p'new_password'

2. 리플리케이션 구성

마스터 서버에서 리플리케이션 계정 생성 및 권한 부여합니다.

CREATE USER 'repluser'@'192.168.20.%' IDENTIFIED WITH mysql_native_password BY 'mhapassword';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.20.%';
FLUSH PRIVILEGES;
SELECT User, Host FROM mysql.user WHERE User='repluser';

마스터 서버에서 바이너리 로그를 활성화합니다.

log-bin = mysql-bin

마스터 서버에서 MySQL을 재시작합니다.

mysqladmin -u root -p'new_password' shutdown
mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &

슬레이브 서버에서 리플리케이션 설정을 수정합니다.

relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index

슬레이브 서버에서 MySQL을 재시작합니다.

mysqladmin -u root -p'new_password' shutdown
mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &

마스터 서버

mysql -h localhost -uroot -p'new_password' -e "show master status\G" | egrep 'File|Position'
$ mysql -h localhost -uroot -p'new_password' -e "show master status\G" | egrep 'File|Position'
mysql: [Warning] Using a password on the command line interface can be insecure.
             File: mysql-bin.000009
         Position: 157
mysql -h localhost -uroot -p'new_password' -e "show processlist\G;"

슬레이브 서버

mysql -hlocalhost -uroot -p'new_password' -e "show slave status\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master'
mysql -h localhost -uroot -p'new_password' -e "stop slave"
mysql -h localhost -uroot -p'new_password' -e "reset slave"
mysql -h localhost -uroot -p'new_password' -e "
CHANGE MASTER TO
MASTER_HOST='192.168.20.211',
MASTER_USER='repluser',
MASTER_PASSWORD='mhapassword',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=157;
"
mysql -hlocalhost -uroot -p'new_password' -e "start slave"
mysql -hlocalhost -uroot -p'new_password' -e "show slave status\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master'
mysql -hlocalhost -uroot -p'new_password' -e "SHOW REPLICA STATUS \G" | egrep 'Source_Host|Source_User|Source_Port|Read_Source_Log_Pos|Relay_Log_File|Relay_Log_Pos|Relay_Source_Log_File|Replica_IO_Running|Replica_SQL_Running|Replica_SQL_Running_State'

MHA 설치 및 구성

MHA SSH 계정 생성

mhauser 계정 생성(모든 node에 설정)

useradd -m -c "MHA user" -d /home/mhauser -s /bin/bash mhauser
echo "mhauser:mhapassword" | chpasswd

sudo 설정

echo "mhauser ALL=(ALL) NOPASSWD:ALL" >> /etc/sudoers

mhauser 계정의 PS1 설정

su - mhauser
echo 'export PS1="\[\e[31m\]\u\[\e[m\]\[\e[37m\]@\[\e[m\]\[\e[33m\]\h\[\e[m\]:\[\033[01;36m\]\W\[\e[m\]$ "' >> ~/.bashrc
source ~/.bashrc

mhauser 계정을 mysql 그룹에 추가

sudo usermod -aG mysql mhauser

mhauser 계정의 ssh key 생성 및 ssh key 교환(전송)

node4

su - mhauser
ssh-keygen -t rsa -b 4096 -C "HMA User"
ssh-copy-id mhauser@node1
더보기 
ssh-copy-id mhauser@node2
ssh-copy-id mhauser@node3
ssh-copy-id mhauser@node4

개인 키(id_rsa) 복사

** 모든 서버에서 양방향으로 패스워드 없이 접속하기 위함.

scp ~/.ssh/id_rsa mhauser@node1:~/.ssh/id_rsa
scp ~/.ssh/id_rsa.pub mhauser@node1:~/.ssh/id_rsa.pub
더보기 
scp ~/.ssh/id_rsa mhauser@node2:~/.ssh/id_rsa
scp ~/.ssh/id_rsa mhauser@node3:~/.ssh/id_rsa
scp ~/.ssh/id_rsa.pub mhauser@node2:~/.ssh/id_rsa.pub
scp ~/.ssh/id_rsa.pub mhauser@node3:~/.ssh/id_rsa.pub

ssh 접속 테스트(node4 -> node1)

mhauser@node4:~$ ssh mhauser@node1

MHA MySQL 계정 생성(마스터에서 실행)

mysql -h localhost -uroot -p'new_password'
CREATE USER 'mhauser'@'192.168.20.%' IDENTIFIED WITH mysql_native_password BY 'mhapassword';
GRANT REPLICATION CLIENT ON *.* TO 'mhauser'@'192.168.20.%';
FLUSH PRIVILEGES;

MHA 패키지 설치를 위해 Perl 및 다른 필수 패키지를 설치합니다.

종속 Perl 모듈 설치

sudo apt-get install -y \
  libdbd-mysql-perl \
  libconfig-tiny-perl \
  liblog-dispatch-perl \
  libparallel-forkmanager-perl

선택 추가 패키지 설치

sudo apt-get install -y \
  perl \
  libdbi-perl \
  libio-socket-ssl-perl \
  libclass-methodmaker-perl \
  libjson-perl \
  libparams-validate-perl \
  libterm-readkey-perl \
  libmodule-install-perl \
  libnet-ssleay-perl \
  libio-socket-inet6-perl

MHA를 다운로드하고 압축을 풉니다.

wget -q https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node_0.58-0_all.deb
wget -q https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager_0.58-0_all.deb

MHA를 설치합니다.

sudo dpkg -i mha4mysql-node_0.58-0_all.deb
sudo dpkg -i mha4mysql-manager_0.58-0_all.deb

mha4mysql-manager, mha4mysql-node 재설치

더보기

nanager(node4)

cd /usr/local/src/
dpkg -l | grep mha4mysql
sudo dpkg -i --force-confmiss mha4mysql-manager_0.58-1.1_all.deb mha4mysql-node_0.58-1.1_all.deb

node(node1, node2, node3)

cd /usr/local/src/
dpkg -l | grep mha4mysql
sudo dpkg -i --force-confmiss mha4mysql-node_0.58-1.1_all.deb

MHA 설정 파일을 생성합니다.

디렉토리 생성 및 소유권 변경

mkdir -p ~/mha/{conf,scripts,logs}
sudo chown -R mhauser.mysql ~/mha

MySQL 명령어 링크 설정

sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
sudo ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
더보기 
sudo ln -s /usr/local/mysql/bin/mysqld_safe /usr/bin/mysqld_safe
sudo ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin

MySQL my.cnf 파일 설정

github : https://github.com/anti1346/mysql-mha

더보기
  • 마스터 서버
### my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
bind-address=0.0.0.0
port=3306
pid-file=/usr/local/mysql/data/mysqld.pid

### general log
general-log=TRUE
general-log-file=/usr/local/mysql/data/general.log

### error log
log-error-verbosity=1
log-error=/usr/local/mysql/data/error.log

### slow query log
slow-launch-time=5
slow-query-log=TRUE
slow-query-log-file=/usr/local/mysql/data/slow-query.log

#skip-grant-tables=FALSE
symbolic-links=FALSE
skip-name-resolve=TRUE

server-id=1
binlog-format=ROW
log-bin=/usr/local/mysql/data/mysql-bin
sync-binlog=1
relay-log=/usr/local/mysql/data/relay-log
#relay-log-index=/usr/local/mysql/data/relay-log.index
relay-log-purge=TRUE
expire-logs-days=7
log-slave-updates=TRUE
  • 슬레이브 서버
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
bind-address=0.0.0.0
port=3306
pid-file=/usr/local/mysql/data/mysqld.pid

### general log
general-log=TRUE
general-log-file=/usr/local/mysql/data/general.log

### error log
log-error-verbosity=3
log-error=/usr/local/mysql/data/error.log

### slow query log
slow-launch-time=5
slow-query-log=TRUE
slow-query-log-file=/usr/local/mysql/data/slow-query.log

#skip-grant-tables=FALSE
symbolic-links=FALSE
skip-name-resolve=TRUE

server-id=2
binlog-format=ROW
log-bin=/usr/local/mysql/data/mysql-bin
sync-binlog=1

relay-log=/usr/local/mysql/data/relay-log
relay-log-index=/usr/local/mysql/data/relay-log.index
relay-log-purge=TRUE
expire-logs-days=7
read-only=FALSE

mha manager 설정 및 스크립트 작성

  • masterha 실행 파일 목록
$ ls -l /usr/bin | grep masterha
-rwxr-xr-x 1 root root       1995 Mar 23  2018 masterha_check_repl
-rwxr-xr-x 1 root root       1779 Mar 23  2018 masterha_check_ssh
-rwxr-xr-x 1 root root       1865 Mar 23  2018 masterha_check_status
-rwxr-xr-x 1 root root       3201 Mar 23  2018 masterha_conf_host
-rwxr-xr-x 1 root root       2517 Mar 23  2018 masterha_manager
-rwxr-xr-x 1 root root       2165 Mar 23  2018 masterha_master_monitor
-rwxr-xr-x 1 root root       2373 Mar 23  2018 masterha_master_switch
-rwxr-xr-x 1 root root       5172 Mar 23  2018 masterha_secondary_check
-rwxr-xr-x 1 root root       1739 Mar 23  2018 masterha_stop
  • masterha_default.cnf 스크립트 작성
sudo vim ~/mha/conf/masterha_default.cnf
[server default]
user=mhauser
password=mhapassword

ssh_user=mhauser
ssh_port=22

repl_user=repluser
repl_password=mhapassword

ping_interval=1
sudo ln -s /home/mhauser/mha/conf/masterha_default.cnf /etc/masterha_default.cnf
  • mha.conf 스크립트 작성
sudo vim /home/mhauser/mha/conf/app1.cnf
### mha.cnf(/home/mhauser/mha/conf/app1.cnf)
[server default]
manager_workdir=/home/mhauser/mha

manager_log=/home/mhauser/mha/logs/manager.log

remote_workdir=/home/mhauser/mha

secondary_check_script=masterha_secondary_check -s 192.168.20.211 -s 192.168.20.212 -s 192.168.20.213

#master_ip_online_change_script=/home/mhauser/mha/scripts/master_ip_online_change
master_ip_failover_script=/home/mhauser/mha/scripts/master_ip_failover
#shutdown_script=/home/mhauser/mha/scripts/shutdown_script

master_binlog_dir=/usr/local/mysql/data

master_pid_file=/usr/local/mysql/data/mysqld.pid

[server1]
hostname=192.168.20.211
port=3306
master_binlog_dir=/usr/local/mysql/data
candidate_master=1

[server2]
hostname=192.168.20.212
port=3306
master_binlog_dir=/usr/local/mysql/data
candidate_master=1

[server3]
hostname=192.168.20.213
port=3306
master_binlog_dir=/usr/local/mysql/data
no_master=1
sudo ln -s /home/mhauser/mha/conf/app1.cnf /etc/app1.cnf

mha4mysql manager samples files

더보기

manager file 다운로드

wget -q https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
tar xfz mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58/samples
$ ls -l
total 0
drwxr-xr-x 2 mhauser mhauser  50 Mar 23  2018 conf
drwxr-xr-x 2 mhauser mhauser 103 Mar 23  2018 scripts

samples conf

$ ls -l conf
total 8
-rw-r--r-- 1 mhauser mhauser 257 Mar 23  2018 app1.cnf
-rw-r--r-- 1 mhauser mhauser 475 Mar 23  2018 masterha_default.cnf

samples scripts

$ ls -l scripts
total 32
-rwxr-xr-x 1 mhauser mhauser  3648 Mar 23  2018 master_ip_failover
-rwxr-xr-x 1 mhauser mhauser  9870 Mar 23  2018 master_ip_online_change
-rwxr-xr-x 1 mhauser mhauser 11867 Mar 23  2018 power_manager
-rwxr-xr-x 1 mhauser mhauser  1360 Mar 23  2018 send_report

 

MHA 명령어 alias 설정

vim ~/.bashrc
alias sshcheck='/usr/bin/masterha_check_ssh --conf=/etc/app1.cnf'
alias replcheck='/usr/bin/masterha_check_repl --conf=/etc/app1.cnf'

alias start='/usr/bin/masterha_manager --conf=/etc/app1.cnf &'
alias status='/usr/bin/masterha_check_status --conf=/etc/app1.cnf'
alias stop='/usr/bin/masterha_stop --conf=/etc/app1.cnf'
source ~/.bashrc
  • master_ip_failover 스크립트 작성
sudo vim /home/mhauser/mha/scripts/master_ip_failover
sudo chmod 755 /home/mhauser/mha/scripts/master_ip_failover
  • master_ip_online_change 스크립트 작성
sudo vim /home/mhauser/mha/scripts/master_ip_online_change
sudo chmod 755 /home/mhauser/mha/scripts/master_ip_online_change

 

VIP(Virtual IP)

ifconfig enp0s8:0 192.168.20.200 netmask 255.255.255.0 broadcast 192.168.20.255 up

 

SSH 접속 테스트

sshcheck
masterha_check_ssh --conf=/home/mhauser/mha/conf/app1.cnf

 

Replication 구성 테스트

replcheck
masterha_check_repl --conf=/home/mhauser/mha/conf/app1.cnf

 

mha failover 실행

masterha_manager --conf=/home/mhauser/mha/conf/mha.cnf
$ masterha_manager --conf=/home/mhauser/mha/conf/mha.cnf
Tue May 23 16:06:52 2023 - [info] Reading default configuration from /etc/masterha_default.cnf..
Tue May 23 16:06:52 2023 - [info] Reading application default configuration from /home/mhauser/conf/mha.cnf..
Tue May 23 16:06:52 2023 - [info] Reading server configuration from /home/mhauser/mha/conf/mha.cnf..

  Creating /home/mhauser/logs if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /usr/local/mysql/data, up to mysql-bin.000014
Tue May 23 16:08:17 2023 - [info] Reading default configuration from /etc/masterha_default.cnf..
Tue May 23 16:08:17 2023 - [info] Reading application default configuration from /home/mhauser/mha/conf/mha.cnf..
Tue May 23 16:08:17 2023 - [info] Reading server configuration from /home/mhauser/mha/conf/mha.cnf..

manager.log 로그 확인

$ tail -f /home/mhauser/mha/logs/manager.log
----- Failover Report -----

mha: MySQL Master failover 192.168.56.101(192.168.56.101:3306) to 192.168.56.102(192.168.56.102:3306) succeeded

Master 192.168.56.101(192.168.56.101:3306) is down!

Check MHA Manager logs at monitor:/home/mhauser/mha/logs/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.56.101(192.168.56.101:3306)
Power off 192.168.56.101.
The latest slave 192.168.56.102(192.168.56.102:3306) has all relay logs for recovery.
Selected 192.168.56.102(192.168.56.102:3306) as a new master.
192.168.56.102(192.168.56.102:3306): OK: Applying all logs succeeded.
192.168.56.102(192.168.56.102:3306): OK: Activated master IP address.
192.168.56.103(192.168.56.103:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.56.103(192.168.56.103:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.56.102(192.168.56.102:3306)
192.168.56.102(192.168.56.102:3306): Resetting slave info succeeded.
Master failover to 192.168.56.102(192.168.56.102:3306) completed successfully.

 

참고URL

- Installation : https://github.com/yoshinorim/mha4mysql-manager/wiki/Installation
- Downloads
 MHA 0.56 : https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
 MHA 0.58 :
  mha4mysql-node : https://github.com/yoshinorim/mha4mysql-node
  mha4mysql-manager : https://github.com/yoshinorim/mha4mysql-manager
 MHA 0.55 : https://code.google.com/archive/p/mysql-master-ha/downloads

 

728x90
저작자표시 비영리

'리눅스' 카테고리의 다른 글

우분투에서 IP 주소를 고정하는 방법  (0) 2023.05.20
[draft] 우분투에 MySQL 5.7을 바이너리 파일로 설치하는 방법  (0) 2023.05.19
[리눅스] MySQL MHA(Master High Availability)를 사용하여 레플리케이션 HA(Virtual IP) 환경을 구성하는 방법  (0) 2023.05.19
curl 명령어  (0) 2023.05.17
[draft] 우분투에서 Chrony를 설치하고 설정하는 방법  (0) 2023.05.15

'리눅스' Related Articles

티스토리툴바