MySQL MHA 설치 및 구성하기

MySQL MHA 설치 및 구성하기

테스트 환경

역할	호스트 이름	운영체제	아이피	MHA 패키지	비고
VIP			192.168.56.105
Master	node1	ubuntu 22.04	192.168.56.101	perl, node
Slave	node2	ubuntu 22.04	192.168.56.102	perl, node
Slave	node3	ubuntu 22.04	192.168.56.103	perl, node
Monitor	monitor	ubuntu 22.04	192.168.56.104	perl, manager, node

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

MySQL 리플리케이션(replication)을 설정하는 방법

MySQL에서 리플리케이션을 다시 연결하는 방법

mysql my.cnf 파일 설정

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

 

• 마스터 서버

### my.cnf
[mysqld]
user = mysql
bind-address = 0.0.0.0
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

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

symbolic-links = 0

#skip-grant-tables
skip-name-resolve

server-id=1
log-bin=/usr/local/mysql/data/mysql-bin
relay-log=/usr/local/mysql/data/relay-log
binlog-format=ROW
expire_logs_days=7
relay_log_purge=0
log_slave_updates=1

[client]
port = 3306
socket = /tmp/mysql.sock

• 슬레이브 서버

### my.cnf
[mysqld]
user = mysql
bind-address = 0.0.0.0
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

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

symbolic-links = 0

#skip-grant-tables
skip-name-resolve

server-id=2
log-bin=mysql-bin
log-bin=/usr/local/mysql/data/mysql-bin
relay-log=/usr/local/mysql/data/relay-log
binlog-format=ROW
expire_logs_days=7
relay_log_purge=0
read_only = 1

[client]
port = 3306
socket = /tmp/mysql.sock

 

mhauser 계정 생성 및 sudo 설정

useradd -m -c "MHA user" -d /home/mhauser -s /bin/bash mhauser

echo "mhauser:mhapassword" | chpasswd

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

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

mhauser 계정을 mysql 그룹에 포함하는 명령어

sudo usermod -aG mysql mhauser

 

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

su - mhauser

ssh-keygen -t rsa

mhauser@monitor:~$ ssh-keygen -t rsa

ssh-copy-id [email protected]

mhauser@monitor:~$ ssh-copy-id [email protected]

개인 키(id_rsa) 복사

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

scp ~/.ssh/id_rsa [email protected]:~/.ssh/id_rsa

mhauser@monitor:~$ scp ~/.ssh/id_rsa [email protected]:~/.ssh/id_rsa

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

mhauser@node1:~$ ssh [email protected]

 

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

mkdir -p ~/mha/{conf,scripts,logs}

sudo chown -R mhauser.mysql ~/mha

MySQL 명령어 PATH 등록 및 link 설정

sudo sh -c 'echo "export PATH=\"/usr/local/mysql/bin:\$PATH\"" >> /etc/profile'

sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

sudo ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin

sudo ln -s /usr/local/mysql/bin/mysqld_safe /usr/bin/mysqld_safe

sudo ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

 

우분투에서 MHA 매니저 패키지 설치

• 종속 Perl 모듈 설치

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

• MHA 노드(node) 설치

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

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

$ dpkg -i mha4mysql-node_0.58-0_all.deb
Selecting previously unselected package mha4mysql-node.
(Reading database ... 75163 files and directories currently installed.)
Preparing to unpack mha4mysql-node_0.58-0_all.deb ...
Unpacking mha4mysql-node (0.58-0) ...
Setting up mha4mysql-node (0.58-0) ...
Processing triggers for man-db (2.10.2-1) ...

• MHA 관리자(manger) 설치

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

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

$ dpkg -i mha4mysql-manager_0.58-0_all.deb
Selecting previously unselected package mha4mysql-manager.
(Reading database ... 75186 files and directories currently installed.)
Preparing to unpack mha4mysql-manager_0.58-0_all.deb ...
Unpacking mha4mysql-manager (0.58-0) ...
Setting up mha4mysql-manager (0.58-0) ...
Processing triggers for man-db (2.10.2-1) ...

 

Perl 모듈 및 MHA 패키지 설치

• node1
  • Perl 모듈, mha4mysql-node 패키지 설치
• node2
  • Perl 모듈, mha4mysql-node 패키지 설치
• node3
  • Perl 모듈, mha4mysql-node 패키지 설치
• monitor
  • Perl 모듈, mha4mysql-node, mha4mysql-manager 패키지 설치

 

MySQL 쉘에서 리플케이션 사용자 생성하기

mysql -u root -p'mysqlrootpassword1!'

use mysql;

CREATE USER 'mhauser'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' BY 'mhapassword';

GRANT ALL ON *.* TO 'mhauser'@'192.168.56.%';

FLUSH PRIVILEGES;

master DB에서 디비 계정 생성

use mysql;

grant all privileges on *.* to 'mhauser'@'192.168.56.101' identified by 'mhapassword';

grant all privileges on *.* to 'mhauser'@'192.168.56.102' identified by 'mhapassword';

grant all privileges on *.* to 'mhauser'@'192.168.56.103' identified by 'mhapassword';

grant all privileges on *.* to 'mhauser'@'192.168.56.104' identified by 'mhapassword';

 

monitor 서버 구성

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 스크립트 작성

vim /etc/masterha_default.cnf

[server default]
### mysql user and password
user=mhauser
password=mhapassword

### ssh user
ssh_user=mhauser
ssh_port=22

### mysql replication user and password
repl_user=mhauser
repl_password=mhapassword
ping_interval=1

• mha.conf 스크립트 작성

sudo vim /home/mhauser/mha/conf/mha.cnf

### mha.cnf(/home/mhauser/mha/conf/mha.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.56.101 -s 192.168.56.102 -s 192.168.56.103

#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=/var/run/mysqld/mysqld.pid

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

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

[server3]
hostname=192.168.56.103
port=3306
master_binlog_dir=/usr/local/mysql/data
no_master=1

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

mhauser@monitor: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

mhauser@monitor:samples$ 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

mhauser@monitor:samples$ 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

 

• 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.56.105 netmask 255.255.255.0 broadcast 192.168.56.255 up

 

SSH 접속 테스트

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

 

Replication 구성 테스트

masterha_check_repl --conf=/home/mhauser/mha/conf/mha.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