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
'리눅스' 카테고리의 다른 글
우분투에서 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 |