DB Library

MHA 특징

• 한번의 failover 수행만 가능 ( mha daemon 은 한번의 failover 만 수행 가능 / failover 후 재구성 필요 )
• candidate master 중 1번째로 지정된 slave 가 master 로 전환되며 기존 master 에 붙어있던 slave 는 new slave 로 전환
• MHA로 failover가 되면, 모든 slave Node에 down되기 직전의 master Node 데이터 변경 내용을 모두 적용 함.
• 모든 Node에 최신 데이터 변경 내용을 적용 후 시작 되기 때문에, master - slave 간 데이터 동기화 지연이 클수록 복구 시간이 오래 걸림.
• vip 를 이동하는 형태로 failover 되므로 서버에서 새로운 마스터 장비의 ip 변경이 불필요함 ( old master 장비에 셋팅되어 있던 VIP를 new master 장비에 셋팅하는 방식 .. Application은 서버 정상 일때나 failover가 된 이후 모두 VIP로 DB에 접속함 )
• MHA를 통해 DB가 스위칭 될때 일시적으로 기존 연결이 끊어지므로, application 서버는 connection 재구성 예외 처리 로직이 필요

MHA 구성 조건

• MySQL 서버 최소 2대 이상 필요
• MySQL Master - Slave Replication 구성 필요
• Manager Server 1대 필요(Slave 장비에서 구성 가능)
• VIP 1개
• test 환경 master : 10.10.10.10 slave : 10.10.10.20 vip : 10.10.10.30

MySQL 설치

아래 rpm 설치 후 진행

yum install -y automake \
binutils bison \
gcc gcc-c++ \
gettext libtool \
make openssl \
openssl openssl-devel \
zlib zlib-devel \
cmake \
gcc-toolset-10-gcc gcc-toolset-10-gcc-c++ gcc-toolset-10-binutils gcc-toolset-10-annobin \
libaio libaio-devel \
perl ncurses-devel \
git redhat-rpm-config rpm-build rpm-sign \
cyrus-sasl cyrus-sasl-devel \
curl curl-devel \
libtirpc-devel \
libudev-devel

MHA 설치

rpm 다운로드 링크

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

mha 구성을 위해 필요한 rpm 설치

dnf config-manager --set-enabled powertools
yum install perl-Module-Install

yum install perl
yum install perl*

yum install perl-DBD-MySQL
yum install perl-Config-Tiny
yum install perl-Log-Dispatch
yum install perl-Parallel-ForkManager
yum install perl-Class-Load
yum install perl-Data-OptList
yum install perl-Date-Manip
yum install perl-Email-Date-Format
yum install perl-Log-Dispatch-FileRotate
yum install perl-Log-Log4perl
yum install perl-Mail-Sender
yum install perl-Mail-Sendmail
yum install perl-MailTools
yum install perl-MIME-Lite
yum install perl-MIME-Types
yum install perl-Params-Util
yum install perl-Params-Validate
yum install perl-Sub-Install
yum install perl-TimeDate
yum install perl-XML-DOM
yum install perl-XML-RegExp

install module 오류
해결 : yum install perl-Module-Install

Can't locate inc/Module/Install.pm in @INC (you may need to install the inc::Module::Install module) (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5) at Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.

perl-Log-Dispatch 오류

powertools 관련 rpm 설치

해결 : dnf config-manager --set-enabled powertools

perl-PAR-Packer-Tk conflicting 오류
yum install perl* 실행 시 아래 에러는 무시 가능

Error:
 Problem 1: conflicting requests
  - nothing provides perl(Tk::ColoredButton) needed by perl-PAR-Packer-Tk-1.052-2.el8.noarch
  - nothing provides perl(Tk::EntryCheck) needed by perl-PAR-Packer-Tk-1.052-2.el8.noarch
  - nothing provides perl(Tk::Getopt) needed by perl-PAR-Packer-Tk-1.052-2.el8.noarch
  - nothing provides perl(Tk::Pod) needed by perl-PAR-Packer-Tk-1.052-2.el8.noarch
 Problem 2: conflicting requests
  - nothing provides perl-HTML-Strip needed by perl-sword-1.8.1-18.el8.x86_64
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)

mha node 설치

모든 장비에 설치

tar -zxf mha4mysql-node-0.57.tar.gz
cd mha4mysql-node-0.57
perl Makefile.PL && make && make install

mha4mysql-node-0.57]# perl Makefile.PL && make && make install
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node
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
Installing /usr/local/share/perl5/MHA/BinlogManager.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing /usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing /usr/local/share/perl5/MHA/NodeUtil.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing /usr/local/share/perl5/MHA/SlaveUtil.pm
Installing /usr/local/share/perl5/MHA/NodeConst.pm
Installing /usr/local/share/man/man1/filter_mysqlbinlog.1
Installing /usr/local/share/man/man1/apply_diff_relay_logs.1
Installing /usr/local/share/man/man1/purge_relay_logs.1
Installing /usr/local/share/man/man1/save_binary_logs.1
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/purge_relay_logs
Installing /usr/local/bin/save_binary_logs
Appending installation info to /usr/lib64/perl5/perllocal.pod

mha manager 설치

manager 장비에서 설치

cd /home/dba_admin/mha-5.7/
tar -zxf mha4mysql-manager-0.57.tar.gz
cd mha4mysql-manager-0.57
perl Makefile.PL && make && make install

mha4mysql-manager-0.57]# perl Makefile.PL && make && make install
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[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)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager
cp lib/MHA/ManagerUtil.pm blib/lib/MHA/ManagerUtil.pm
cp lib/MHA/Config.pm blib/lib/MHA/Config.pm
cp lib/MHA/HealthCheck.pm blib/lib/MHA/HealthCheck.pm
cp lib/MHA/ServerManager.pm blib/lib/MHA/ServerManager.pm
cp lib/MHA/ManagerConst.pm blib/lib/MHA/ManagerConst.pm
cp lib/MHA/ManagerAdmin.pm blib/lib/MHA/ManagerAdmin.pm
cp lib/MHA/FileStatus.pm blib/lib/MHA/FileStatus.pm
cp lib/MHA/MasterFailover.pm blib/lib/MHA/MasterFailover.pm
cp lib/MHA/ManagerAdminWrapper.pm blib/lib/MHA/ManagerAdminWrapper.pm
cp lib/MHA/MasterMonitor.pm blib/lib/MHA/MasterMonitor.pm
cp lib/MHA/MasterRotate.pm blib/lib/MHA/MasterRotate.pm
cp lib/MHA/SSHCheck.pm blib/lib/MHA/SSHCheck.pm
cp lib/MHA/Server.pm blib/lib/MHA/Server.pm
cp lib/MHA/DBHelper.pm blib/lib/MHA/DBHelper.pm
cp bin/masterha_stop blib/script/masterha_stop
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_stop
cp bin/masterha_conf_host blib/script/masterha_conf_host
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_conf_host
cp bin/masterha_check_repl blib/script/masterha_check_repl
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_repl
cp bin/masterha_check_status blib/script/masterha_check_status
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_status
cp bin/masterha_master_monitor blib/script/masterha_master_monitor
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_monitor
cp bin/masterha_check_ssh blib/script/masterha_check_ssh
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_ssh
cp bin/masterha_master_switch blib/script/masterha_master_switch
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_switch
cp bin/masterha_secondary_check blib/script/masterha_secondary_check
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_secondary_check
cp bin/masterha_manager blib/script/masterha_manager
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_manager
Manifying blib/man1/masterha_stop.1
Manifying blib/man1/masterha_conf_host.1
Manifying blib/man1/masterha_check_repl.1
Manifying blib/man1/masterha_check_status.1
Manifying blib/man1/masterha_master_monitor.1
Manifying blib/man1/masterha_check_ssh.1
Manifying blib/man1/masterha_master_switch.1
Manifying blib/man1/masterha_secondary_check.1
Manifying blib/man1/masterha_manager.1
Installing /usr/local/share/perl5/MHA/ManagerUtil.pm
Installing /usr/local/share/perl5/MHA/Config.pm
Installing /usr/local/share/perl5/MHA/HealthCheck.pm
Installing /usr/local/share/perl5/MHA/ServerManager.pm
Installing /usr/local/share/perl5/MHA/ManagerConst.pm
Installing /usr/local/share/perl5/MHA/ManagerAdmin.pm
Installing /usr/local/share/perl5/MHA/FileStatus.pm
Installing /usr/local/share/perl5/MHA/MasterFailover.pm
Installing /usr/local/share/perl5/MHA/ManagerAdminWrapper.pm
Installing /usr/local/share/perl5/MHA/MasterMonitor.pm
Installing /usr/local/share/perl5/MHA/MasterRotate.pm
Installing /usr/local/share/perl5/MHA/SSHCheck.pm
Installing /usr/local/share/perl5/MHA/Server.pm
Installing /usr/local/share/perl5/MHA/DBHelper.pm
Installing /usr/local/share/man/man1/masterha_stop.1
Installing /usr/local/share/man/man1/masterha_conf_host.1
Installing /usr/local/share/man/man1/masterha_check_repl.1
Installing /usr/local/share/man/man1/masterha_check_status.1
Installing /usr/local/share/man/man1/masterha_master_monitor.1
Installing /usr/local/share/man/man1/masterha_check_ssh.1
Installing /usr/local/share/man/man1/masterha_master_switch.1
Installing /usr/local/share/man/man1/masterha_secondary_check.1
Installing /usr/local/share/man/man1/masterha_manager.1
Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_master_switch
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_manager
Appending installation info to /usr/lib64/perl5/perllocal.pod

MySQL 설정

MySQL 계정 생성

master 및 slave 모두 생성

GRANT ALL PRIVILEGES ON *.* TO 'mhauser'@'%' IDENTIFIED BY 'mhauser비밀번호';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'repluser비밀번호';

MySQL 설정 변경

relay-log=/log/mysqld_relay_bin
relay_log_purge=0

Mysql binary File Copy

cp /usr/local/mysql/bin/* /usr/bin/ ( ln -s /usr/local/mysql/bin/* /usr/bin/ )

MySQL Backup 및 Restore

backup
mysqldump ... --master-data=2 --user=root -p --port=3306 TestDB > TestDB.sql
restore
mysql -h 10.10.10.20 -u root -p -P 3306 < testDB.sql

MySQL Replication 구성

slave에서 실행

change master to
master_host='10.10.10.10',
master_port=3306,
master_user='repluser',
master_password='repluser비밀번호',
master_log_file='mysql-bin.000XXX', – master 백업 파일에서 참고
master_log_pos= XXX; – master 백업 파일에서 참고

start slave;

OS 설정

모든 장비에서 실행

유저 생성

adduser mhauser

passwd mhauser
usermod -G mysql mhauser

유저 기간 만료 확인 및 수정

chage -l mhauser | grep -i "^password expires"

위의 값이 never가 아닐 경우 아래 명령어로 영구화 필요

chage -E -1 -M 99999 "mhauser"

mha directory 생성

mhatest의 경우는 서비스명이나 서비스코드 형태를 사용

mkdir -p /mha/{log,conf,remote,scripts,work}
mkdir /mha/work/mhatest
mkdir /mha/remote/mhatest
chown -R mhauser.mhauser /mha
chmod -R 750 /mha

mhauser 권한 부여

~]# vi /etc/ssh/sshd_config

아래 부분 주석해제 후 저장

RSAAuthentication yes
PubkeyAuthentication yes
AuthorizedKeysFile .ssh/authorized_keys


-- visudo 명령어로 암호없이 ssh계정 사용하기위해 아래 부분 변경 
(ssh 통신 시 암호없이 통신을하기 위한 방법이며, 보안을 위해 VIP 알리아스를 만든 후 
ohnew 계정은 VIP 알리아스만 실행하고자 변경)

~]# visudo

23라인 부근의 아래 커멘트 부근에 아래 명령어 추가 
## Command Aliases
## These are groups of related commands...

Cmnd_Alias VIP = /sbin/ifconfig, /sbin/arping


57라인 부근에 이부분 주석 처리
# Defaults requiretty


105 라인 부근에 아래 부분 추가
## Allow root to run any commands anywhere
mhauser ALL=(ALL) NOPASSWD: VIP

mha user ssh 설정

su - mhauser ( 반드시 mhauser 계정으로 실행 )
ssh-keygen -t rsa ( 암호없이 생성을 위해 enter 연타 )

# source 서버에서 target 서버로 전달
ssh-copy-id mhauser@10.10.10.10
ssh-copy-id mhauser@10.10.10.20

================================================================================================================
 ~]$ ssh-copy-id mhauser@10.10.10.10
The authenticity of host '10.10.10.10 (10.10.10.10)' can't be established.
ECDSA key fingerprint is 38:58:d9:71:8a:ed:89:af:1d:c8:d1:69:e0:e1:b8:dd.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
mhauser@10.10.10.10's password:
/etc/profile.d/logging.sh: line 7: ifconfig: command not found
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'mhauser@10.10.10.10'"
and check to make sure that only the key(s) you wanted were added.
================================================================================================================

# 접속 테스트 ( 모든 장비 Node, Manager 간 비밀번호 없이 접속 가능 )
ssh mhauser@10.10.10.10
ssh mhauser@10.10.10.20

VIP 설정

master node 장비에서만 실행

-- IP 확인
~]# ifconfig
bond0: flags=5187<UP,BROADCAST,RUNNING,MASTER,MULTICAST> mtu 1500
inet 10.10.10.10 netmask 255.255.255.0 broadcast 10.10.10.255
.....

-- VIP 설정
~]# ifconfig bond0:0 10.10.10.30 up netmask 255.255.255.128 up

-- VIP 적용
~]# arping -c3 -D -I bond0 -s 10.10.10.30 -U 10.10.10.1
ARPING 10.10.10.1 from 10.10.10.10 bond0
Unicast reply from 10.162.131.10 [00:00:5E:00:01:15] 0.770ms
Sent 1 probes (1 broadcast(s))
Received 1 response(s)
[root@Nmsprofdb01 ~]#

-- VIP 확인
[root@Nmsprofdb01 ~]# ifconfig
bond0: flags=5187<UP,BROADCAST,RUNNING,MASTER,MULTICAST> mtu 1500
inet 10.10.10.10 netmask 255.255.255.0 broadcast 10.10.10.255
.....
bond0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.10.10.30 netmask 255.0.0.0 broadcast 10.255.255.255

master_ip_failover 파일 생성

manager 장비 /mha/script/master_ip_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_ssh_port,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user,
$new_master_password, $new_master_ssh_port
);
my $vip = '10.10.10.30'; # Virtual IP
my $key = "0";
my $devicenm = "bond0";
my $ssh_start_vip = "sudo /sbin/ifconfig $devicenm:$key $vip netmask 255.255.255.128 up";
my $ssh_stop_vip = "sudo /sbin/ifconfig $devicenm:$key down";
my $ssh_mac_refresh = "sudo /sbin/arping -c3 -D -I $devicenm -s 10.10.10.30 10.10.10.1";
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,
'orig_master_ssh_port=i' => \$orig_master_ssh_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,
'new_master_ssh_port=i' => \$new_master_ssh_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $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 -p $orig_master_ssh_port $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 -p $new_master_ssh_port $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
`ssh -p $new_master_ssh_port $ssh_user\@$new_master_host \" $ssh_mac_refresh\"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh -p $orig_master_ssh_port $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";
}

scripts/master_ip_failover 실행 권한 추가

chmod +x /mha/scripts/master_ip_failover

conf 파일 추가

/mha/conf/mhatest.conf 파일 생성 후 작성

[server default]
# mysql user and password
user=mhauser
password=mhauserDB비밀번호
ssh_user=mhauser
repl_user=repluser
repl_password=repluserDB비밀번호

# manager log file
manager_log=/mha/log/mhatest.log

# working directory on the manager
manager_workdir=/mha/work/mhatest/

# working directory on MySQL servers
remote_workdir=/mha/remote/mhatest/

master_binlog_dir=/log/

master_ip_failover_script=/mha/scripts/master_ip_failover --orig_master_ssh_port=22 --new_master_ssh_port=22

[server1]
hostname=10.10.10.10
port=59306
candidate_master=1

[server2]
hostname=10.10.10.20
port=59306
candidate_master=1

#no_master=1
#ignore_fail=1

relay log 삭제 스크립트 추가

relay log purge 스크립트 등록
vi /mha/scripts/relay_log_purge.sh
chmod 755 /mha/scripts/relay_log_purge.sh

#!/bin/sh

source ~/.bash_profile

USER=mhauser
PORT=$1
PASSWORD='mhauser패스워드'


# Slave Check
v_mysql=`which mysql`
SLAVE_STATUS=`$v_mysql -u root -p XXXX -P 3306 -e 'show slave status\G;' | grep Running | grep Yes | wc -l`

if [ $SLAVE_STATUS -lt 2 ]
then
echo 'The master does not execute purge...' >> /mha/log/purge_relay.log
exit;
fi

/usr/local/bin/purge_relay_logs --user=$USER --port=$PORT --password=$PASSWORD --disable_relay_log_purge >> /mha/log/purge_relay.log 2>&1

crontab 등록

crontab -e

######################################################################
#
# Relay Log Purge Daily
#
######################################################################
0 5 * * * /mha/scripts/relay_log_purge.sh 3306

perl 실행 권한 부여

chmod 755 /usr/bin/perl 

MHA 설정 Test

mhauser OS 계정으로 실행

masterha_check_ssh

ll /usr/bin/perl
su - mhauser
masterha_check_ssh --conf=/mha/conf/mhatest.conf


~]$ masterha_check_ssh --conf /mha/conf/mhatest.conf
Thu Jun 1 18:52:28 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 1 18:52:28 2017 - [info] Reading application default configurations from /mha/conf/mhatest.conf..
Thu Jun 1 18:52:28 2017 - [info] Reading server configurations from /mha/conf/mhatest.conf..
Thu Jun 1 18:52:28 2017 - [info] Starting SSH connection tests..
Thu Jun 1 18:52:28 2017 - [debug]
Thu Jun 1 18:52:28 2017 - [debug] Connecting via SSH from mhauser@10.10.10.20(10.10.10.20:22) to mhauser@10.10.10.10(10.10.10.10:22)..
Thu Jun 1 18:52:28 2017 - [debug] ok.
Thu Jun 1 18:52:29 2017 - [debug]
Thu Jun 1 18:52:28 2017 - [debug] Connecting via SSH from mhauser@10.10.10.20(10.10.10.20:22) to mhauser@10.10.10.10(10.10.10.10:22)..
Thu Jun 1 18:52:29 2017 - [debug] ok.
Thu Jun 1 18:52:29 2017 - [info] All SSH connection tests passed successfully.
===============================================================================================================

masterha_check_repl

ll /usr/bin/perl
su - mhauser
masterha_check_repl --conf=/mha/conf/mhatest.conf


~]$ masterha_check_repl --conf=/mha/conf/mhatest.conf
Mon Jun 5 14:15:35 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 5 14:15:35 2017 - [debug] MHA::MasterMonitor version 0.56.
Mon Jun 5 14:15:35 2017 - [debug] Connecting to servers..
Mon Jun 5 14:15:36 2017 - [debug] Connected to: 10.10.10.10(10.10.10.10:59306), user=mhauser
Mon Jun 5 14:15:36 2017 - [debug] Connected to: 10.10.10.20(10.10.10.20:59306), user=mhauser
Mon Jun 5 14:15:36 2017 - [debug] Comparing MySQL versions..
Mon Jun 5 14:15:36 2017 - [debug] Comparing MySQL versions done.
Mon Jun 5 14:15:36 2017 - [debug] Connecting to servers done.
.....
Checking the Status of the script.. OK
Bad port 'mhauser@10.10.10.20'
Mon Jun 5 14:15:38 2017 - [debug] OK.
Mon Jun 5 14:15:38 2017 - [warning] shutdown_script is not defined.
Mon Jun 5 14:15:38 2017 - [debug] Disconnected from 10.10.10.10(10.10.10.10:59306)
Mon Jun 5 14:15:38 2017 - [debug] Disconnected from 10.10.10.20(10.10.10.20:59306)
Mon Jun 5 14:15:38 2017 - [debug] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

Mysql 버전 확인 부분 오류 수정

~]$ masterha_check_repl --conf=/mha/conf/mhatest.conf

## 에러 내용
Fri Nov  4 11:30:59 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Nov  4 11:30:59 2022 - [info] Reading application default configuration from /mha/conf/mhatest.conf..
Fri Nov  4 11:30:59 2022 - [info] Reading server configuration from /mha/conf/mhatest.conf..
Fri Nov  4 11:30:59 2022 - [info] MHA::MasterMonitor version 0.57.
Fri Nov  4 11:31:00 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. Redundant argument in sprintf at /usr/local/share/perl5/MHA/NodeUtil.pm line 190.
Fri Nov  4 11:31:00 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Fri Nov  4 11:31:00 2022 - [info] Got exit code 1 (Not master dead).

## 수정 방법
/usr/local/share/perl5/MHA/NodeUtil.pm 파일의 190번 라인을 아래 형태로 수정

~]# vi  /usr/local/share/perl5/MHA/NodeUtil.pm

182 sub parse_mysql_version($) {
183   my $str = shift;
184   my $result = sprintf( '%03d%03d%03d', $str =~ m/(\d+)/g );
185   return $result;
186 }
187
188 sub parse_mysql_major_version($) {
189   my $str = shift;
190   my $result = sprintf( '%03d%03d%03d', $str =~ m/(\d+)/g );
191   return $result;
192 }

mha 실행

su - mhauser

nohup masterha_manager --conf=/mha/conf/mhatest.conf > /mha/log/mhatest.log 2>&1 &

현재 지원되는 SQL Server에 사용 가능한 최신 업데이트

다음 각 링크는 적용 가능한 모든 제품 및 기술에 대한 정보를 제공합니다.

Query 형태

Example

t1 테이블과 t2 테이블의 조건에 해당하는 행 모두 삭제 ( t1, t2 테이블 동시 삭제 )

  t1 id값이 1인 행 삭제

  21 id값이 1인 행 삭제

Delete 실행

결과

참고

https://www.mysqltutorial.org/mysql-delete-join/