[MariaDB] MHA + Replication 구성하기

1.  사전 준비 환경

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

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 설치

 

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​

 

3.  MariaDB Master / Slave 구성

• 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에도 생성되었는지 확인

 

4.  MHA 설치

• 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

 

5.  설정 확인

sshcheck

replcheck

 

6.  Slave IP <=> Master IP 변경 스크립트 설정 

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

 

7.  Failover 스크립트 설정

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