Mysq-5.6 MHA 구성

###### Mysql MHA 구성 ##################

0) 환경 구성

- 아이피 구성

master : 192.168.0.111 (hostnamae: mha1)

slave : 192.168.0.112 (hostnamae: mha2)

slave(mon) : 192.168.0.113 (hostnamae: mha3)

- OS : centos 6.8 64bit

1) mysql 설치  (모든 node 동일)

echo "192.168.0.111 mha1" >> /etc/hosts

echo "192.168.0.112 mha2" >> /etc/hosts

echo "192.168.0.113 mha3" >> /etc/hosts

yum install gcc.x86_64 gcc-c++.x86_64 wget.x86_64 bzip2-devel.x86_64 pkgconfig.x86_64 openssl-devel.x86_64 make.x86_64 man.x86_64 nasm.x86_64 gmp.x86_64 gdbm-devel.x86_64 readline-devel.x86_64 compat-readline43.x86_64 ncurses-devel.x86_64 db4-devel.x86_64 automake* autoconf* -y

 yum install perl-CGI -y

yum -y install rdate  cmake lrzsz openssh-clients 

rdate -s time.bora.net

cd /usr/local/src

wget http://ftp.kaist.ac.kr/mysql/Downloads/MySQL-5.6/mysql-5.6.31.tar.gz

tar xvzf mysql-5.6.31.tar.gz

cd mysql-5.6.31 

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FEDERATEDX_STORAGE_ENGINE=1 \

-DWITH_ARIA_STORAGE_ENGINE=1 \

-DWITH_XTRADB_STORAGE_ENGINE=1 \

-DMYSQL_DATADIR=/usr/local/mysql/data \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

-DSYSCONFDIR=/etc \

-DMYSQL_TCP_PORT=3306

make && make  install

groupadd mysql && useradd -M -g mysql mysql

chown -R mysql /usr/local/mysql

chown -R mysql /usr/local/mysql/data

chgrp -R mysql /usr/local/mysql

echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

. /etc/profile

cd /usr/local/mysql

./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

mkdir /var/run/mysqld

chown mysql /var/run/mysqld

chgrp mysql /var/run/mysqld

cd /usr/local/mysql/support-files

cp -aR my-default.cnf /etc/my.cnf

cp -aR mysql.server  /etc/init.d/mysqld

/etc/init.d/mysqld restart

## mysql_secure_installation 이용하여 root 패스워드 및 미사용 계정 삭제

mysql_secure_installation

chkconfig --add mysqld

2) mysql replication

## master node 구성을 위해 master node 에 database 생성 및 db 입력 (db는 대충 랜덤값으로 생성 )

mysql -uroot -p

mysql> create database dummy;

mysql> flush privileges;

mysql> exit

mysql -uroot -p dummy  <test.sql

##  master node 에 my.cnf 수정

## server_id 는 모든서버가 uniq 하게 설정 하며, 추후 master slave 변경을 위한 relay-log 설정

## max_binlog_size 2G로 설정 기본 1G

## 로스 삭제 주기 15일로 자동 삭제 되도록 설정

vi /etc/my.cnf

[mysqld]

server_id = 101

log_bin=mysql-bin

relay-log=mysqld_relay_bin

max_binlog_size=2000M

expire_logs_days=15

bind-address = 0.0.0.0

## replication mysql 계정 추가

mysql -uroot -p

mysql> grant replication slave, replication client on *.* to repl@'192.168.0.%' identified by '12345';

## mstart node 재시작 후 master position 및 바이너리 로그 파일 명 확인

 /etc/init.d/mysqld restart

[root@mha1 ~]# echo " show master status; " | mysql -uroot -p       

Enter password:

File    Position        Binlog_Do_DB    Binlog_Ignore_DB        Executed_Gtid_Set

mysql-bin.000001        350

## relication 서버에반영될 master 서버 db dump 작업

mysql -uroot -p

mysql > FLUSH TABLES WITH READ LOCK;

mysql > exit

mysqldump -u root -p --all-databases > /tmp/database-backup.sql

mysql -uroot -p

mysql > UNLOCK TABLES;

## scp로 replication 서버에 전달

scp /tmp/database-backup.sql root@mha2:/tmp   

scp /tmp/database-backup.sql root@mha3:/tmp

##  slave node 에 my.cnf 수정

## 기본 설정 자체는 master 와 동일 read-only 설정 하여 일반 계정의 write 금지 하도록 설정

## server id는 uniq 하게 설정

vi /etc/my.cnf

server_id = 102

log_bin=mysql-bin

relay-log=mysqld_relay_bin

max_binlog_size=2000M

expire_logs_days=15

bind-address = 0.0.0.0

read_only=1

relay_log_purge=0

## slave node db재시작

/etc/init.d/mysqld restart

## slave node 에서 master 서버 아이피 replication 계정 아이디 비번 및 master서버 로그 파일 번호 포지션 확인

## (mstart 에서 show master stat \G; 로 확인

mysql -uroot -p

mysql> CHANGE MASTER TO master_host='192.168.0.111', master_port=3306, master_user='repl',

master_password='12345', master_log_file='mysql-bin.000001', master_log_pos=350;

mysql> exit

## scp 로 전달한 master db 파일 slave 에 적용

mysql -uroot -p < /tmp/database-backup.sql

## slave 에서 db쉘을 통하여 start slave 로 replication 시작

## 이상이 없다면 slave io,sql 쓰레드가 아래와 같이 yes 로 나옴

## master 에서 db에 변경 사항이 있을 경우 slave 에서 master 의 binlog와 포지션이 slave와 동일 한지 체크 한다.

## stop slave; -> reset slave; -> start slave ; 명령어로 slave 정보를 갱신하여 master 와의  replication 정보를 다시 맞출수있따.

mysql -uroot -p

mysql> start slave;

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.111

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 350

               Relay_Log_File: mysqld_relay_bin.000004

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 350

              Relay_Log_Space: 937

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 101

                  Master_UUID: 7e7c187d-5e35-11e6-b92d-000c294de04b

             Master_Info_File: /usr/local/mysql/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

3) mha node 및 manager 설치

## mha node설치 진행 (모든 노드 진행 )

yum install epel -y

yum install perl-DBD-MySQL -y

yum install perl-Config-Tiny  -y

yum install perl-Log-Dispatch  -y

yum install perl-Parallel-ForkManager  -y

yum install perl-devel -y

yum install perl-CPAN -y

mkdir -p  /mysql/program

cd /mysql/program

wget https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-node-0.56.tar.gz

tar xvzf mha4mysql-node-0.56.tar.gz

cd mha4mysql-node-0.56

perl Makefile.PL  && make  && make install

## mha manager 설치 (slave 모니터링 서버만 설치)

cd /mysql/program

wget https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-manager-0.56.tar.gz

tar xvzf mha4mysql-manager-0.56.tar.gz

cd /mysql/program/mha4mysql-manager-0.56

perl Makefile.PL  && make  && make install

## mha 에서 mysql binay 파일 경로 변경 (소스컴파일시)

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

4) mha 계정 생성 및 ssh 자동로그인을 위한 공개키 설정

## 모든 노드 설정

useradd mhauser

passwd mhauser

 usermod  -G mysql mhauser

su mhauser

ssh-keygen

##해당 내용 모두 summary 후 해당 내용 각 서버/home/mhauser/.ssh/authorized_keys  에 저장  (mhauser 계정으로 )

cat /home/mhauser/.ssh/id_rsa.pub

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA2jKJCmP8poOh/790WzwTogMO9fYUBF80aQBd/om/HLFHY5AYbCq1CQJLtJD+cVnAbhbPHV85FqIlIC8ST45xpjfhSZ7MGs+/jJeyl75J3xkk1Pbn5TGNiV2v8bWjsXWL8ekxgXYxkh7rts0jxA35u2NMhz99mZdellZGC/BkMA3t0RUvjQEIluiPNsYWAOZkIiij1Qwoi84Idm4j5mo5/0g/cUv/ZTcCi012cYA6PEqCEPOh35woaAzMy438ESsgjBa0yUJ+ypls5DyS7COOckndToRBqUzl4YeLDFo2LJAAjJN0AndpizhbHO4lI8q4FS1R1PnLivftzmbyOGnAYQ== mhauser@mha1

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAxzPlFl0+mJDsLCjLZn0eNJ3jbtiLg2wJTurmGDv4kjM0ofeHbY1SdpQqRCRRp739sFZc/oum794wTXMYNtz9OGOkJIyjqMnYe7vrgeKmdOQvGdRHPQU4EYhKJIc5G7cWhLlJLObpvNh+IXY2WxGtLzKPDRjlMngzasyfqId/TQBFwUKmb/RUC1d/ZwEBjQpauc3IQrQe60QbDizmX/OISrZA3f38b4xkGPLhlfs6zZL2yIOvm3E7TwoRz3cJly6mllV7a+hoUYVmunhb77zhfYm6Bz5R9+AQ4xPX/REYJfbOR6nMoHpp9YCrVeIueA2VP/Mx0eqL/6WDWzfaqW7Hyw== mhauser@mha2

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA1RO+GQdyD/aJZA6aZmTW+lcGbYzSnCtut6TAA8mGE3wBLPDMi2354PGlQbHHX45t27BHUMqPsLeNppuVYvWefvjEZMirrTFHpECkS+6dBBdH7QFTgmpaSwQ9L63999+umHkVvKum6r2/uR2X6EXNX92AwZkqNpZreVOuTsLuaZFY/DXaSJ/9Qa+Zq2xwHtgB2ittfMi2ritDGm6TfqdPaiyZGg3QYvYQi5jyYz38CS5yCO4tW+jrlgeWCJY0TSUNqKOmLidVdf1caBOzVlW1yneFK+PW/iRkBgDH9lR5EF4Jg0APEi8J1nXpEnzdQAee//vBg4d8Zw4OIArM7wkuXQ== mhauser@mha3

install /dev/null -m 400 /home/mhauser/.ssh/authorized_keys

vi /home/mhauser/.ssh/authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA2jKJCmP8poOh/790WzwTogMO9fYUBF80aQBd/om/HLFHY5AYbCq1CQJLtJD+cVnAbhbPHV85FqIlIC8ST45xpjfhSZ7MGs+/jJeyl75J3xkk1Pbn5TGNiV2v8bWjsXWL8ekxgXYxkh7rts0jxA35u2NMhz99mZdellZGC/BkMA3t0RUvjQEIluiPNsYWAOZkIiij1Qwoi84Idm4j5mo5/0g/cUv/ZTcCi012cYA6PEqCEPOh35woaAzMy438ESsgjBa0yUJ+ypls5DyS7COOckndToRBqUzl4YeLDFo2LJAAjJN0AndpizhbHO4lI8q4FS1R1PnLivftzmbyOGnAYQ== mhauser@mha1

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAxzPlFl0+mJDsLCjLZn0eNJ3jbtiLg2wJTurmGDv4kjM0ofeHbY1SdpQqRCRRp739sFZc/oum794wTXMYNtz9OGOkJIyjqMnYe7vrgeKmdOQvGdRHPQU4EYhKJIc5G7cWhLlJLObpvNh+IXY2WxGtLzKPDRjlMngzasyfqId/TQBFwUKmb/RUC1d/ZwEBjQpauc3IQrQe60QbDizmX/OISrZA3f38b4xkGPLhlfs6zZL2yIOvm3E7TwoRz3cJly6mllV7a+hoUYVmunhb77zhfYm6Bz5R9+AQ4xPX/REYJfbOR6nMoHpp9YCrVeIueA2VP/Mx0eqL/6WDWzfaqW7Hyw== mhauser@mha2

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA1RO+GQdyD/aJZA6aZmTW+lcGbYzSnCtut6TAA8mGE3wBLPDMi2354PGlQbHHX45t27BHUMqPsLeNppuVYvWefvjEZMirrTFHpECkS+6dBBdH7QFTgmpaSwQ9L63999+umHkVvKum6r2/uR2X6EXNX92AwZkqNpZreVOuTsLuaZFY/DXaSJ/9Qa+Zq2xwHtgB2ittfMi2ritDGm6TfqdPaiyZGg3QYvYQi5jyYz38CS5yCO4tW+jrlgeWCJY0TSUNqKOmLidVdf1caBOzVlW1yneFK+PW/iRkBgDH9lR5EF4Jg0APEi8J1nXpEnzdQAee//vBg4d8Zw4OIArM7wkuXQ== mhauser@mha3

## visudo로 mhauser 계정 권한 추가 (root계정으로 )

visudo

mhauser ALL=(ALL) NOPASSWD:/sbin/ifconfig << 추가

## 각서버에서 mhauser 계정으로 hostname 명령어를 통하여 nopassword 설정 확인  (mhauser 계정으로 )

ssh mha1 hostname

ssh mha2 hostname

ssh mha3 hostname

4) mha 매니저 설정

## 모든 노드에서 매니저가 root 권한으로 mysql 에 접속 할 수 있도록 root 호스트 추가 

mysql -uroot -p'12345'

use mysql;

grant all on *.* to root@'192.168.0.%' identified by '12345';

flush privileges;

exit

## 모든노드에 mha 설정 파일이 저장될 디렉터리 생성 및 권한 추가

mkdir /mysql/mha

chown -R mhauser:mysql /mysql/mha

chown  mhauser:mysql -R /mysql/

touch /etc/masterha_default.cnf

## manager 계정으로 mha 설정 진행 (매니저 노드만 진행)

## 설정은 아래와 같이 하되, master 후보는 candidate_master 1 로 설정해야지만 master 로 진행 가능

su - mhauser

vi /mysql/mha/app1.cnf

[server default]

ping_interval = 3

ping_type     = select

## 사용자 패스워드

user=root

password=12345

ssh_user=mhauser

repl_user=repl

repl_password=12345

##mha 매니저 실행 정보 저장 디렉터리 로그 파일

manager_workdir=/mysql/mha/app1

manager_log=/mysql/mha/app1.log

## mysql 서버의 실행 디렉터리

remote_workdir=/mysql/mha/app1

master_binlog_dir=/usr/local/mysql/data

[server1]

hostname=mha1

candidate_master=1

[server2]

hostname=mha2

candidate_master=1

[server3]

hostname=mha3

## 아래 명령어로 replication 과 ssh test 진행

masterha_check_ssh --conf /mysql/mha/app1.cnf

masterha_check_repl --conf /mysql/mha/app1.cnf

## 지금 master 로 inster 되는 서버의 vip 설정

 ifconfig  eth0:0 192.168.0.114 netmask 255.255.255.128 up

## faili시 사용 될 스크립트 작성 (매니저 노드 )

## 스크립트 중간 $ssh_mac_refresh, vip 부분만 master vip 로 변경

install -m 777 /dev/null  /usr/bin/mha_failover

vi  /usr/bin/mha_failover

#!/usr/bin/env perl

# use strict;

# use warnings FATAL => 'all';

use Getopt::Long;

my (

        $command,     

        $ssh_user,   

        $orig_master_host,

        $orig_master_ip,

        $orig_master_port,

        $new_master_host,

        $new_master_ip,

        $new_master_port,

        $new_master_user,

        $new_master_password

);

my $vip = '192.168.80.155/24';  # Virtual IP

my $key = "0";

my $ssh_start_vip   = "sudo /sbin/ifconfig eth0:$key $vip";

my $ssh_stop_vip    = "sudo /sbin/ifconfig eth0:$key down";

my $ssh_mac_refresh = "sudo /sbin/arping -c3 -D -I eth0 -s 192.168.80.155 192.168.80.155";

GetOptions(

        'command=s'          => \$command,

        'ssh_user=s'         => \$ssh_user,

        'orig_master_host=s' => \$orig_master_host,

        'orig_master_ip=s'   => \$orig_master_ip,

        'orig_master_port=i' => \$orig_master_port,

        'new_master_host=s'  => \$new_master_host,

        'new_master_ip=s'    => \$new_master_ip,

        'new_master_port=i'  => \$new_master_port,

        'new_master_user=s'  => \$new_master_user,

        'new_master_password=s' => \$new_master_password

);

exit &main();

sub main {

 print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

 if ( $command eq "stop" || $command eq "stopssh" ) {

        print $command;

     # $orig_master_host, $orig_master_ip, $orig_master_port are passed.

     # If you manage master ip address at global catalog database,

     # invalidate orig_master_ip here.

     my $exit_code = 1;

     eval {

         print "Disabling the VIP on old master: $orig_master_host \n";

         &stop_vip();

         $exit_code = 0;

     };

     if ($@) {

         warn "Got Error: $@\n";

         exit $exit_code;

     }

     exit $exit_code;

 }

 elsif ( $command eq "start" ) {

     # all arguments are passed.

     # If you manage master ip address at global catalog database,

     # activate new_master_ip here.

     # You can also grant write access (create user, set read_only=0, etc) here.

     my $exit_code = 10;

     eval {

         print "Enabling the VIP - $vip on the new master - $new_master_host \n";

         &start_vip();

         $exit_code = 0;

     };

     if ($@) {

         warn $@;

         exit $exit_code;

     }

     exit $exit_code;

 }

 elsif ( $command eq "status" ) {

     print "Checking the Status of the script.. OK \n";

        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;

     exit 0;

 }

 else {

     &usage();

     exit 1;

 }

}

# A simple system call that enable the VIP on the new master

sub start_vip() {

        `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

        `ssh $ssh_user\@$new_master_host \" $ssh_mac_refresh\"`;

}

# A simple system call that disable the VIP on the old_master

sub stop_vip() {

        `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

}

sub usage {

 print

 "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";

} 

#########

## app.cnf 파일 기본 설정에 failover 시 아래 스크립트 작동 하도록 추가

vi /mysql/mha/app1.cnf

#master_ip_failover_script= master_ip_online_change

master_ip_failover_script=/usr/bin/mha_failover

## nohup 으로 대몬 형식으로 확인

nohup masterha_manager --conf /mysql/mha/app1.cnf  < /dev/null > /mysql/mha/app1.log 2>&1 &

## tail 명령를 통하여 로그 확인

$ tail -f /mysql/mha/app1.log

mha1(192.168.0.111:3306) (current master)

 +--mha2(192.168.0.112:3306)

 +--mha3(192.168.0.113:3306)

Wed Aug 10 10:50:07 2016 - [warning] master_ip_failover_script is not defined.

Wed Aug 10 10:50:07 2016 - [warning] shutdown_script is not defined.

Wed Aug 10 10:50:07 2016 - [info] Set master ping interval 3 seconds.

Wed Aug 10 10:50:07 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.

Wed Aug 10 10:50:07 2016 - [info] Starting ping health check on mha1(192.168.0.111:3306)..

Wed Aug 10 10:50:07 2016 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

## master node reboot 시 candidate_master node에 vip 추가 사항 확인 및 master 확인

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: 192.168.0.112

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 416

               Relay_Log_File: mysqld_relay_bin.000001