MHA 설치(new)

(1)

MHA 설치(new)

MHA 구성 설치정보

구축환경 mysql 설치

유저생성 db 초기화 script 이중화 생성 MHA 설치

펄 모듈 설치 MHA 패키지 받기 MHA 노드 설치 MHA 매니저 설치

MHA 매니저 서버에만 설치한다.

설치 경로 설정 및 패키지 extract Make

make 오류 대응 사용자 계정 생성 SSH 설정

manager 서버의 IP를 각 hosts.allow 에 각 서버 등록 ssh 키 설정

ssh 키 생성 ssh 키 복사 ssh key 권한 변경 VIP 설정 실행을 위한 권한 설정 DB 접속계정 생성

MHA 동작 디렉토리 생성 MHA 설정

MHA 매니저 서버 설정

글로벌(Global) 설정 파일 Manager 설정파일 .bashrc 환경설정 바이너리 로그 설정

권한 설정

권한 설정 동작 확인 VIP 설정

MHA 제어 및 모니터링 MHA 명령어

주요 명령어 script 설명 및 역할 bin 경로 아래 실행파일 목록 masterha_manager

masterha_check_status masterha_check_ssh masterha_check_repl masterha_stop masterha_conf_host masterha_master_switch

maserha_master_switch 옵션 masterha_master_switch 사용예

마스터 DB가 online일때 사용가능 masterha_master_switch 옵션 purge_relay_logs

master_ip_online_change로 vip 스위치오버 설정하기 master_ip_online_change를 편집

change_vip.sh 생성

MHA manager config 파일을 수정 master_ip_online_change 동작테스트 master_ip_failover 스크립트

master_ip_failover 수정 설정파일변경

MHA 테스트

테스트용 스크립트

MHA manager 제어용 편의 script master 서버 초기화용 script 자동 페일오버 테스트

MHA 매니저 구동 마스터 DB stop MHA 매니저 로그

Failover 정상동작여부 확인 주의사항!!

수동 페일오버테스트

마스터DB라 alive 일때 마스터 DB가 dead 일때 참고

(2)

MHA 구성

역할 IP 호스트명 MHA 타입

마스터 DB 192.168.56.101 data1 MHA 노드 Slave DB1 192.168.56.102 data2 MHA 노드 Slave DB2 192.168.56.103 data3 MHA 노드 MHA 매니저

proxySQL app 구동

192.168.56.104 mgrdb MHA 매니저

설치정보

구축환경

종류 버전

OS CentOS Linux release 7.6.1810 (Core) MHA Manager mha4mysql-manager-0.57

MHA Node mha4mysql-node-0.57

MySQL Percona-Server-8.0.26-16-Linux.x86_64

mysql 설치 유저생성

[root@data1 mysql]# useradd -g mysql -d /home/bos -m -s /bin/bash bos ;

이하 mysql 설치 절차는 https://conf.mysoftwiz.com:8444/x/JgLdAQ 를 참조한다.

db 초기화 script

MYSQL_HOME=$HOME/mysql ROOT_PASSWD=iamroot OWNER=$USER

IP=10.1.5.23 PORT=3306

NODE_NAME=`hostname`

SERVER_ID=1

BASEDIR=$MYSQL_HOME DATADIR=$BASEDIR/data TMPDIR=$BASEDIR/tmp

(3)

LOGDIR=$BASEDIR/log ETCDIR=$BASEDIR/etc

SOCKET_FILE=$TMPDIR/mysql.sock

PID_FILE=$TMPDIR/${NODE_NAME}.pid

LOG_ERROR_FILE=$LOGDIR/${NODE_NAME}.log

GENERAL_LOG_FILE=$LOGDIR/${NODE_NAME}_general.log SLOW_LOG_FILE=$LOGDIR/${NODE_NAME}_slow_query.log

MYSQL_OPT="-u root -p$ROOT_PASSWD --socket=$SOCKET_FILE"

MYSQL_CMD="mysql $MYSQL_OPT "

MYCNF_FILE=$ETCDIR/my.cnf

PARAM="

[mysqld]

server-id = $SERVER_ID user = $OWNER port = $PORT basedir = $BASEDIR datadir = $DATADIR tmpdir = $TMPDIR socket = $SOCKET_FILE

character-set-server = utf8mb4

collation-server = utf8mb4_0900_ai_ci default-storage-engine = InnoDB

skip-name-resolve skip-external-locking

default_authentication_plugin = mysql_native_password

event-scheduler = OFF sysdate-is-now

back_log = 1024 max_connections = 600 max_user_connections = 600 max_connect_errors = 999999 max_error_count = 1024 thread_cache_size = 50 table_open_cache = 4000 interactive_timeout = 86400 wait_timeout = 31536000

max_allowed_packet = 64M max_heap_table_size = 64M tmp_table_size = 512K sort_buffer_size = 128k join_buffer_size = 128k

(4)

read_buffer_size = 128k read_rnd_buffer_size = 128k

group_concat_max_len = 1024

transaction-isolation = READ-COMMITTED

innodb_stats_on_metadata = 1 innodb_max_dirty_pages_pct = 90 innodb_adaptive_hash_index = 1 innodb_strict_mode = 0 innodb_io_capacity = 16000 innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_autoinc_lock_mode = 2 innodb_adaptive_flushing = 1

innodb_change_buffering = inserts innodb_old_blocks_time = 500

innodb_buffer_pool_size = 4096M innodb_buffer_pool_instances = 2 innodb_buffer_pool_chunk_size = 1G innodb_file_per_table = 1

innodb_data_file_path = ibdata1:10M:autoextend innodb_autoextend_increment = 100

innodb_log_buffer_size = 16M innodb_log_file_size = 1024M innodb_log_files_in_group = 2 innodb_thread_concurrency = 0 innodb_lock_wait_timeout = 60 innodb_flush_log_at_trx_commit = 1 innodb_force_recovery = 0

innodb_flush_method = O_DIRECT innodb_doublewrite = 1

innodb_sync_spin_loops = 20 innodb_table_locks = 1 innodb_thread_sleep_delay = 1000 innodb_max_purge_lag = 0 innodb_commit_concurrency = 0 innodb_concurrency_tickets = 500

pid-file = $PID_FILE

log-error = $LOG_ERROR_FILE general_log = 0

general_log_file = $GENERAL_LOG_FILE log_slow_admin_statements = on

slow_query_log = 1 long_query_time = 1

slow_query_log_file = $SLOW_LOG_FILE

#log_bin = $MYSQL_HOME/data/binlog binlog_cache_size = 128k

max_binlog_size = 1G

(5)

log_bin_trust_function_creators = 1 sync_binlog = 1 binlog_format = ROW gtid_mode = on enforce_gtid_consistency = on log_slave_updates = on

"

VALIDATE_PASSWORD_PARAM="

validate_password.policy = LOW validate_password.length = 4

"

SEMI_SYNC_PARAM="

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=100000 # 1second rpl_semi_sync_slave_enabled=1

"

my_kill() {

ps -ef | grep mysqld | grep -v grep | awk '{ print "kill -9 "$2 }' | sh }

my_clean() {

rm -rf $MYSQL_HOME/data rm -rf $MYSQL_HOME/tmp #rm -rf $MYSQL_HOME/log rm -rf $MYSQL_HOME/etc }

my_init(){

mkdir -p $DATADIR mkdir -p $TMPDIR mkdir -p $LOGDIR mkdir -p $ETCDIR

echo "$PARAM" | sed -e 's/\$MYSQL_HOME/$MYSQL_HOME/g' > $MYCNF_FILE

bin/mysqld --no-defaults --initialize-insecure --user=$OWNER -- datadir=$DATADIR --basedir=$BASEDIR

}

my_setrootpw(){

while true;

do

mysql -u root --skip-password 2> /dev/null <<-EOF

ALTER USER 'root'@'localhost' IDENTIFIED BY '$ROOT_PASSWD';

EOF

if [ $? -eq 0 ];

then

echo "set root@localhost password. succeed."

break

(6)

else

echo "retry. set root@localhost password..."

sleep 2 fi

done }

my_installcomponent(){

$MYSQL_CMD -e "INSTALL COMPONENT 'file://component_validate_password'"

$MYSQL_CMD -e "install plugin rpl_semi_sync_master soname 'semisync_master.so'"

$MYSQL_CMD -e "install plugin rpl_semi_sync_slave soname 'semisync_slave.so'"

}

my_adduser(){

# $MYSQL_CMD -e "create user root identified with mysql_native_password by '$ROOT_PASSWD'"

# $MYSQL_CMD -e "grant all privileges on *.* to root with grant option"

$MYSQL_CMD -e "create user bos@'%' identified with mysql_native_password by 'bosdb'"

$MYSQL_CMD -e "create user repl@'%' identified with mysql_native_password by 'repl'"

$MYSQL_CMD -e "grant replication slave on *.* to repl@'%'"

}

my_sampledb() {

$MYSQL_CMD -e "create database bosdb"

$MYSQL_CMD -e "grant all privileges on bosdb.* to 'bos'@'%'"

}

my_start(){

$MYSQL_HOME/bin/mysqld_safe --defaults-file=$ETCDIR/my.cnf -- user=$OWNER &

sleep 3 }

my_stop(){

$MYSQL_HOME/bin/mysqladmin $MYSQL_OPT shutdown }

my_addextraparam(){

echo "$VALIDATE_PASSWORD_PARAM" >> $MYCNF_FILE echo "$SEMI_SYNC_PARAM" >> $MYCNF_FILE }

if [ "$1" = "start" ];

then

my_start

elif [ "$1" = "stop" ];

then

(7)

my_stop

elif [ "$1" = "setrootpw" ];

then

my_setrootpw

elif [ "$1" = "kill" ];

then

my_kill

elif [ "$1" = "clean" ];

then

my_clean

elif [ "$1" = "init" ];

then

my_init

elif [ "$1" = "restart" ];

then

my_stop my_start

elif [ "$1" = "adduser" ];

then

my_adduser

elif [ "$1" = "dbinit" ];

then

my_kill my_clean my_init my_start my_setrootpw #my_adduser

my_installcomponent my_stop

my_addextraparam my_start

my_adduser my_sampledb fi

이중화 생성

(8)

reset master;

CHANGE MASTER TO

MASTER_HOST = '192.168.56.101', MASTER_PORT = 3306,

MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_AUTO_POSITION = 1;

start slave;

MHA 설치

펄 모듈 설치

모든 MHA 구성 서버에 공통 : MHA 매니저, Master DB, Slave DB

yum install perl-DBD-MySQL perl-Config-Tiny perl-Params-Validate perl- Parallel-ForkManager perl-Log-Dispatch per-Time-HiRes perl-CPAN

MHA 패키지 받기

패키징된 공식적인 버전은 다음과 같다.

항목 내용

down link https://github.com/yoshinorim/mha4mysql-manager/wiki /Downloads ← dead link 임

mha node mha4mysql-manager-0.56.tar.gz

mha manager mha4mysql-node-0.56.tar.gz

최신버전 소스를 받아서 설치하는 경우

항목 내용

mha node https://github.com/yoshinorim/mha4mysql-node mha manager https://github.com/yoshinorim/mha4mysql-manager

최신버전 0.57 버전은 binary 파일을 찾기 어렵다. 첨부 파일로 첨부하니 여기 바로 다운로드 받아서 사용한다.

항목 내용

mha manager ( 0.57 버전 ) mha4mysql-manager-0.57.tar.gz

mha node ( 0.57 버전 ) mha4mysql-node-0.57.tar.gz

MHA 노드 설치

(9)

모든 MHA 구성 서버에 공통 : MHA 매니저/마스터 DB/Slave DB

#

# mkdir -p /mysql/mha

# cd /mysql/mha

# tar xvzf mha4mysql-node-0.57.tar.gz

[root@data1 mha4mysql-node-0.57]# perl Makefile.PL INSTALL_BASE=/mysql /mha <--

*** Module::AutoInstall version 1.06

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.627) - DBD::mysql ...loaded. (4.023)

*** Module::AutoInstall configuration finished.

Writing Makefile for mha4mysql::node [root@data1 mha4mysql-node-0.57]#

[root@data1 mha4mysql-node-0.57]#

[root@data1 mha4mysql-node-0.57]# make <-- make cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm

cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm

cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm

cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm

cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm

cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog

/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script /filter_mysqlbinlog

cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs

/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script /apply_diff_relay_logs

cp bin/purge_relay_logs blib/script/purge_relay_logs

/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script /purge_relay_logs

cp bin/save_binary_logs blib/script/save_binary_logs

/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script /save_binary_logs

Manifying blib/man1/filter_mysqlbinlog.1 Manifying blib/man1/apply_diff_relay_logs.1 Manifying blib/man1/purge_relay_logs.1 Manifying blib/man1/save_binary_logs.1 [root@data1 mha4mysql-node-0.57]#

[root@data1 mha4mysql-node-0.57]#

[root@data1 mha4mysql-node-0.57]# make install <--- install Installing /mysql/mha/lib/perl5/MHA/BinlogManager.pm

Installing /mysql/mha/lib/perl5/MHA/BinlogPosFindManager.pm Installing /mysql/mha/lib/perl5/MHA/BinlogPosFinderXid.pm

(10)

Installing /mysql/mha/lib/perl5/MHA/BinlogHeaderParser.pm Installing /mysql/mha/lib/perl5/MHA/BinlogPosFinder.pm Installing /mysql/mha/lib/perl5/MHA/NodeUtil.pm

Installing /mysql/mha/lib/perl5/MHA/BinlogPosFinderElp.pm Installing /mysql/mha/lib/perl5/MHA/SlaveUtil.pm

Installing /mysql/mha/lib/perl5/MHA/NodeConst.pm Installing /mysql/mha/man/man1/filter_mysqlbinlog.1 Installing /mysql/mha/man/man1/apply_diff_relay_logs.1 Installing /mysql/mha/man/man1/purge_relay_logs.1 Installing /mysql/mha/man/man1/save_binary_logs.1 Installing /mysql/mha/bin/filter_mysqlbinlog Installing /mysql/mha/bin/apply_diff_relay_logs Installing /mysql/mha/bin/purge_relay_logs Installing /mysql/mha/bin/save_binary_logs

Appending installation info to /mysql/mha/lib/perl5/x86_64-linux-thread- multi/perllocal.pod

[root@data1 mha4mysql-node-0.57]# ls -al /mysql/mha/ <---- 172

drwxr-xr-x. 6 root root 137 1126 18:22 . drwxr-xr-x. 3 root root 17 1126 17:46 ..

drwxr-xr-x. 2 root root 109 1126 18:22 bin drwxr-xr-x. 3 root root 19 1126 18:22 lib drwxr-xr-x. 3 root root 18 1126 18:22 man

MHA 매니저 설치

MHA 매니저 서버에만 설치한다.

설치 경로 설정 및 패키지 extract

#

# mkdir -p /mysql/mha

# cd /mysql/mha

# tar xvzf mha4mysql-node-0.57.tar.gz

Make

perl Makefile.PL INSTALL_BASE=/mysql/mha 로 설치지정 경로해서 make 준비한다.

(11)

[root@mgrdb mha]# cd mha4mysql-manager-0.57 [root@mgrdb mha4mysql-manager-0.57]#

[root@mgrdb mha4mysql-manager-0.57]# perl Makefile.PL INSTALL_BASE=/mysql /mha

[Core Features]

- DBI ...loaded. (1.627) - DBD::mysql ...loaded. (4.023) - Time::HiRes ...loaded. (1.9725) - Config::Tiny ...loaded. (2.14) - Log::Dispatch ...loaded. (2.41) - Parallel::ForkManager ...loaded. (1.18) - MHA::NodeConst ...missing.

==> Auto-install the 1 mandatory module(s) from CPAN? [y] y

*** Dependencies will be installed the next time you type 'make'.

Checking if your kit is complete...

Looks good

Warning: prerequisite MHA::NodeConst 0 not found.

Writing Makefile for mha4mysql::manager

make 오류 대응

아래 오류 발생시 해결책 부분 참조

(12)

Writing /root/.cpan/CPAN/MyConfig.pm for bootstrap...

commit: wrote '/root/.cpan/CPAN/MyConfig.pm' Fetching with HTTP::Tiny:

http://www.cpan.org/authors/01mailrc.txt.gz

Reading '/root/.cpan/sources/authors/01mailrc.txt.gz' ....

export PERL5LIB="/root/perl5/lib/perl5:$PERL5LIB";

export PATH="/root/perl5/bin:$PATH";

Would you like me to append that to /root/.bashrc now? [yes]

commit: wrote '/root/.cpan/CPAN/MyConfig.pm'

You can re-run configuration any time with 'o conf init' in the CPAN shell

*** Could not find a version 0 or above for MHA::NodeConst; skipping.

*** Module::AutoInstall installation finished.

Can't locate ExtUtils/Install.pm in @INC (@INC contains: inc /usr/local /lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share /perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).

BEGIN failed--compilation aborted.

make: *** [pm_to_blib] Error 2

컴파일 오류 해결책

(13)

# ( )

[root@mgrdb mha4mysql-manager-0.57]# yum install perl-ExtUtils- Install

[root@mgrdb mha4mysql-manager-0.57]# yum install perl-ExtUtils- MakeMaker

[root@mgrdb mha4mysql-manager-0.57]# yum -y install perl-CPAN [root@mgrdb mha4mysql-manager-0.57]# yum -y install perl-Module- Install

# mha node library perl lib

root@mgrdb mha4mysql-manager-0.57]# export PERL5LIB="/root/perl5/lib /perl5:$PERL5LIB:/mysql/mha/lib/perl5";

# make

[root@mgrdb mha4mysql-manager-0.57]# perl Makefile.PL INSTALL_BASE=

/mysql/mha

[Core Features]

- DBI ...loaded. (1.627) - DBD::mysql ...loaded. (4.023) - Time::HiRes ...loaded. (1.9725) - Config::Tiny ...loaded. (2.14) - Log::Dispatch ...loaded. (2.41) - Parallel::ForkManager ...loaded. (1.18) - MHA::NodeConst ...loaded. (0.57)

Generating a Unix-style Makefile

Writing Makefile for mha4mysql::manager Writing MYMETA.yml and MYMETA.json

### make

[root@mgrdb mha4mysql-manager-0.57]# make Skip blib/lib/MHA/ManagerUtil.pm (unchanged ...

...

"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib /script/masterha_stop

Manifying 9 pod documents

#### Make install

[root@mgrdb mha4mysql-manager-0.57]# make install Manifying 9 pod documents

Appending installation info to /mysql/mha/lib/perl5/x86_64-linux- thread-multi/perllocal.pod

(14)

사용자 계정 생성

모든 MHA 구성 서버 공통 : MHA 매니저/마스터 DB/슬레이브 DB

[root@data1 mysql]# useradd -g mysql -d /home/mha -m -s /bin/bash mha;

[root@data1 mysql]# passwd mha asdf1234 <--

SSH 설정

manager 서버의 IP를 각 hosts.allow 에 각 서버 등록

mha manager 서버는 각 노드 서버에 ssh 로 접속 가능해야 한다.

매니저 , DB 서버 공통으로 등록한다.

[root@mgrdb mha4mysql-manager-0.57]# vi /etc/hosts.allow

#for MHA

sshd:192.168.56.101,192.168.56.102,192.168.56.103,192.168.56.104

ssh 키 설정

ssh 키 생성

모든 서버에서 실행한다.

ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa

cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys chmod 0700 ~/.ssh/authorized_keys

ssh 키 복사

manager 서버에서 실행한다.

패스워드 입력없이 로그인이 가능해야 한다.

(15)

ssh-copy-id -i ~/.ssh/id_dsa.pub mha@192.168.56.101;

# .

[mha@mgrdb ~]$ ssh mha@192.168.56.101 Last login: Mon Nov 29 15:43:44 2021 [mha@data1 ~]$ exit

logout

Connection to 192.168.56.101 closed.

[mha@mgrdb ~]$ ssh mha@192.168.56.102 Last login: Mon Nov 29 15:44:08 2021 [mha@data2 ~]$ exit

logout

[mha@mgrdb ~]$ ssh mha@192.168.56.103 Last login: Mon Nov 29 15:44:24 2021 [mha@data3 ~]$

모든 db서버에서 실행한다.

패스워드 없이 로그인이 가능해야 한다.

# data1 .

[mha@data1 ~]$ ssh mha@192.168.56.102

Last login: Tue Nov 30 10:46:48 2021 from data1 [mha@data2 ~]$ exit

logout

[mha@data1 ~]$ ssh mha@192.168.56.103

Last login: Tue Nov 30 10:46:38 2021 from data1 [mha@data3 ~]$ exit

logout

[mha@data1 ~]$ ssh mha@192.168.56.104

Last login: Tue Nov 30 10:46:56 2021 from data2 [mha@mgrdb ~]$ exit

logout

ssh key 권한 변경

모든 서버에서 실행한다.

(16)

chmod 400 ~/.ssh/authorized_keys

VIP 설정 실행을 위한 권한 설정

fail over 를 위한 VIP 를 할당하고 이를 활성/비활성하기 위해서는 mha 유저에게 ifconfig 명령을 실행할 수 있는 권한 부여가 필요하다.

sudo 에 권한을 부여한다 모든 DB 서버에서 실행한다.

(17)

[root@data1 ~]# visudo

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

[root@data1 ~]# su - mha

Last login: Tue Nov 30 11:20:23 KST 2021 on pts/2 [mha@data1 ~]$ ifconfig -a

enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500

inet 10.0.2.15 netmask 255.255.255.0 broadcast 10.0.2.255 inet6 fe80::3c81:1b2d:9a95:6c48 prefixlen 64 scopeid 0x20<link>

ether 08:00:27:ef:24:99 txqueuelen 1000 (Ethernet) RX packets 1250 bytes 128116 (125.1 KiB)

RX errors 0 dropped 0 overruns 0 frame 0 TX packets 877 bytes 117003 (114.2 KiB)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.56.101 netmask 255.255.255.0 broadcast 192.168.56.255

inet6 fe80::6334:33fd:fd06:f9f8 prefixlen 64 scopeid 0x20<link>

ether 08:00:27:ef:6b:1e txqueuelen 1000 (Ethernet) RX packets 5547 bytes 658782 (643.3 KiB)

lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0

inet6 ::1 prefixlen 128 scopeid 0x10<host>

loop txqueuelen 1000 (Local Loopback) RX packets 71 bytes 4826 (4.7 KiB)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 [mha@data1 ~]$ sudo /sbin/ifconfig enp0s8 down <--- sudo

DB 접속계정 생성

MHA 매니저 서버에서 모든 DB 서버의 MySQL 에 접속할 수 있어야 한다.

해당 계정으로 레플리케이션 모니터링 및 기타 쿼리를 실행할 수 있어야 한다.

이중화가 실행중인 경우는 Master DB에서만 실행하면 된다.

(18)

# manager

create user root@'192.168.56.101' identified with mysql_native_password by 'mysql';

grant all on *.* to root@'192.168.56.101';

# local db script : purge_relay_log script

flush privileges;

MHA 동작 디렉토리 생성

MHA가 동작할 때 필요한 파일이 저장될 디렉토리르를 생성한다.

모든 서버( MHA 매니저, DB서버) 에서 생성한다.

(19)

# data1

[root@data1 mha]# pwd /mysql/mha

[root@data1 mha]# mkdir -p mhawork/bosdb

[root@data1 mha]# chown -R mha:mysql /mysql/mha/mhawork [root@data1 mha]#

# data2

[root@data2 ~]# cd /mysql/mha

[root@data2 mha]# chown -R mha:mysql /mysql/mha/mhawork

# data3

[root@data3 ~]# cd /mysql/mha

[root@data3 mha]# chown -R mha:mysql /mysql/mha/mhawork

# mha manager

[root@mgrdb ~]# cd /mysql/mha

[root@mgrdb mha]# mkdir -p mhawork/bosdb

[root@mgrdb mha]# chown -R mha:mysql /mysql/mha/mhawork

# DB tmp

# mha

mkdir -p /mysql/mha/mhawork/log mkdir -p /mysql/mha/mhawork/tmp mkdir -p /mysql/mha/mhawork/script

MHA 설정

MHA 매니저 서버 설정

MHA 설정 파일을 구성한다.

MHA 매니저 서버에 위치해야 한다.

글로벌(Global) 설정 파일

HA 서버군을 여러대 운영할 경우 공통적인 속성을 하나의 파일에 설정해서 공유할 수 있다.

/etc/masterha_default.cnf 파일로 생성한다.

(20)

[server default]

user=root password=mysql ssh_user=mha

# master_ip_failover_script= /script/masterha/master_ip_failover

# shutdown_script= /script/masterha/power_manager

# report_script= /script/masterha/send_report

# master_ip_online_change_script= /script/masterha /master_ip_online_change

Manager 설정파일

manager 실행시 로딩하는 설정파일이다.

(21)

[mha@data1 conf]$ pwd /mysql/mha/mhawork/conf

[mha@data1 conf]$ cat > bosdb.cnf [server default]

user=root password=mysql ssh_user=mha ssh_port=22

manager_workdir=/mysql/mha/mhawork/bosdb

manager_log=/mysql/mha/mhawork/bosdb/manager.log master_binlog_dir= /home/bos/mysql/data

remote_workdir=/mysql/mha/mhawork/bosdb

secondary_check_script = /mysql/mha/bin/masterha_secondary_check -s 192.168.56.102 -s 192.168.56.103

ping_interval=3

# master_ip_failover_script= /script/masterha/master_ip_failover

# shutdown_script= /script/masterha/power_manager

# report_script= /script/masterha/send_report

# master_ip_online_change_script= /script/masterha /master_ip_online_change

[server1]

hostname=192.168.56.101 candidate_master=1

[server2]

hostname=192.168.56.102 candidate_master=1

[server3]

hostname=192.168.56.103 no_master=1

#[server4]

#hostname=host4

#no_master=1

.bashrc 환경설정

MHA manager 구동시 필요한 library를 참조할 수 있도록 perl lib 경로 환경변수를 추가한다.

mha user 계정의 .bashrc에 추가한다.

모든 DB 노드에도 동일하게 설정한다.

export PERL5LIB="/root/perl5/lib/perl5:$PERL5LIB:/mysql/mha/lib/perl5"

export PATH="$PATH:/mysql/mha/bin"

(22)

바이너리 로그 설정

manager 서버에서 DB 노드의 binary log 에 접근할 수 있도록 권한 설정이 필요하다.

모든 DB 노드에서 작업한다.

권한 설정

/home/bos/mysql/data 에 binlog 가 생성될 경우 상위디렉토리까지 permission 이 있어야 ssh 를 통해서 manager가 디렉토리 접근 이 가능하다.

/home/bos 전체의 권한을 mha 유저가 속한 그룹에도 부여한다.

# data1

[root@data1 home]# chmod 770 /home/bos

#data2

[root@data2 mha]# chmod 770 /home/bos

#data3

[root@data3 mha]# chmod 770 /home/bos

권한 설정 동작 확인

manager 서버에서 아래 동작을 확인한다.

[mha@mgrdb conf]$ ssh mha@192.168.56.101 ls -al /home/bos/mysql/data

VIP 설정

master 서버에 client 가 접속하는 VIP 를 설정한다.

(23)

[root@data1 home]# ifconfig enp0s8

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.56.101 netmask 255.255.255.0 broadcast 192.168.56.255

inet6 fe80::6334:33fd:fd06:f9f8 prefixlen 64 scopeid 0x20<link>

ether 08:00:27:ef:6b:1e txqueuelen 1000 (Ethernet) RX packets 35478 bytes 3421104 (3.2 MiB)

RX errors 0 dropped 0 overruns 0 frame 0 TX packets 29577 bytes 2830228 (2.6 MiB)

[root@data1 home]# ifconfig enp0s8:0 192.168.56.10 netmask 255.255.255.0 broadcast 192.168.56.255 up

[root@data1 home]# ifconfig enp0s8:0

enp0s8:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.56.10 netmask 255.255.255.0 broadcast 192.168.56.255

ether 08:00:27:ef:6b:1e txqueuelen 1000 (Ethernet)

#down

[root@data1 home]# ifconfig enp0s8:0 192.168.56.10 netmask 255.255.255.0 broadcast 192.168.56.255 down

manager 서버에서 ping 테스트를 한다.

[mha@mgrdb conf]$ ping 192.168.56.10

PING 192.168.56.10 (192.168.56.10) 56(84) bytes of data.

64 bytes from 192.168.56.10: icmp_seq=1 ttl=64 time=0.553 ms 64 bytes from 192.168.56.10: icmp_seq=2 ttl=64 time=0.284 ms

MHA 제어 및 모니터링

MHA 명령어

manager 서버의 install 경로 의 bin 아래에 있다.

MHA를 운영하는 데 사용된다

MHA manager를 실행하고 모니터링하고 페일오버를 제어한다.

주요 명령어 script 설명 및 역할

스크립트 설명

masterha_manager MHA manager를 구동하는 명령으로, 마스터 서버의 상태가 비정상일 때 자동 페일오버를 시도한다.

masterha_check_ssh manager, db 서버간의 ssh 접속 모니터링.

masterha_check_repl 모든 db 서버간의 replication 모니터링.

masterha_check_status MHA manager 프로세스가 정상적으로 동작하는지 모니터링 한다.

(24)

masterha_stop MHA manager 프로세스를 중단시킨다.

masterha_conf_host MHA 설정 파일에 호스트를 추가하거나 삭제할 때 사용한다.

masterha_master_switch 수동으로 페일오버를 수행할 때 사용하는 스크립트이다.

bin 경로 아래 실행파일 목록

[root@mgrdb bin]# ls -1 apply_diff_relay_logs filter_mysqlbinlog masterha_check_repl masterha_check_ssh masterha_check_status masterha_conf_host masterha_manager

masterha_master_monitor masterha_master_switch masterha_secondary_check masterha_stop

purge_relay_logs save_binary_logs

masterha_manager

mha user 계정으로 구동한다.

nohup 또는 demontools 등으로 백그라운드로 실행한다.

구동하기 전에 master/slave db가 정상 구동되어 있고 이중화로 연결되어 있어야 한다.

masterha_manager --conf=/mysql/mha/mhawork/conf/bosdb.cnf

Wed Dec 1 12:49:10 2021 - [warning] Global configuration file /etc /masterha_default.cnf not found. Skipping.

Wed Dec 1 12:49:10 2021 - [info] Reading application default configuration from /mysql/mha/mhawork/conf/bosdb.cnf..

Wed Dec 1 12:49:10 2021 - [info] Reading server configuration from /mysql /mha/mhawork/conf/bosdb.cnf.

masterha_check_status

manager 프로세스가 마스터 DB를 정상적으로 모니터링하고 있는지 확인한다.

masterha_manager 가 백그라운드에서 실행하고 있어야 정상으로 표시된다.

masterha_check_status --conf=/mysql/mha/mhawork/conf/bosdb.cnf

bosdb (pid:21872) is running(0:PING_OK), master:192.168.56.101

(25)

masterha_check_ssh

설정파일(bosdb.cnf) 에 지정돼 있는 모든 서버를 SSH 로 접근 가능한지 확인한다.

masterha_check_ssh --conf=/mysql/mha/mhawork/conf/bosdb.cnf

Wed Dec 1 14:49:59 2021 - [info] Reading server configuration from /mysql /mha/mhawork/conf/bosdb.cnf..

Wed Dec 1 14:49:59 2021 - [info] Starting SSH connection tests..

Wed Dec 1 14:50:02 2021 - [debug]

Wed Dec 1 14:50:00 2021 - [debug] Connecting via SSH from mha@192.

168.56.103(192.168.56.103:22) to mha@192.168.56.101(192.168.56.101:22)..

Wed Dec 1 14:50:01 2021 - [debug] ok.

168.56.103(192.168.56.103:22) to mha@192.168.56.102(192.168.56.102:22)..

Wed Dec 1 14:50:02 2021 - [debug] ok.

Wed Dec 1 14:50:02 2021 - [debug]

168.56.102(192.168.56.102:22) to mha@192.168.56.101(192.168.56.101:22)..

Wed Dec 1 14:50:01 2021 - [debug] ok.

168.56.102(192.168.56.102:22) to mha@192.168.56.103(192.168.56.103:22)..

Wed Dec 1 14:50:01 2021 - [debug] ok.

Wed Dec 1 14:50:02 2021 - [debug]

168.56.101(192.168.56.101:22) to mha@192.168.56.102(192.168.56.102:22)..

Wed Dec 1 14:50:00 2021 - [debug] ok.

168.56.101(192.168.56.101:22) to mha@192.168.56.103(192.168.56.103:22)..

Wed Dec 1 14:50:01 2021 - [debug] ok.

Wed Dec 1 14:50:02 2021 - [info] All SSH connection tests passed successfully.

masterha_check_repl

마스터 DB와 Slave DB 간 이중화가 정상 상태인지 확인하는 스크립트이다.

masterha_check_repl --conf=/mysql/mha/mhawork/conf/bosdb.cnf

Wed Dec 1 14:52:41 2021 - [info] Reading server configuration from /mysql /mha/mhawork/conf/bosdb.cnf..

Wed Dec 1 14:52:41 2021 - [info] MHA::MasterMonitor version 0.57.

(26)

Wed Dec 1 14:52:43 2021 - [info] GTID failover mode = 1 Wed Dec 1 14:52:43 2021 - [info] Dead Servers:

Wed Dec 1 14:52:43 2021 - [info] Alive Servers:

Wed Dec 1 14:52:43 2021 - [info] 192.168.56.101(192.168.56.101:3306) Wed Dec 1 14:52:43 2021 - [info] 192.168.56.102(192.168.56.102:3306) Wed Dec 1 14:52:43 2021 - [info] 192.168.56.103(192.168.56.103:3306) Wed Dec 1 14:52:43 2021 - [info] Alive Slaves:

Wed Dec 1 14:52:43 2021 - [info] 192.168.56.102(192.168.56.102:3306) Version=8.0.26-16 (oldest major version between slaves) log-bin:enabled Wed Dec 1 14:52:43 2021 - [info] GTID ON

Wed Dec 1 14:52:43 2021 - [info] Replicating from 192.168.56.101 (192.168.56.101:3306)

Wed Dec 1 14:52:43 2021 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Dec 1 14:52:43 2021 - [info] 192.168.56.103(192.168.56.103:3306) Version=8.0.26-16 (oldest major version between slaves) log-bin:enabled Wed Dec 1 14:52:43 2021 - [info] GTID ON

Wed Dec 1 14:52:43 2021 - [info] Replicating from 192.168.56.101 (192.168.56.101:3306)

Wed Dec 1 14:52:43 2021 - [info] Not candidate for the new Master (no_master is set)

Wed Dec 1 14:52:43 2021 - [info] Current Alive Master: 192.168.56.101 (192.168.56.101:3306)

Wed Dec 1 14:52:43 2021 - [info] Checking slave configurations..

Wed Dec 1 14:52:43 2021 - [info] read_only=1 is not set on slave 192.168.56.102(192.168.56.102:3306).

Wed Dec 1 14:52:43 2021 - [info] read_only=1 is not set on slave 192.168.56.103(192.168.56.103:3306).

Wed Dec 1 14:52:43 2021 - [info] Checking replication filtering settings..

Wed Dec 1 14:52:43 2021 - [info] binlog_do_db= , binlog_ignore_db=

Wed Dec 1 14:52:43 2021 - [info] Replication filtering check ok.

Wed Dec 1 14:52:43 2021 - [info] GTID (with auto-pos) is supported.

Skipping all SSH and Node package checking.

Wed Dec 1 14:52:43 2021 - [info] Checking SSH publickey authentication settings on the current master..

Wed Dec 1 14:52:43 2021 - [info] HealthCheck: SSH to 192.168.56.101 is reachable.

Wed Dec 1 14:52:43 2021 - [info]

192.168.56.101(192.168.56.101:3306) (current master) +--192.168.56.102(192.168.56.102:3306)

+--192.168.56.103(192.168.56.103:3306)

Wed Dec 1 14:52:43 2021 - [info] Checking replication health on 192.168.56.102..

Wed Dec 1 14:52:43 2021 - [info] ok.

Wed Dec 1 14:52:43 2021 - [info] Checking replication health on 192.168.56.103..

Wed Dec 1 14:52:43 2021 - [info] ok.

Wed Dec 1 14:52:43 2021 - [warning] master_ip_failover_script is not defined.

Wed Dec 1 14:52:43 2021 - [warning] shutdown_script is not defined.

Wed Dec 1 14:52:43 2021 - [info] Got exit code 0 (Not master dead).

(27)

masterha_stop

manager script를 중단할때 사용한다

masterha_stop --conf=/mysql/mha/mhawork/conf/bosdb.cnf Stopped bosdb successfully.

tail -f manager.log

Wed Dec 1 14:56:14 2021 - [info] Got terminate signal. Exit.

masterha_conf_host

설정정보에 새로운 호스트 정보를 추가하거나 삭제할 때 사용한다.

masterha_conf_host --command=add --conf=/mysql/mha/mhawork/conf/bosdb.cnf --hostname=192.168.56.105

Wrote server_192.168.56.105 entry to /mysql/mha/mhawork/conf/bosdb.cnf .

[mha@mgrdb bin]$ tail -10 /mysql/mha/mhawork/conf/bosdb.cnf [server2]

candidate_master=1 hostname=192.168.56.102

[server3]

hostname=192.168.56.103 no_master=1

[server_192.168.56.105]

hostname=192.168.56.105.

masterha_master_switch

작업자가 의도적으로 페일오버를 할 수 있도록 제공되는 스크립트이다.

백그라운드로 실행중인 masterha_manager 스크립트를 중단시킨 뒤에 스위치 명령을 실행할 수 있다.

maserha_master_switch 옵션

옵션 설명

--master-state=(dead|alive)

필수

dead 와 alive 중 하나 선택

장애 발생으로 master 에 접근 불가능한 경우라면 dead를 선택한 다.

--dead_master_host=(hostname)

--master_state=dead 인 경우 필수

비정상 상태인 마스터 DB의 서버 호스트명을 입력한다.

--dead_master_ip 와 --dead_master_port를 추가로 설정가능

(28)

--new_master_host=(hostname)

새로운 마스터 서버의 호스트명 입력

입력하지 않으면 MHA가 규칙에 따라서 새로운 마스터 DB를 선택 한다.

--interactive=(0|1)

대화식 페일오버=1 비대화식 페일오버=0

--ssh_reacheable=(0|1|2)

마스터 DB 서버에 ssh 로 접속할 수 있는지 여부를 명시한다.

0:접근불가, 1:접근가능, 2:알수없음(default) 2인 경우 내부적으 로 ssh 접근 가능여부 체크

ssh 접근 가능하면 장애가 발생한 마스터 DB에서 바이너리 로그 를 복사한다.

--skip_change_master

옵션 사용시 릴레이로그 sync가 끝난 후 change master, slave start 구문을 실행하지 않는다.

slave 점검후 직접 이중화 연결을 할 경우 사용할 수 있다.

--skip_disable_read_only

새로운 마스터에서 "set global read_only=0" 을 실행하지 않는다.

새로운 마스터가 수동으로 변경할때가지 read_only 모드로 남아 있는다.

--last_failover_minute=(minute)

직전에 발생한 페일오버가 최근에 발생한 것이라면(기본8시간) 다 시 발생한 장애로 페일오버를 진행하지 않는다.

이전 문제로 인한 페일오버가 완료되지 않는 것으로 간주한다

--ignore_last_failover

직전에 발생한 페이오버 작업이 실패했다면 MHA는 다시 페일오버 를 시도하지 않는다.(manager_workdir)/(app_name) 경로 아래 *.

failover.error 존재를 가지고 판단한다.

이 옵션을 사용하면 마지막 페일오버 상태와 상관없이 페일오버 를 수행한다.

--wait_on_failover_error=(초)

페일오버를 수행하는 동안 에러가 발생하면

wait_on_failover_error 로 설정된 시간만큼 대기하다가 종료한다.

기본갓 0초

모니터링을 다시 시작하기전 이 설정값만큼 대기하므로 유용하다.

--remove_dead_master_conf

페일오버가 완료된 경우 MHA 매니저가 자동으로 설정파일에서 Master DB정보를 삭제한다.

마스터 정보가 남아 있으면 master 변경후 MHA매니저가 재시작 시 에러가 발생한다.

페일오버와 모니터링을 자동으로 시작되게 할때 사용한다.

--wait_until_gtid_in_sync=(0|1)

gtid 기반으로 failover가 발생할 때 MHA가 slave DB의 새로운 마 스터 DB의 gtid 까지 적용할지 여부를 설정한다.

default 1이다.

--ignore_binlog_server_error

페일오버를 진행동안 바이너리 로그 서버로부터 받은 에러를 모 두 무시한다.

masterha_master_switch 사용예

실제로 실행하기 전에는 나중에 설명할 master_ip_online_change script 설정이후에 정상동작 가능하다.

(29)

# DB

masterha_master_switch --master_state=dead --conf=/mysql/mha/mhawork/conf /bosdb.cnf --dead_master_host=192.168.56.102 --new_master_host=192.

168.56.103

# DB Online DB

masterha_master_switch --master_state=alive --conf=/mysql/mha/mhawork/conf /bosdb.cnf

마스터 DB가 online일때 사용가능 masterha_master_switch 옵션

옵션 설명

--new_master_host

새로운 마스터DB 서버의 호스트명

--org_master_is_new_slave

마스터 스위치 작업이 정상 완료되면 OLD 마스터를 새로운 마스 터 DB의 slave 로 사용한다.

default는 disable

--running_updates_limit=(초)

현재 마스터 DB에 실행중인 쿼리나 Slave DB의

seconds_behind_master 값이 running_updates_limit 파라미터 값 이상으로 크다면 마스터 스위치 작업이 중단된다

default 1(초)

--remove_org_master_conf

스위치가 성공적이면 설정파일에서 장애발생 마스터 DB의 설정 정보를 모두 삭제한다.

default 삭제 안함

--skip_lock_all_tables

스위치 동안 마스터 DB에서 실행하는 FLUSH TABLES WITH READ LOCK 명령을 수행하지 않는다.

purge_relay_logs

MHA 구성 DB는 relay_log_purge 가 1인 경우

MHA에서 relay_log_purge가 0 이 아니라는 경고가 나옴

relay_log_purge = 1인 경우 SQL thread가 사용한 relay log는 DB에 의해서 삭제된다.

relay_log_purge가 0 인 경우

SQL thread에 의해서 적용된 과거의 relay 로그가 계속 남아 있어 디스크 공간 사용량이 늘어나게 됨 MHA의 복구 시나리오는 slave가 다른 slave의 relay 로그를 참조할 수 있으므로 임의로 삭제되어서는 안되나

MHA노드는 DB설정을 relay_log_purge = 0 으로 유지하면서 purge_relay_log script를 주기적으로 호출해서 relay 로그를 삭제하는 작업을 한다.

relay log 에 대한 hard link를 생성한다 ( 대량 삭제로인한 성능저하 최소화 , default 경로 /var/tmp, 경로 지정 가능) SET GLOBAL relay_log_purge = 1; FLUSH LOGS; SET GLOBAL relay_log_purge = 0; 수행해서 로그 삭제

hard link 파일 삭제

매일 오전 5시에 릴레이 로그를 삭제하도록 설정 Slave DB에서 실행되도록 한다.

(30)

crontab -l

# purge relay logs at 5am

0 5 * * */usr/bin/purge_relay_logs --user=root --password=PASSWORD -- disable_relay_log_purge >>/var/log/masterha/purge_relay_logs.log 2>&1

# shell script

cat >/etc/auto_clean_relay_log.sh<<EOF

#!/bin/sh USER=root PASSWORD=mysql PORT=3306

log_dir='/mysql/mha/mhawork/log' work_dir='/mysql/mha/mhawork/tmp'

purge='/mysql/mha/bin/purge_relay_logs' if [ ! -d $log_dir ]

then

mkdir $log_dir -p fi

$purge --user=$USER --password=$PASSWORD --workdir=$work_dir --

disable_relay_log_purge --port=$PORT >> $log_dir/purge_relay_logs.log 2>&1

master_ip_online_change로 vip 스위치오버 설정하기

master DB가 online 상태에서 역할을 변경할때 즉 masterha_master_switch 를 master DB가 온라인상태에서 실행할때 VIP 를 변경 하도록 호출되는 script 이다.

이와 유사한 master_ip_failover 는 MHA manager가 장애를 감지하고 failover 를 진행하는 동안 VIP 를 변경할때 자동으로 호출되는 script 로 호출조건에 차이가 있다.

master_ip_online_change를 편집

(31)

# MHA manager script

[mha@mgrdb script]$ mkdir -p /mysql/mha/mhawork/script

[mha@mgrdb script]$ cp /mysql/mha/mha4mysql-manager-0.57/samples/scripts /master_ip_online_change ./

vi master_ip_online_change

# LIne . --- 1)

## 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);

--- 2)

## 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();

### VIP shellscript . --- VIP shell script 250 ### VIP change

251 my @cmd = "/bin/bash /mysql/mha/mhawork/script/change_vip.sh

$new_master_ip";

252 system(@cmd);

253

254 ## Update master ip on the catalog database, etc 255 $exit_code = 0;

change_vip.sh 생성

master_ip_online_change 내부에서 호출되는 script 이다 VIP 를 변경하는 system command 로 작성하면 된다.

(32)

MASTER="192.168.56.101"

SLAVE="192.168.56.102"

LOG="/mysql/mha/mhawork/script/vip.log"

NEW_MASTER="$1"

echo `date "+%Y%m%d_%H:%M:%S"`" new master ip passed : $1" >> $LOG

if [ "$NEW_MASTER" == "$SLAVE" ] ; then

ssh mha@192.168.56.101 sudo /sbin/ifconfig enp0s8:0 down >> $LOG ssh mha@192.168.56.102 sudo /sbin/ifconfig enp0s8:0 192.168.56.10 netmask 255.255.255.0 broadcast 192.168.56.255 up >> $LOG

elif [ "$NEW_MASTER" == "$MASTER" ] ; then

ssh mha@192.168.56.102 sudo /sbin/ifconfig enp0s8:0 down >> $LOG ssh mha@192.168.56.101 sudo /sbin/ifconfig enp0s8:0 192.168.56.10 netmask 255.255.255.0 broadcast 192.168.56.255 up >> $LOG

fi

MHA manager config 파일을 수정

manager 설정파일에 스위치오버가 발생했을 때 IP를 변경하는 script를 호출하는 부분을 추가한다.

[mha@mgrdb conf]$ vi /mysql/mha/mhawork/conf/bosdb.cnf

[server default]

manager_log=/mysql/mha/mhawork/bosdb/manager.log manager_workdir=/mysql/mha/mhawork/bosdb

master_binlog_dir=/home/bos/mysql/data password=mysql

ping_interval=3

remote_workdir=/mysql/mha/mhawork/bosdb

secondary_check_script=/mysql/mha/bin/masterha_secondary_check -s 192.168.56.102 -s 192.168.56.103

ssh_port=22 ssh_user=mha user=root

master_ip_online_change_script=/mysql/mha/mhawork/script /master_ip_online_change <---

master_ip_online_change 동작테스트

masterha_master_switch 로 online failover를 진행해서 정상동작 유무를 테스트 한다.

MHA manager 프로세스를 종료한 후에 실행한다.

실행

(33)

[mha@mgrdb bin]$ masterha_master_switch --master_state=alive --conf=

/mysql/mha/mhawork/conf/bosdb.cnf --running_updates_limit=10000

실행결과

[mha@mgrdb bin]$ masterha_master_switch --master_state=alive --conf=/mysql /mha/mhawork/conf/bosdb.cnf --running_updates_limit=10000

Thu Dec 2 17:42:03 2021 - [info] MHA::MasterRotate version 0.57.

Thu Dec 2 17:42:03 2021 - [info] Starting online master switch..

Thu Dec 2 17:42:03 2021 - [info]

Thu Dec 2 17:42:03 2021 - [info] * Phase 1: Configuration Check Phase..

Thu Dec 2 17:42:03 2021 - [info]

Thu Dec 2 17:42:03 2021 - [warning] Global configuration file /etc /masterha_default.cnf not found. Skipping.

Thu Dec 2 17:42:03 2021 - [info] Reading application default configuration from /mysql/mha/mhawork/conf/bosdb.cnf..

Thu Dec 2 17:42:03 2021 - [info] Reading server configuration from /mysql /mha/mhawork/conf/bosdb.cnf..

Thu Dec 2 17:42:04 2021 - [info] GTID failover mode = 1

Thu Dec 2 17:42:04 2021 - [info] Current Alive Master: 192.168.56.101 (192.168.56.101:3306)

Thu Dec 2 17:42:04 2021 - [info] Alive Slaves:

Thu Dec 2 17:42:04 2021 - [info] 192.168.56.102(192.168.56.102:3306) Version=8.0.26-16 (oldest major version between slaves) log-bin:enabled Thu Dec 2 17:42:04 2021 - [info] GTID ON

Thu Dec 2 17:42:04 2021 - [info] Replicating from 192.168.56.101 (192.168.56.101:3306)

Thu Dec 2 17:42:04 2021 - [info] Primary candidate for the new Master (candidate_master is set)

Thu Dec 2 17:42:04 2021 - [info] Not candidate for the new Master (no_master is set)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.101(192.168.56.101:

3306)? (YES/no): Yes

Thu Dec 2 17:42:08 2021 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..

Thu Dec 2 17:42:08 2021 - [info] ok.

Thu Dec 2 17:42:08 2021 - [info] Checking MHA is not monitoring or doing failover..

Thu Dec 2 17:42:08 2021 - [info] Checking replication health on 192.168.56.102..

(34)

Thu Dec 2 17:42:08 2021 - [info] ok.

Thu Dec 2 17:42:08 2021 - [info] Checking replication health on 192.168.56.103..

Thu Dec 2 17:42:08 2021 - [info] ok.

Thu Dec 2 17:42:08 2021 - [info] Searching new master from slaves..

Thu Dec 2 17:42:08 2021 - [info] Candidate masters from the configuration file:

Thu Dec 2 17:42:08 2021 - [info] 192.168.56.101(192.168.56.101:3306) Version=8.0.26-16 log-bin:enabled

Thu Dec 2 17:42:08 2021 - [info] GTID ON

Thu Dec 2 17:42:08 2021 - [info] Non-candidate masters:

Thu Dec 2 17:42:08 2021 - [info] Searching from candidate_master slaves which have received the latest relay log events..

Thu Dec 2 17:42:08 2021 - [info]

From:

192.168.56.101(192.168.56.101:3306) (current master) +--192.168.56.102(192.168.56.102:3306)

+--192.168.56.103(192.168.56.103:3306)

To:

192.168.56.102(192.168.56.102:3306) (new master) +--192.168.56.103(192.168.56.103:3306)

Starting master switch from 192.168.56.101(192.168.56.101:3306) to 192.168.56.102(192.168.56.102:3306)? (yes/NO): yes

Thu Dec 2 17:42:10 2021 - [info] Checking whether 192.168.56.102 (192.168.56.102:3306) is ok for the new master..

Thu Dec 2 17:42:10 2021 - [info] ok.

Thu Dec 2 17:42:10 2021 - [info] ** Phase 1: Configuration Check Phase completed.

Thu Dec 2 17:42:10 2021 - [info]

Thu Dec 2 17:42:10 2021 - [info] * Phase 2: Rejecting updates Phase..

Thu Dec 2 17:42:10 2021 - [info]

Thu Dec 2 17:42:10 2021 - [info] Executing master ip online change script to disable write on the current master:

Thu Dec 2 17:42:10 2021 - [info] /mysql/mha/mhawork/script

/master_ip_online_change --command=stop --orig_master_host=192.168.56.101 --orig_master_ip=192.168.56.101 --orig_master_port=3306 --

orig_master_user='root' --new_master_host=192.168.56.102 --

(35)

new_master_ip=192.168.56.102 --new_master_port=3306 -- new_master_user='root' --orig_master_ssh_user=mha -- new_master_ssh_user=mha --orig_master_password=xxx -- new_master_password=xxx

Thu Dec 2 17:42:10 2021 161401 Set read_only on the new master.. ok.

Thu Dec 2 17:42:10 2021 165031 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds)

{'Time' => '42','db' => undef,'Id' => '170','User' => 'repl','State' =>

'Source has sent all binlog to replica; waiting for more updates','Time_ms' => '41316','Command' => 'Binlog Dump

GTID','Rows_examined' => '0','Info' => undef,'Rows_sent' => '0','Host' =>

'192.168.56.102:47466'}

'192.168.56.103:43924'}

'192.168.56.102:47466'}

'192.168.56.103:43924'}

'192.168.56.102:47466'}

'192.168.56.103:43924'}

Thu Dec 2 17:42:11 2021 672507 Set read_only=1 on the orig master.. ok.

Thu Dec 2 17:42:11 2021 674065 Waiting all running 2 queries are disconnected.. (max 500 milliseconds)

'192.168.56.102:47466'}

(36)

'192.168.56.103:43924'}

Thu Dec 2 17:42:12 2021 176384 Killing all application threads..

Thu Dec 2 17:42:12 2021 180085 done.

Thu Dec 2 17:42:12 2021 - [info] ok.

Thu Dec 2 17:42:12 2021 - [info] Locking all tables on the orig master to reject updates from everybody (including root):

Thu Dec 2 17:42:12 2021 - [info] Executing FLUSH TABLES WITH READ LOCK..

Thu Dec 2 17:42:12 2021 - [info] ok.

Thu Dec 2 17:42:12 2021 - [info] Orig master binlog:pos is binlog.000004:

7628.

Thu Dec 2 17:42:12 2021 - [info] Waiting to execute all relay logs on 192.168.56.102(192.168.56.102:3306)..

Thu Dec 2 17:42:12 2021 - [info] master_pos_wait(binlog.000004:7628) completed on 192.168.56.102(192.168.56.102:3306). Executed 0 events.

Thu Dec 2 17:42:12 2021 - [info] done.

Thu Dec 2 17:42:12 2021 - [info] Getting new master's binlog name and position..

Thu Dec 2 17:42:12 2021 - [info] binlog.000003:9360

Thu Dec 2 17:42:12 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO

MASTER_HOST='192.168.56.102', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

Thu Dec 2 17:42:12 2021 - [info] Executing master ip online change script to allow write on the new master:

Thu Dec 2 17:42:12 2021 - [info] /mysql/mha/mhawork/script

/master_ip_online_change --command=start --orig_master_host=192.168.56.101 --orig_master_ip=192.168.56.101 --orig_master_port=3306 --

orig_master_user='root' --new_master_host=192.168.56.102 -- new_master_ip=192.168.56.102 --new_master_port=3306 -- new_master_user='root' --orig_master_ssh_user=mha -- new_master_ssh_user=mha --orig_master_password=xxx -- new_master_password=xxx

Thu Dec 2 17:42:12 2021 311913 Set read_only=0 on the new master.

Thu Dec 2 17:42:12 2021 - [info] ok.

Thu Dec 2 17:42:12 2021 - [info]

Thu Dec 2 17:42:12 2021 - [info] * Switching slaves in parallel..

Thu Dec 2 17:42:12 2021 - [info]

Thu Dec 2 17:42:12 2021 - [info] -- Slave switch on host 192.168.56.103 (192.168.56.103:3306) started, pid: 14849

Thu Dec 2 17:42:12 2021 - [info]

Thu Dec 2 17:42:13 2021 - [info] Log messages from 192.168.56.103 ...

Thu Dec 2 17:42:13 2021 - [info]

Thu Dec 2 17:42:12 2021 - [info] Waiting to execute all relay logs on 192.168.56.103(192.168.56.103:3306)..

Thu Dec 2 17:42:12 2021 - [info] master_pos_wait(binlog.000004:7628) completed on 192.168.56.103(192.168.56.103:3306). Executed 0 events.

Thu Dec 2 17:42:12 2021 - [info] done.

Thu Dec 2 17:42:12 2021 - [info] Resetting slave 192.168.56.103 (192.168.56.103:3306) and starting replication from the new master 192.168.56.102(192.168.56.102:3306)..

Thu Dec 2 17:42:13 2021 - [info] Executed CHANGE MASTER.

Thu Dec 2 17:42:13 2021 - [info] Slave started.

Thu Dec 2 17:42:13 2021 - [info] End of log messages from 192.168.56.103

(37)

...

Thu Dec 2 17:42:13 2021 - [info]

Thu Dec 2 17:42:13 2021 - [info] -- Slave switch on host 192.168.56.103 (192.168.56.103:3306) succeeded.

Thu Dec 2 17:42:13 2021 - [info] Unlocking all tables on the orig master:

Thu Dec 2 17:42:13 2021 - [info] Executing UNLOCK TABLES..

Thu Dec 2 17:42:13 2021 - [info] ok.

Thu Dec 2 17:42:13 2021 - [info] All new slave servers switched successfully.

Thu Dec 2 17:42:13 2021 - [info]

Thu Dec 2 17:42:13 2021 - [info] * Phase 5: New master cleanup phase..

Thu Dec 2 17:42:13 2021 - [info]

Thu Dec 2 17:42:13 2021 - [info] 192.168.56.102: Resetting slave info succeeded.

Thu Dec 2 17:42:13 2021 - [info] Switching master to 192.168.56.102 (192.168.56.102:3306) completed successfully.

확인사항

slave db 에서 show slave status 로 이중화 연결 확인

신규 master 서버에서 show variables like 'read_only' 로 read_only=off 여부 확인

master_ip_failover 스크립트

master_ip_failover 는 MHA manager 에 의해서 자동 failover가 발생하면 호출된다.

master_ip_online_change 와 유사하게 이 스크립트 파일을 이용해서 VIP 도 함께 페일오버 하도록 한다.

master_ip_failover 수정

(38)

# copy

[mha@mgrdb script]$ pwd /mysql/mha/mhawork/script

[mha@mgrdb script]$ cp /mysql/mha/mha4mysql-manager-0.57/samples/scripts /master_ip_failover ./

[mha@mgrdb script]$ vi master_ip_failover

--- : .

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;

--- .: vip failover . 95 ###VIP Change

96 my @cmd = "/bin/bash /mysql/mha/mhawork/script/change_vip.sh

$new_master_ip";

97 system(@cmd);

설정파일변경

MHA manager config 파일에 master_ip_failover_script 의 파일 위치를 추가한다

[mha@mgrdb conf]$ vi bosdb.cnf

master_ip_failover_script=/mysql/mha/mhawork/script/master_ip_failover

<---

MHA 테스트

테스트용 스크립트

MHA manager 제어용 편의 script

manager 를 start, stop, status check 및 수동 페일오버할 수 있는 편의 script

(39)

CNF=/mysql/mha/mhawork/conf/bosdb.cnf

CNF_switched=/mysql/mha/mhawork/conf/bosdb.cnf.switched

if [ $# -lt 1 ] ; then echo "Usage : $0 action=

fi

if [ "$1" == "start" ] ; then

nohup masterha_manager --conf=$CNF 2>&1 &

elif [ "$1" == "stop" ] ; then masterha_stop --conf=$CNF elif [ "$1" == "switch" ] ; then

masterha_master_switch --master_state=alive --conf=$CNF -- running_updates_limit=10000

elif [ "$1" == "switchdead" ] ; then

masterha_master_switch --master_state=dead --conf=$CNF -- dead_master_host=192.168.56.101

elif [ "$1" == "status" ] ; then masterha_check_status --conf=$CNF elif [ "$1" == "checkrep" ] ; then masterha_check_repl --conf=$CNF

elif [ "$1" == "checkswitchrep" ] ; then masterha_check_repl --conf=$CNF_switched fi

master 서버 초기화용 script

failover를 반복 테스트하려면 이중화 구성 / VIP 설정/ read_only설정을 초기 마스터 구성 상태로 원복해야 한다.

아래 스크립트를 사용해서 쉽게 변경한다.

[mha@mgrdb script]$ cat resetmaster.sh USER=root

PASSWD=mysql

## DB readonly function a() {

HOST="192.168.56.101"

CMD="mysql -u$USER -p$PASSWD -h $HOST "

$CMD <<EOF

set global read_only=OFF;

EOF

}

(40)

## Slave 1 function b()

{

HOST="192.168.56.102"

$CMD <<EOF stop slave;

reset slave all;

CHANGE MASTER TO

start slave;

EOF }

## slave 2 function c() {

HOST="192.168.56.103"

$CMD <<EOF stop slave;

reset slave all;

CHANGE MASTER TO

start slave;

EOF }

a b c

### VIP

ssh mha@192.168.56.102 sudo /sbin/ifconfig enp0s8:0 down

ssh mha@192.168.56.101 sudo /sbin/ifconfig enp0s8:0 192.168.56.10 netmask 255.255.255.0 broadcast 192.168.56.255 up

자동 페일오버 테스트

MHA 매니저 구동

(41)

[mha@mgrdb bin]$ sh manager.sh start

[mha@mgrdb bin]$ nohup: appending output to '/home/mha/nohup.out'

[mha@mgrdb bin]$ sh manager.sh status

bosdb (pid:15361) is running(0:PING_OK), master:192.168.56.101

마스터 DB stop

[bos@data1 mysql]$ sh mysql.sh stop

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

MHA 매니저 로그

Thu Dec 2 18:21:55 2021 - [warning] Got error on MySQL select ping: 1053 (Server shutdown in progress)

Thu Dec 2 18:21:55 2021 - [info] Executing SSH check script: exit 0 Thu Dec 2 18:21:55 2021 - [info] Executing secondary network check script: /mysql/mha/bin/masterha_secondary_check -s 192.168.56.102 -s 192.168.56.103 --user=mha --master_host=192.168.56.101 --master_ip=192.

168.56.101 --master_port=3306 --master_user=root --master_password=mysql --ping_type=SELECT

Thu Dec 2 18:21:55 2021 - [info] HealthCheck: SSH to 192.168.56.101 is reachable.

Monitoring server 192.168.56.102 is reachable, Master is not reachable from 192.168.56.102. OK.

Monitoring server 192.168.56.103 is reachable, Master is not reachable from 192.168.56.103. OK.

Thu Dec 2 18:21:56 2021 - [info] Master is not reachable from all other monitoring servers. Failover should start.

Thu Dec 2 18:21:58 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.101' (111))

Thu Dec 2 18:21:58 2021 - [warning] Connection failed 2 time(s)..

Thu Dec 2 18:22:04 2021 - [warning] Master is not reachable from health checker!

Thu Dec 2 18:22:04 2021 - [warning] Master 192.168.56.101(192.168.56.101:

3306) is not reachable!

Thu Dec 2 18:22:04 2021 - [warning] SSH is reachable.

Thu Dec 2 18:22:04 2021 - [info] Connecting to a master server failed.

(42)

Reading configuration file /etc/masterha_default.cnf and /mysql/mha/mhawork /conf/bosdb.cnf again, and trying to connect to all servers to check

server status..

Thu Dec 2 18:22:04 2021 - [warning] Global configuration file /etc /masterha_default.cnf not found. Skipping.

Thu Dec 2 18:22:04 2021 - [info] Reading application default configuration from /mysql/mha/mhawork/conf/bosdb.cnf..

Thu Dec 2 18:22:04 2021 - [info] Reading server configuration from /mysql /mha/mhawork/conf/bosdb.cnf..

Thu Dec 2 18:22:05 2021 - [info] GTID failover mode = 1 Thu Dec 2 18:22:05 2021 - [info] Dead Servers:

Thu Dec 2 18:22:05 2021 - [info] 192.168.56.101(192.168.56.101:3306) Thu Dec 2 18:22:05 2021 - [info] Alive Servers:

Thu Dec 2 18:22:05 2021 - [info] 192.168.56.102(192.168.56.102:3306) Thu Dec 2 18:22:05 2021 - [info] 192.168.56.103(192.168.56.103:3306) Thu Dec 2 18:22:05 2021 - [info] Alive Slaves:

Thu Dec 2 18:22:05 2021 - [info] Checking slave configurations..

Thu Dec 2 18:22:05 2021 - [info] read_only=1 is not set on slave 192.168.56.102(192.168.56.102:3306).

Thu Dec 2 18:22:05 2021 - [info] Checking replication filtering settings..

Thu Dec 2 18:22:05 2021 - [info] Replication filtering check ok.

Thu Dec 2 18:22:05 2021 - [info] Master is down!

Thu Dec 2 18:22:05 2021 - [info] Terminating monitoring script.

Thu Dec 2 18:22:05 2021 - [info] Got exit code 20 (Master dead).

Thu Dec 2 18:22:05 2021 - [info] MHA::MasterFailover version 0.57.

Thu Dec 2 18:22:05 2021 - [info] Starting master failover.

Thu Dec 2 18:22:05 2021 - [info]

Thu Dec 2 18:22:05 2021 - [info] * Phase 1: Configuration Check Phase..

Thu Dec 2 18:22:05 2021 - [info]

Thu Dec 2 18:22:06 2021 - [info] GTID failover mode = 1 Thu Dec 2 18:22:06 2021 - [info] Dead Servers:

Thu Dec 2 18:22:06 2021 - [info] 192.168.56.101(192.168.56.101:3306) Thu Dec 2 18:22:06 2021 - [info] Checking master reachability via MySQL (double check)...

Thu Dec 2 18:22:06 2021 - [info] ok.

Thu Dec 2 18:22:06 2021 - [info] Alive Servers:

Thu Dec 2 18:22:06 2021 - [info] 192.168.56.102(192.168.56.102:3306) Thu Dec 2 18:22:06 2021 - [info] 192.168.56.103(192.168.56.103:3306) Thu Dec 2 18:22:06 2021 - [info] Alive Slaves: