MariaDB 10, MHA Replication 구성

CentOS 7 + MariaDB 10.2 + MHA 0.57 조합으로 구성
서버 (3개), ip는 vip로 구성해도 될 것으로 보임.

노드	IP	Role
MHA Manager Server	192.168.0.150	(mgr)
Master DB Server	192.168.0.151	(masterdb)
Slave DB Server	192.168.0.152	(slavedb01)

방화벽 3306 포트 개방
모든 서버에 MariaDB 10.2 설치

필요 패키지 설치

yum install epel-release -y
yum install net-tools sysstat wget lrzsz lsof htop iftop rsync bzip2 unzip patch syslog -y

MHA 관련

yum -y install epel perl-devel perl-CPAN perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Module-Install

DB 실행(Master,Slave)

service mysql start

root 패스워드 변경(Master,Slave)

mysql_secure_installation

  Enter current password for root (enter for none): enter
  Set root password? [Y/n] y
  Remove anonymous users? [Y/n] y
  Disallow root login remotely? [Y/n] n
  Remove test database and access to it? [Y/n] n
  Reload privilege tables now? [Y/n] y

root 접속 테스트(Master,Slave)

shell >> mysql -uroot -p

mysql >> show global variables like '%dir%';

  base: /usr
  datadir: /var/lib/mysql/

Replication 유저 생성(Master,Slave)

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

MHA 유저 생성(Master,Slave)

grant all privileges on *.* to 'mha'@'%' identified by 'mha';
flush privileges;

설정 위해 DB 종료(Master,Slave)

mysql >> exit

shell >> service mysql stop

환경변수 설정

vi /etc/my.cnf.d/server.cnf

# Master DB Server에서
[mariadb]
server_id=1
log-bin=mysql-bin

# Slave DB Server에서
[mariadb]
server_id=2
log-bin=mysql-bin
read_only=1
relay_log_purge=0

다시 DB 실행(Master,Slave)

service mysql start

Master 서버에서 로그 파일과 위치 확인(Master)

show master status;

Slave 서버에서 Replication 세팅(Slave)

change master to
master_host='192.168.0.151', 
master_user='rep', 
master_password='rep', 
master_port=3306, 
master_log_file='mysql-bin.000005',
master_log_pos=342,
master_connect_retry=10;

start slave;

show slave status\G;

Slave_IO_State, Slave_IO_Running, Slave_SQL_Running이 다음과 같은 상태여야 함.

실제로 Replication이 동작하는지 Master 서버에서 데이터베이스와 테이블 생성하고 Slave에도 생성되었는지 확인

MHA 프로그램이 사용할 디렉토리 생성 (모든 서버)

mkdir /mha

MHA 유저 생성 (모든 서버)

useradd -g mysql -d /home/mhauser -m -s /bin/bash mhauser

passwd mhauser
mhauser

chown -R mhauser:mysql /mha

MHA node 설치 (모든 서버)

mkdir /source
cd /source

wget http://cliel.com/data/mha4mysql-node-0.57.tar.gz
tar xvzf mha4mysql-node-0.57.tar.gz

cd /source/mha4mysql-node-0.57
perl Makefile.PL
make
make install

MHA Manager 설치 (MHA Manager 서버)

cpan YAML
perl -MCPAN -e "install File::Remove"
perl -MCPAN -e "install Build"
perl -MCPAN -e "install Module::Install"  
perl -MCPAN -e "install Net::Telnet"
perl -MCPAN -e "install Log::Dispatch"

cd /source
wget http://cliel.com/data/mha4mysql-manager-0.57.tar.gz
tar xvzf mha4mysql-manager-0.57.tar.gz

cd /source/mha4mysql-manager-0.57
perl Makefile.PL
make
make install

SSH 연결. MHA 모니터링과 Failover를 수행하기 위해선 각각의 서버들이 서로 간에 비밀번호 없이 SSH 접속할 수 있어야 함.(모든서버)

visudo

# 맨 아래에 추가
mhauser ALL = (ALL) NOPASSWD:/sbin/ifconfig

각 서버에서 다른 서버들에게 공개키를 줘야 함. (MHA Manager → Master DB, Slave DB / Master DB → Slave DB, MHA Manager / Slave DB → MasterDB, MHA Manager)

키 생성 :
	su - mhauser
	ssh-keygen -t rsa [엔터 3번]

키 복사 :
	ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.151
	ssh-copy-id -i ~/.ssh/id_rsa.pub mhauser@192.168.56.152

연결 테스트 :
	ssh 192.168.56.151
	ssh 192.168.56.152

파일의 내용이 변경되지 않도록 권한 수정(모든 서버)

su - mhauser
cd .ssh
chmod 400 authorized_keys

MHA Manager 명령어 커스텀마이징(MHA Manager 서버)

vi .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_profile

MHA 사용할 config 파일 생성(MHA Manager 서버)

cp /source/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/mha.cnf

vi /etc/mha.cnf

[server default]
user=mha
password=mha1122

ssh_user=mhauser

repl_user=rep
repl_password=rep1122

manager_workdir=/mha
manager_log=/mha/manager.log
remote_workdir=/mha

master_binlog_dir=/var/lib/mysql

[server1]
hostname=192.168.0.151
candidate_master=1

[server2]
hostname=192.168.0.152
candidate_master=1

sshcheck

replcheck

 
mkdir /mha/scripts

cp /source/mha4mysql-manager-0.57/samples/scripts/master_ip_online_change /mha/scripts/master_ip_online_change

vi /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();

vi /etc/mha.cnf 수정 (master_ip_online_change 파일 경로 추가)


[server default]
user=mha
password=mha

ssh_user=mhauser

repl_user=rep
repl_password=rep1122

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   

[server1]
hostname=192.168.0.151
candidate_master=1

[server2]
hostname=192.168.0.152
candidate_master=1

cp /source/mha4mysql-manager-0.57/samples/scripts/master_ip_failover /mha/scripts/

vi /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;

vi /etc/mha.cnf 수정 (Failover 스크립트 설정)

[server default]
user=mha
password=mha1122

ssh_user=mhauser

repl_user=rep
repl_password=rep1122

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.0.151
candidate_master=1

[server2]
hostname=192.168.0.152
candidate_master=1