본문 바로가기
MySQL, MariaDB

MariaDB MHA 이중화 구성

'Tis Heaven 2021. 3. 19. 20:11

MariaDB single 구성한 경험은 여러번 있었지만, 이중화 구성은 좀처럼 기회가 없었다.
하지만, 운 좋게도 구성할 기회가 생겨 이번에 정리하게 되었다.

내가 확인한 이중화 구성의 종류에는 6가지 종류가 있다.

  1. master - slave의 replication 구조
  2. MHA + replication를 활용한 이중화 구조
  3. MaxScale + replication을 활용한 이중화 구조
  4. 공유 볼륨과 클러스터 솔루션(리눅스 클러스터)을 사용한 이중화 구조
  5. Replication과 Galera Cluster를 사용한 이중화 구조
  6. block 복제 솔루션(ArcServe)를 활용한 이중화 구조

일반 replication 이중화 구성의 경우에는 구축이 쉽기도 하고
MHA와 MaxScale을 활용한 이중화 구성에 replication 구축내용이 포함되어 있으므로,
여기서는 MHA + Replication을 활용한 2node 이중화를 다뤄보려 한다.

구성도를 간단히 보자면 위와 같다.

서버는 총 3대가 필요하다.
active와 standby 노드에는 mariadb 엔진이 설치되어 있어야 하며,
manager 노드에는 MHA 소프트웨어 컴파일이 되어야 한다.

설치테스트를 한 MariaDB의 버전은 현재 시점에서 가장 stable한 10.5.8 버전을 사용하였다.

active 서버와 standby 서버에 MariaDB를 설치를 시작해보려 한다.

##1 Master
*1패키지 설치 확인
rpm -qa |grep epel-release
rpm -qa |grep net-tools
rpm -qa |grep sysstat
rpm -qa |grep wget
rpm -qa |grep lrzsz
rpm -qa |grep lsof
rpm -qa |grep htop
rpm -qa |grep iftop
rpm -qa |grep rsync
rpm -qa |grep bzip2
rpm -qa |grep unzip
rpm -qa |grep patch
rpm -qa |grep syslog

 

# rpm -qa |grep net-tools

net-tools-2.0-0.25.20131004git.el7.x86_64

 

# rpm -qa |grep sysstat

sysstat-10.1.5-19.el7.x86_64

 

# rpm -qa |grep wget

wget-1.14-18.el7_6.1.x86_64

 

# rpm -qa |grep lrzsz

lrzsz-0.12.20-36.el7.x86_64

 

# rpm -qa |grep lsof

lsof-4.87-6.el7.x86_64

 

# rpm -qa |grep htop

htop-2.2.0-3.el7.x86_64

 

# rpm -qa |grep iftop

iftop-1.0-0.14.pre4.el7.x86_64

 

# rpm -qa |grep rsync

rsync-3.1.2-10.el7.x86_64

 

# rpm -qa |grep bzip2

bzip2-libs-1.0.6-13.el7.x86_64
bzip2-1.0.6-13.el7.x86_64

 

# rpm -qa |grep unzip

unzip-6.0-21.el7.x86_64

 

# rpm -qa |grep patch

patch-2.7.1-12.el7_7.x86_64
python-jsonpatch-1.2-4.el7.noarch
perl-Log-Dispatch-2.41-1.el7.1.noarch

 

# rpm -qa |grep syslog

rsyslog-8.24.0-52.el7.x86_64

rpm -qa |grep epel
rpm -qa |grep perl-devel
rpm -qa |grep perl-CPAN
rpm -qa |grep perl-DBD-MySQL
rpm -qa |grep perl-Config-Tiny
rpm -qa |grep perl-Log-Dispatch
rpm -qa |grep perl-Parallel-ForkManager
rpm -qa |grep perl-Module-Install

 

# rpm -qa |grep perl-devel

perl-devel-5.16.3-295.el7.x86_64

 

# rpm -qa |grep perl-CPAN

perl-CPANPLUS-0.91.38-4.el7.noarch
perl-CPAN-Meta-2.120921-5.el7.noarch
perl-CPAN-1.9800-295.el7.noarch
perl-CPAN-Meta-YAML-0.008-14.el7.noarch
perl-CPAN-Meta-Requirements-2.122-7.el7.noarch

 

# rpm -qa |grep perl-DBD-MySQL

perl-DBD-MySQL-4.023-6.el7.x86_64

 

# rpm -qa |grep perl-Config-Tiny

perl-Config-Tiny-2.14-7.el7.noarch

 

# rpm -qa |grep perl-Log-Dispatch

perl-Log-Dispatch-2.41-1.el7.1.noarch

 

# rpm -qa |grep perl-Parallel-ForkManager

perl-Parallel-ForkManager-1.18-2.el7.noarch

 

# rpm -qa |grep perl-Module-Install

perl-Module-Install-1.06-4.el7.noarch

 

*2인터페이스 구성 확인(VIP)
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet x.x.x.108 netmask 255.255.255.0 broadcast x.x.x.255
inet6 fe80::f816:3eff:fe0d:2932 prefixlen 64 scopeid 0x20
ether fa:16:3e:0d:29:32 txqueuelen 1000 (Ethernet)
RX packets 5186112 bytes 1096839821 (1.0 GiB)
RX errors 0 dropped 42 overruns 0 frame 0
TX packets 71690 bytes 5768414 (5.5 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet x.x.x.127 netmask 255.255.255.0 broadcast x.x.x.255
ether fa:16:3e:0d:29:32 txqueuelen 1000 (Ethernet)

  1. hosts 파일 설정vi /etc/hosts

x.x.x.1 testdb1
x.x.x.2 testdb2
x.x.x.3 mngsever

  1. mysql 유저 생성#passwd mysql
  2. useradd mysql -g mysql
  3. groupadd mysql
  4. mariadb 디렉토리 생성basedir=/usr/mariadblog-error=/log/mariadbd.errdatadir=/DATAlog-bin=/log/binlog/binlogrelay-log=/log/relaylog/relaylog

# mkdir -p /log/binlog /log/relaylog /log/errlog /log/slowlog

 

  1. tar 압축 해제tar xvzf mariadb-10.5.8-linux-x86_64.tar.gz
  2. 바이너리 이동mv mariadb-10.5.8-linux-x86_64 /usr/mariadb
  3. 소유자 변경
  4. chown -R mysql:mysql /usr/mariadb /log /DATA
  5. my.cnf 작성

[client]
port=13308
socket=/tmp/mariadb.sock

[mariadb]
user=mysql
port=13308

basedir=/usr/mariadb
datadir=/DATA
log-error=/log/errlog/mariadb_error.log
tmpdir=/tmp
socket=/tmp/mariadb.sock

server-id=1
log_bin=/log/binlog/mariadb-bin
max_binlog_size=200M
binlog_cache_size=2M
binlog_format=MIXED

expire_logs_days=10
relay-log=/log/relay-log/mariadb-relay
relay_log_purge=0
#reay_only

slow_query_log = 1
slow_query_log_file = /log/slowlog/mariadb-slow.log
log-output=FILE,TABLE
long_query_time = 5
log_slow_rate_limit = 1
log_slow_verbosity = query_plan
log_slow_admin_statements

innodb_buffer_pool_size=16G

performance_schema=ON
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8'
skip-character-set-client-handshake
event_scheduler = ON

max_connections = 1000

[mysqld_safe]
log-error=/log/errlog/mariadb_error.log
pid-file=/DATA/data/mariadb.pid

  1. 환경변수 세팅(OS mysql 유저)vi ~/.bash_profile

# MariaDB Path

PATH=$PATH:/usr/mariadb/bin

  1. mariadb 초기화 수행/usr/mariadb/scripts/mysql_install_db --defaults-file=/etc/my.cnf

11 mariadb 기동
$ mysqld --defaults-file=/etc/my.cnf &

  1. mariadb root 패스워드 변경(OS 유저 root로 접속)MariaDB [(none)]> alter user 'root'@'localhost' identified by '*****';Query OK, 0 rows affected (0.002 sec)

##2 Slave

rpm -qa |grep epel-release
rpm -qa |grep net-tools
rpm -qa |grep sysstat
rpm -qa |grep wget
rpm -qa |grep lrzsz
rpm -qa |grep lsof
rpm -qa |grep htop
rpm -qa |grep iftop
rpm -qa |grep rsync
rpm -qa |grep bzip2
rpm -qa |grep unzip
rpm -qa |grep patch
rpm -qa |grep syslog

 

# rpm -qa |grep net-tools

net-tools-2.0-0.25.20131004git.el7.x86_64

 

# rpm -qa |grep sysstat

sysstat-10.1.5-19.el7.x86_64

 

# rpm -qa |grep wget

wget-1.14-18.el7_6.1.x86_64

# rpm -qa |grep lrzsz

lrzsz-0.12.20-36.el7.x86_64

# rpm -qa |grep lsof

lsof-4.87-6.el7.x86_64

# rpm -qa |grep htop

htop-2.2.0-3.el7.x86_64

# rpm -qa |grep iftop

iftop-1.0-0.14.pre4.el7.x86_64

# rpm -qa |grep rsync

rsync-3.1.2-10.el7.x86_64

# rpm -qa |grep bzip2

bzip2-libs-1.0.6-13.el7.x86_64
bzip2-1.0.6-13.el7.x86_64

# rpm -qa |grep unzip

unzip-6.0-21.el7.x86_64

rpm -qa |grep patch

patch-2.7.1-12.el7_7.x86_64
python-jsonpatch-1.2-4.el7.noarch
perl-Log-Dispatch-2.41-1.el7.1.noarch

# rpm -qa |grep syslog

rsyslog-8.24.0-52.el7.x86_64

rpm -qa |grep epel
rpm -qa |grep perl-devel
rpm -qa |grep perl-CPAN
rpm -qa |grep perl-DBD-MySQL
rpm -qa |grep perl-Config-Tiny
rpm -qa |grep perl-Log-Dispatch
rpm -qa |grep perl-Parallel-ForkManager
rpm -qa |grep perl-Module-Install

# rpm -qa |grep epel

# rpm -qa |grep perl-devel

perl-devel-5.16.3-295.el7.x86_64

# rpm -qa |grep perl-CPAN

perl-CPAN-Meta-2.120921-5.el7.noarch
perl-CPAN-1.9800-295.el7.noarch
perl-CPAN-Meta-YAML-0.008-14.el7.noarch
perl-CPAN-Meta-Requirements-2.122-7.el7.noarch
perl-CPANPLUS-0.91.38-4.el7.noarch

# rpm -qa |grep perl-DBD-MySQL

perl-DBD-MySQL-4.023-6.el7.x86_64

# rpm -qa |grep perl-Config-Tiny

perl-Config-Tiny-2.14-7.el7.noarch

# rpm -qa |grep perl-Log-Dispatch

perl-Log-Dispatch-2.41-1.el7.1.noarch

# rpm -qa |grep perl-Parallel-ForkManager

perl-Parallel-ForkManager-1.18-2.el7.noarch

# rpm -qa |grep perl-Module-Install

perl-Module-Install-1.06-4.el7.noarch

  1. 인터페이스 세팅 확인eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet x.x.x.109 netmask 255.255.255.0 broadcast x.x.x.255 inet6 fe80::f816:3eff:fe79:8661 prefixlen 64 scopeid 0x20<link> ether fa:16:3e:79:86:61 txqueuelen 1000 (Ethernet) RX packets 4508615 bytes 926475120 (883.5 MiB) RX errors 0 dropped 54 overruns 0 frame 0 TX packets 66815 bytes 4982545 (4.7 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet x.x.x.127 netmask 255.255.255.0 broadcast x.x.x.255
ether fa:16:3e:79:86:61 txqueuelen 1000 (Ethernet)

  1. 호스트 파일 설정vi /etc/hosts

x.x.x.1 testdb1
x.x.x.2 testdb2
x.x.x.3 mngsever

  1. mysql 유저 생성
  2.  
  3. # passwd mysql
  4. useradd mysql -g mysql
  5. groupadd mysql
  6. mariadb 디렉토리 생성basedir=/usr/mariadblog-error=/log/mariadbd.errdatadir=/DATAlog-bin=/log/binlog/binlogrelay-log=/log/relaylog/relaylog
  7. mkdir -p /log/binlog /log/relaylog /log/errlog /log/slowlog
  8. tar 압축 해제
  9. tar xvzf mariadb-10.5.8-linux-x86_64.tar.gz
  10. 바이너리 이동
  11. mv mariadb-10.5.8-linux-x86_64 /usr/mariadb
  12. 소유자 변경
  13. chown -R mysql:mysql /usr/mariadb /log /DATA
  14. my.cnf 작성

# 

# 

[client]
port=13308
socket=/tmp/mariadb.sock

[mariadb]
user=mysql
port=13308

basedir=/usr/mariadb
datadir=/DATA
log-error=/log/errlog/mariadb_error.log
tmpdir=/tmp
socket=/tmp/mariadb.sock

server-id=2
log_bin=/log/binlog/mariadb-bin
max_binlog_size=200M
binlog_cache_size=2M
binlog_format=MIXED

expire_logs_days=10
relay-log=/log/relay-log/mariadb-relay
relay_log_purge=0
read_only

slow_query_log = 1
slow_query_log_file = /log/slowlog/mariadb-slow.log
log-output=FILE,TABLE
long_query_time = 5
log_slow_rate_limit = 1
log_slow_verbosity = query_plan
log_slow_admin_statements

innodb_buffer_pool_size=16G

performance_schema=ON
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8'
skip-character-set-client-handshake
event_scheduler = ON

max_connections = 1000

[mysqld_safe]
log-error=/log/errlog/mariadb_error.log
pid-file=/DATA/data/mariadb.pid

  1. 환경변수 세팅(OS mysql 유저)vi ~/.bash_profile

# MariaDB Path

PATH=$PATH:/usr/mariadb/bin

  1. mariadb 초기화 수행/usr/mariadb/scripts/mysql_install_db --defaults-file=/etc/my.cnf

11 mariadb 기동
$ mysqld --defaults-file=/etc/my.cnf &

  1. mariadb root 패스워드 변경(OS 유저 root로 접속)MariaDB [(none)]> alter user 'root'@'localhost' identified by '*****';Query OK, 0 rows affected (0.002 sec)
  • replcation 설정
  1. rep 유저 생성 및 권한 부여(Master, Slave 둘 다 수행)CREATE USER 'rep'@'x.x.x.107' IDENTIFIED BY '';CREATE USER 'rep'@'x.x.x.108' IDENTIFIED BY '';CREATE USER 'rep'@'x.x.x.109' IDENTIFIED BY '*****';

GRANT REPLICATION SLAVE ON . TO 'rep'@'x.x.x.107';
GRANT REPLICATION SLAVE ON . TO 'rep'@'x.x.x.108';
GRANT REPLICATION SLAVE ON . TO 'rep'@'x.x.x.109';

  1. 바이너리 로그 포지션 확인MariaDB [(none)]> show master status ;

+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000003 | 1528 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

 

  1. CHANGE MASTER TO 수행(Slave에서)

CHANGE MASTER TO
MASTER_HOST='x.x.x.108',
MASTER_PORT=13308,
MASTER_USER='rep',
MASTER_PASSWORD='*****',
MASTER_LOG_FILE='mariadb-bin.000004',
MASTER_LOG_POS=344,
MASTER_CONNECT_RETRY=10;

 

  1. replication 스레드 시작 및 스테이터스 확인MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave status \G
*********** 1. row ***********
Slave_IO_State: Waiting for master to send event
Master_Host: x.x.x.108
Master_User: rep
Master_Port: 13308
Connect_Retry: 10
Master_Log_File: mariadb-bin.000004
Read_Master_Log_Pos: 344
Relay_Log_File: mariadb-relay.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000004
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: 344
Relay_Log_Space: 864
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: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

----- MHA Node 설정(Master, Slave)

  1. MHA 디렉토리 생성 (M,S)
  2. mkdir -p /usr/mha/source
  3. MHA Node 파일 압축 해제 및 이동
  4. # mv mha4mysql-node-0.57 /usr/mha/source/
  5. tar xvzf mha4mysql-node-0.57.tar.gz
  6. 소유자 변경(mysql:mysql) (M,S)
  7. chown -R mysql:mysql /usr/mha
  8. MHA Node 소스코드 컴파일 (M,S)
  9. perl Makefile.PL
  10. # make
  11. # make install
  12. cd /usr/mha/source/mha4mysql-node-0.57/
  13. VIP 자동 전환을 위한 권한 부여(OS mysql 유저) (M,S)visudomysql ALL=(ALL) NOPASSWD:/sbin/ifconfigmysql ALL=(ALL) NOPASSWD:/usr/sbin/ifupmysql ALL=(ALL) NOPASSWD:/usr/sbin/ifdown
  14. MHA VIP UP/DOWN Privileges
  15. sudo 권한 작동 확인 (M,S)$ sudo ifdown eth0:0$ ifconfigeth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet x.x.x.108 netmask 255.255.255.0 broadcast x.x.x.255 inet6 fe80::f816:3eff:fe0d:2932 prefixlen 64 scopeid 0x20<link> ether fa:16:3e:0d:29:32 txqueuelen 1000 (Ethernet) RX packets 5506779 bytes 1131425845 (1.0 GiB) RX errors 0 dropped 42 overruns 0 frame 0 TX packets 77175 bytes 8225127 (7.8 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

$ sudo ifup eth0:0
$ ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet x.x.x.108 netmask 255.255.255.0 broadcast x.x.x.255
inet6 fe80::f816:3eff:fe0d:2932 prefixlen 64 scopeid 0x20
ether fa:16:3e:0d:29:32 txqueuelen 1000 (Ethernet)
RX packets 5508020 bytes 1131556933 (1.0 GiB)
RX errors 0 dropped 42 overruns 0 frame 0
TX packets 77206 bytes 8228957 (7.8 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet x.x.x.127 netmask 255.255.255.0 broadcast x.x.x.255
ether fa:16:3e:0d:29:32 txqueuelen 1000 (Ethernet)

  1. ssh 키 생성 (OS mysql 유저) (M-S)ssh-keygen -t rsa(엔터 3번)

$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/mysql/.ssh/id_rsa):
Created directory '/home/mysql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/mysql/.ssh/id_rsa.
Your public key has been saved in /home/mysql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:EIsbQt7JTEt5pbhMYiXmNr4j3D3C5CabLvdbGK83u/A mysql@hsnc-okrsdb1
The key's randomart image is:
+---[RSA 2048]----+
| + +.... |
| = O.=.+ |
| O @.+ |
| + * + . |
| ..= S |
|. =..+ |
|.oo_ooo |
|.._..=+ |
|o= .+oE+ |
+----[SHA256]-----+

  1. ssh 키 교환 (OS mysql 유저) (M,S)$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@x.x.x.109/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/mysql/.ssh/id_rsa.pub"The authenticity of host 'x.x.x.109 (x.x.x.109)' can't be established.ECDSA key fingerprint is SHA256:7ZzXQ+4zQEiA96b70HJf9911+mgmC9dLSQY9/kPVaw8.ECDSA key fingerprint is MD5:90:6e:71:d6:db:b7:56:7c:81:66:38:0f:0c:96:b2:94.

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

mysql@x.x.x.109's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'mysql@x.x.x.109'"
and check to make sure that only the key(s) you wanted were added.

  1. ssh 키 교환 확인 (OS mysql 유저) (M,S)$ ssh mysql@x.x.x.109 hostnametestdb2

$ ssh mysql@x.x.x.108 hostname
hsnc-okrsdb1

  1. mysql, mysqlbinlog 심볼릭 링크 생성 (OS root) (M,S)cd /usr/local/binln -s /usr/mariadb/bin/mysql mysqlln -s /usr/mariadb/bin/mysqlbinlog mysqlbinlog
  2. MariaDB MHA 유저 생성 (Master에서만)create user 'mha'@'x.x.x.107' identified by '';create user 'mha'@'x.x.x.108' identified by '';create user 'mha'@'x.x.x.109' identified by '*****';

grant all privileges on . to 'mha'@'x.x.x.107';
grant all privileges on . to 'mha'@'x.x.x.108';
grant all privileges on . to 'mha'@'x.x.x.109';

----- MHA Manager 설정(Manager)

Manager - x.x.x.107

  1. rpm 설치 확인

# rpm -qa |grep epel-release

rpm -qa |grep net-tools
rpm -qa |grep sysstat
rpm -qa |grep wget
rpm -qa |grep lrzsz
rpm -qa |grep lsof
rpm -qa |grep htop
rpm -qa |grep iftop
rpm -qa |grep rsync
rpm -qa |grep bzip2
rpm -qa |grep unzip
rpm -qa |grep patch
rpm -qa |grep syslog

# rpm -qa |grep net-tools

net-tools-2.0-0.25.20131004git.el7.x86_64

# rpm -qa |grep sysstat

sysstat-10.1.5-19.el7.x86_64

# rpm -qa |grep wget

wget-1.14-18.el7_6.1.x86_64

# rpm -qa |grep lrzsz

lrzsz-0.12.20-36.el7.x86_64

# rpm -qa |grep lsof

lsof-4.87-6.el7.x86_64

# rpm -qa |grep htop

htop-2.2.0-3.el7.x86_64

# rpm -qa |grep iftop

iftop-1.0-0.14.pre4.el7.x86_64

# rpm -qa |grep rsync

rsync-3.1.2-10.el7.x86_64

# rpm -qa |grep bzip2

bzip2-libs-1.0.6-13.el7.x86_64
bzip2-1.0.6-13.el7.x86_64

# rpm -qa |grep unzip

unzip-6.0-21.el7.x86_64

# rpm -qa |grep patch

patch-2.7.1-12.el7_7.x86_64
python-jsonpatch-1.2-4.el7.noarch
perl-Log-Dispatch-2.41-1.el7.1.noarch

# rpm -qa |grep syslog

rsyslog-8.24.0-52.el7.x86_64

# rpm -qa |grep epel

# rpm -qa |grep perl-devel

perl-devel-5.16.3-295.el7.x86_64

# rpm -qa |grep perl-CPAN

perl-CPAN-Meta-2.120921-5.el7.noarch
perl-CPAN-1.9800-295.el7.noarch
perl-CPAN-Meta-YAML-0.008-14.el7.noarch
perl-CPAN-Meta-Requirements-2.122-7.el7.noarch
perl-CPANPLUS-0.91.38-4.el7.noarch

# rpm -qa |grep perl-DBD-MySQL

perl-DBD-MySQL-4.023-6.el7.x86_64

# rpm -qa |grep perl-Config-Tiny

perl-Config-Tiny-2.14-7.el7.noarch

# rpm -qa |grep perl-Log-Dispatch

perl-Log-Dispatch-2.41-1.el7.1.noarch

# rpm -qa |grep perl-Parallel-ForkManager

perl-Parallel-ForkManager-1.18-2.el7.noarch

# rpm -qa |grep perl-Module-Install

perl-Module-Install-1.06-4.el7.noarch

  1. hosts 파일 확인

# vi /etc/hosts

x.x.x.1 testdb1
x.x.x.2 testdb2
x.x.x.3 mngsever

  1. MariaDB 클라이언트 압축 해제 및 이동
  2. # mv mariadb-10.5.8-linux-x86_64 /usr/mariadb
  3. tar xvzf mariadb-10.5.8-linux-x86_64.tar.gz
  4. MHA 디렉토리 생성
  5. mkdir -p /usr/mha/source
  6. MHA OS 유저 생성 및 패스워드 변경
  7. # useradd mysql -g mysql
  8. # passwd mysql
  9. groupadd mysql
  1. MHA Node 압축 해제 및 이동
  2. # mv mha4mysql-node-0.57 /usr/mha/source
  3. tar xvzf mha4mysql-node-0.57.tar.gz
  4. 디렉토리 소유자 변경
  5. chown -R mysql:mysql /usr/mha /usr/mariadb
  6. MHA Node 컴파일
  7. # perl Makefile.PL
  8. # make
  9. # make install
  10. cd /usr/mha/source/mha4mysql-node-0.57/
  11. CPAN YAML 설치 (설치가 잘 안될 경우 별도의 CPAN rpm을 다운받아 설치해도 무방)cpan YAMLyesyesperl -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"
  12. MHA Manager 압축 해제 및 이동
  13. # mv mha4mysql-manager-0.57 /usr/mha/source/
  14. tar xvzf mha4mysql-manager-0.57.tar.gz
  15. MHA Manager 컴파일
  16. # perl Makefile.PL
  17. # make
  18. # make install
  19. cd /usr/mha/source/mha4mysql-manager-0.57/
  20. ssh 키 생성 및 교환(OS mysql 유저)su - mysqlssh-keygen -t rsa엔터 3번

$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@x.x.x.109
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/mysql/.ssh/id_rsa.pub"
The authenticity of host 'x.x.x.109 (x.x.x.109)' can't be established.
ECDSA key fingerprint is SHA256:7ZzXQ+4zQEiA96b70HJf9911+mgmC9dLSQY9/kPVaw8.
ECDSA key fingerprint is MD5:90:6e:71:d6:db:b7:56:7c:81:66:38:0f:0c:96:b2:94.
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
mysql@x.x.x.109's password:

Number of key(s) added: 1

ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@x.x.x.108

  1. 키교환 확인$ ssh mysql@x.x.x.108 hostnametestdb1$ ssh mysql@x.x.x.109 hostnametestdb2
  2. os mysql 유저 MHA 환경변수 추가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 /usr/mha/manager.log'

  1. MHA Config 파일 작성vi /etc/mha.cnf

[server default]

user=mha
password=*****

ssh_user=mysql

repl_user=rep
repl_password=*****

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

remote_workdir=/usr/mha

master_binlog_dir=/log/binlog

master_ip_online_change_script=/usr/mha/scripts/master_ip_online_change
master_ip_failover_script=/usr/mha/scripts/master_ip_failover

[server1]
hostname=x.x.x.108
port=13308
candidate_master=1

[server2]
hostname=x.x.x.109
port=13308
candidate_master=1

  1. MHA 스크립트 작성(os mysql 유저)mkdir /usr/mha/scriptscd /usr/mha/source/mha4mysql-manager-0.57/samples/scripts

$ cp master_ip_online_change /usr/mha/scripts/master_ip_online_change
$ cp master_ip_failover /usr/mha/scripts/master_ip_failover

vi /usr/mha/scripts/master_ip_online_change
150, 151, 152 주석 추가
245, 246, 247, 248 주석 추가
248 라인 아래에 아래 코드 추가 후 저장

##vip change
if($new_master_ip eq "x.x.x.108"){
system("bin/sh /usr/mha/scripts/master_vip_up.sh");
}
elsif($new_master_ip eq "x.x.x.109"){
system("bin/sh /usr/mha/scripts/slave_vip_up.sh");
}
else{}

vi /usr/mha/scripts/master_ip_failover
87, 88, 89, 90, 93 라인 주석 추가
93 라인 아래에 아래 코드 추가 후 저장

##vip change
if($new_master_ip eq "x.x.x.108"){
system("bin/sh /usr/mha/scripts/master_vip_up.sh");
}
elsif($new_master_ip eq "x.x.x.109"){
system("bin/sh /usr/mha/scripts/slave_vip_up.sh");
}
else{}

  1. vip up/down 스크립트 작성vi /usr/mha/scripts/master_vip_up.sh

#!/bin/sh

# master : x.x.x.108

# slave : x.x.x.109

# VIP Interface : eth0:0

ssh mysql@x.x.x.109 sudo /usr/sbin/ifdown eth0:0
ssh mysql@x.x.x.108 sudo /usr/sbin/ifup eth0:0

vi /usr/mha/scripts/slave_vip_up.sh

#!/bin/sh

# master : x.x.x.108

# slave : x.x.x.109

# VIP Interface : eth0:0

ssh mysql@x.x.x.108 sudo /usr/sbin/ifdown eth0:0
ssh mysql@x.x.x.109 sudo /usr/sbin/ifup eth0:0

  1. 스크립트 실행 권한 부여chmod 750 /usr/mha/scripts/*.sh

이렇게 해서 MariaDB 이중화 구성이 완료되었다.
다음에는 MHA를 활용하여 failover TEST를 해보고자 한다.

'MySQL, MariaDB' 카테고리의 다른 글

MariaDB SQL튜닝 사례-1  (0) 2021.03.15
MariaDB에서 테이블스페이스란?  (0) 2020.04.06
MariaDB - 데이터베이스 및 사용자 생성  (0) 2020.03.27
MariaDB 기동 및 서버 로그인  (0) 2020.03.25
MariaDB Enterprise Server 버전별 feature summary  (2) 2020.03.25

'MySQL, MariaDB' Related Articles

티스토리툴바