MySQL MHA(Master High Availability) 설치

MHA 구성 환경

OS : CentOS 6.10

Mysql : 5.6.51

 

IP 설정 내역
192.168.136.155   ##Master
192.168.136.156   ##Slave

192.168.136.157   ##MHA_Manager

192.168.136.158   ##VIP

 

 

MHA 구성

Node - 모든서버

vi /etc/hosts

# host
192.168.136.155 host01
192.168.136.156 host02

# mgr
192.168.136.157 host03

# vip
192.168.136.158 mha-vip

 

기존에 설치된 mysql 제거

yum list installed | grep mysql

 

yum remove -y mysql-libs

yum remove mysql <-설치되어 있으면 삭제

 

 

MySQL 5.6 설치

rpm -ivh https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

yum install -y mysql mysql-server

ls -al /etc/yum.repos.d/

아래 두개의 파일이 존재하는지 확인 (rpm 설치 시 나옴)

 

root 패스워드 설정

mysqladmin -u root password 'root'

 

방화벽 해제

iptables -I INPUT 1 -p tcp --dport 3306 -j ACCEPT

-- MySQL 기본 포트 3306

 

Node - DB서버 (로그 파일 경로 생성)

mkdir -p /var/lib/mysql-files/logs
chown -R mysql:mysql /var/lib/mysql-files

 

MySQL - my.cnf 수정

vi /etc/my.cnf

 

# Master

[mysqld]
server-id=1
### Replication
log-bin=/var/lib/mysql-files/logs/mysql-bin
sync_binlog=1
binlog_cache_size=2M
binlog_format=ROW
max_binlog_size=512M
expire_logs_days=7
log-bin-trust-function-creators=1
report-host=host01
relay-log=/var/lib/mysql-files/logs/relay_log
relay-log-index=/var/lib/mysql-files/logs/relay_log.index
relay_log_purge=off
expire_logs_days=7
log_slave_updates=ON

 

# Slave

[mysqld]
server-id=2
### Replication
log-bin=/var/lib/mysql-files/logs/mysql-bin
sync_binlog=1
binlog_cache_size=2M
binlog_format=ROW
max_binlog_size=512M
expire_logs_days=7
log-bin-trust-function-creators=1
report-host=host02
relay-log=/var/lib/mysql-files/logs/relay_log
relay-log-index=/var/lib/mysql-files/logs/relay_log.index
relay_log_purge=off
expire_logs_days=7
log_slave_updates=ON

 

mysql 서비스 시작

chkconfig mysqld on
service mysqld start

 

Replication 유저 생성 (Node - DB서버)

# mysql -uroot -p

create user 'repl_u'@'%' identified by 'repl_u';
grant replication slave on *.* to 'repl_u'@'%';
flush privileges;

 

 

Replication 설정

 

# Master

show master status \G;

*************************** 1. row ***************************
                   File: mysql-bin.000001
             Position: 120
      Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

 

# Slave

change master to
master_host='192.168.136.155',
master_user='repl_u',
master_password='repl_u',
master_log_file='mysql-bin.000001',
master_log_pos=120;

start slave;

 

확인

show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.155
                  Master_User: repl_u
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

 

# Master - 확인

show slave hosts \G;

*************************** 1. row ***************************
 Server_id: 2
       Host: host02
       Port: 3306
 Master_id: 1
Slave_UUID: 7b13b120-f985-11eb-889c-000c29369425
1 row in set (0.00 sec)

 

 

MHA 유저 생성

 

# Master

CREATE USER 'mha'@'%' IDENTIFIED BY 'mha';
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'%';
FLUSH PRIVILEGES;

 

OS 유저 생성 (모든 노드 공통)

# useradd -g mysql mha
# passwd mha
# su - mha

$ echo "export PATH=$PATH:/var/lib/mysql-files/bin:/var/lib/mysql/bin:/var/lib/mysql/bin" >> ~/.bash_profile
$ source ~/.bash_profile

 

SSH 키 생성

mha 유저

$ ssh-keygen -t rsa -b 4096

[mha@host01 ~]$ ssh-keygen -t rsa -b 4096
Generating public/private rsa key pair.
Enter file in which to save the key (/home/mha/.ssh/id_rsa):
/home/mha/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase): {enter}
Enter same passphrase again: {enter}
Your identification has been saved in /home/mha/.ssh/id_rsa.
Your public key has been saved in /home/mha/.ssh/id_rsa.pub.
The key fingerprint is:
8d:a4:9a:dc:ee:f1:70:ef:b8:1f:f3:8b:1b:d5:e5:8c mha@host01
The key's randomart image is:
+--[ RSA 4096]----+
|                     |
|                     |
|        .       .     |
|       o o   . =   |
|      . S . . E o    |
|   . +     .         |
|    + + . +        |
|     . = o *        |
|     .o ++*.o.     |
+-----------------+

 

SSH 키 전달

# Manager

ssh-copy-id -i host01
ssh-copy-id -i host02

# Master

ssh-copy-id -i host03
ssh-copy-id -i host02

# slave

ssh-copy-id -i host01
ssh-copy-id -i host03

 

확인

#Manager

ssh host01
ssh host02

 

mha 유저 sudouser 설정

failover 시 mha에게 root 권한을 수행하기 위해 설정

 

# Master, Slave

root 계정

visudo

mha ALL=(ALL) NOPASSWD:/sbin/ifconfig

-- 위 내용 추가

 

VIP설정

# Master, Slave

mha 계정

ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:af:f9:71 brd ff:ff:ff:ff:ff:ff
    inet 192.168.136.155/24 brd 192.168.136.255 scope global eth0

 

sudo ifconfig eth0:0 192.168.136.158 up

sudo ifconfig eth0:0 192.168.136.158 down

 

MHA 설치

# 모든 노드

root 계정

yum install -y perl-DBD-MySQL \
perl-Config-Tiny perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-Log-Dispatch perl-Time-HiRes \
perl-CPAN perl-Module-Install

 

# Manager

root 계정

mkdir /root/setup
cd /root/setup
yum install -y wget
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz

tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58

perl Makefile.PL

-- y

 

make;make install

-- yes

 

# Manager, Master, Slave

root 계정

mkdir /root/setup
cd /root/setup
yum install -y wget

-- Master, Slave 만 설치 (Manager는 위에서 설치 함)

 

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

tar zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58

perl Makefile.PL

make;make install

 

심볼릭 제외

 

 

# Manager

mkdir -p /etc/masterha/conf
mkdir -p /masterha/scripts

cd /root/setup/mha4mysql-manager-0.58/samples
cp conf/* /etc/masterha/conf/
cp scripts/* /masterha/scripts/

 

# Manager, Master, Slave

mkdir -p /masterha/app1
chown -R mha:mysql /masterha

 

MHA 설정

# Manager

root 계정

 

cd /etc/masterha/conf

chown root:mysql app1.cnf
chmod 775 app1.cnf

 

vi app1.cnf

[server default]
user=mha
password=mha
ssh_user=mha
repl_user=repl_u
repl_password=repl_u
manager_workdir=/masterha/app1
manager_log=/masterha/app1/app1.log
remote_workdir=/masterha/app1
master_binlog_dir=/var/lib/mysql-files/logs

secondary_check_script=/usr/local/bin/masterha_secondary_check -s host01 -s host02 --user=mha --master_host=host01 --master_ip=host01 --master_port=3306

master_ip_failover_script=/masterha/scripts/master_ip_failover
master_ip_online_change_script=/masterha/scripts/master_ip_online_change

[server1]
hostname=host01
candidate_master=1

[server2]
hostname=host02
candidate_master=1

 

mha 계정

master_ip_failover 수정

cd /masterha/scripts
vi master_ip_failover

-- 86 ~ 93 라인부터 주석처리 후 아래 내용 추가(mha_change_vip.sh)

## Creating an app user on the new master
# print "Creating app user on the new master..\n";
# FIXME_xxx_create_user( $new_master_handler->{dbh} );
# $new_master_handler->enable_log_bin_local();
# $new_master_handler->disconnect();

## Update master ip on the catalog database, etc
# FIXME_xxx;

system("/bin/bash /masterha/scripts/mha_change_vip.sh $new_master_ip");

 

master_ip_online_change 수정

vi master_ip_online_change

 

-- 149 ~ 152 라인부터 주석처리

## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
# $orig_master_handler->disable_log_bin_local();
# print current_time_us() . " Drpping app user on the orig master..\n";
# FIXME_xxx_drop_app_user($orig_master_handler);

 

-- 244 ~ 248 라인부터 주석처리 후 아래 내용 추가(mha_change_vip.sh)

## Creating an app user on the new master
# print current_time_us() . " Creating app user on the new master..\n";
# FIXME_xxx_create_app_user($new_master_handler);
# $new_master_handler->enable_log_bin_local();
# $new_master_handler->disconnect();

## Update master ip on the catalog database, etc
system("/bin/bash /masterha/scripts/mha_change_vip.sh $new_master_ip");
$exit_code = 0;

 

mha_change_vip.sh 수정

vi mha_change_vip.sh

#!/bin/bash
## Fail-Over VIP Change

V_NEW_MASTER=`cat /etc/hosts | grep $1 | awk '{print $2}'`
V_EXIST_VIP_CHK=`ping -c 1 -W 1 mha-vip | grep "packet loss" | awk '{print $6}'`
V_VIP_IP=`cat /etc/hosts | grep mha-vip | awk '{print $1}'`

if [ $V_EXIST_VIP_CHK = "0%" ]
then

echo "VIP IS Alive, VIP Relocate $V_NEW_MASTER "
/usr/bin/ssh mha-vip /usr/bin/sudo /sbin/ifconfig eth0:0 down &

ssh $V_NEW_MASTER /usr/bin/sudo /sbin/ifconfig eth0:0 $V_VIP_IP
ssh $V_NEW_MASTER /sbin/arping -c 5 -D -I eth0 -s $V_VIP_IP $V_VIP_IP

VIP_NOHUP_PS=`ps -ef| grep "ifconfig eth0:0" | grep ssh | grep -v grep | awk '{print $2}'` && kill -9 $VIP_NOHUP_PS


elif [ $V_EXIST_VIP_CHK = "100%" ]
then

echo "VIP IS dead, VIP Relocate $V_NEW_MASTER "
/usr/bin/ssh $V_NEW_MASTER /usr/bin/sudo /sbin/ifconfig eth0:0 $V_VIP_IP
/usr/bin/ssh $V_NEW_MASTER /sbin/arping -c 5 -D -I eth0 -s $V_VIP_IP $V_VIP_IP

fi

-- 현재 사용하는 NIC : eth0, VIP이름 : mha-vip

 

chmod 755 mha_change_vip.sh

 

 

./mha_change_vip.sh 192.168.136.155
ping mha-vip -c 4

./mha_change_vip.sh 192.168.136.156
ping mha-vip -c 4

 

MHA 설정 및 생성 완료

MHA 모니터링 부터는 추후 업로드 예정