[리눅스] MySQL(MariaDB) Replication - DB 실시간 이중화(Master/Slave)

 

# MySQL(MariaDB) Replication

- DB 복제를 의미

- 2대 이상의 DBMS 를 나눠서 데이터를 저장하는 방식

(master-slave, master-master, 다중 slave 방식 등)

- 비동기 복제 방식

 

# Replication(복제)을 하는 이유

- 실시간 데이터 백업이 가능

- DB서버 부하 분산이 가능

(master에서는 주로 삽입, 삭제, 수정 / slave 에서는 주로 조회)

 

# Master 와 Slave DB

- Master : 데이터에 대한 변경(등록, 수정, 삭제) event 발생시 binary logs 에 기록하고, slave 서버에 전달

  (master 는 slave 에 대한 정보를 전혀 모름. - 몇 개의 slave가 있는지 등)

- Slave : master에서 전달받은 binarylog를 읽어 DB에 반영

 

# Binarylog 란?

- binarylog = binlog

- mysql(maria DB)에서 발생하는 모든 내역들이 기록되는 파일

- binary log 는 default 는 비활성되어 있는데, 이를 활성화 시켜야 함

 

# 복제 방식

- STATEMENT : SQL문을 복사

- ROW : 변경된 row만 복사

- MIXED : 두가지 방식을 섞어서 복사

 

# 주의 사항

- master와 slave의 구성환경을 동일해야 함.(ex. mysql 버전!)

- master → slave 순으로 작동시켜야 함

-  replication 을 하다보면 binary log가 계속 쌓임. 이를 cron을 이용하여 처리하거나

   my.cnf 에 expire_logs_days 에 유효기간을 지정

- Replication 과정에서 에러가 발생하는 경우, slave는 에러 로그를 작성한다.

  후에 에러를 추적하여 바이너리 로그를 분석하여 유실된 자료에 대해 자동 업데이트가 가능

 

# Replication 동작과정

 

#실습

# MasterDB (centos8)

# 방화벽 먼저 꺼주기
systemctl stop firewalld

# Master DB 설정
yum -y install mariadb-server
vi /etc/my.cnf.d/mariadb-server.cnf
######################################
# 맨 마지막 줄에 붙여넣기
log-bin = mysql-bin 
server-id = 1 
binlog_format = row 
expire_logs_days = 2
######################################저장

systemctl restart mariadb   # systemctl enable --now mariadb

mysql
> grant replication slave on *.* to 'slave_db'@'%' identified by '1234'; # slaveDB 복제
> show master status;  # 번호 복사
# File명과 Position 잘 기록하기
*** DB 재시작시 file명과 position 번호가 변경될 수 있음

 

# SlaveDB (centos8-2)

# 방화벽 먼저 꺼주기
systemctl stop firewalld

# Master DB 설정
yum -y install mariadb-server
vi /etc/my.cnf.d/mariadb-server.cnf
######################################
# 맨 마지막 줄에 붙여넣기
log-bin = mysql-bin 
server-id = 2
binlog_format = row 
expire_logs_days = 2
######################################저장

systemctl restart mariadb  
=> mysql -u root -p => mysql 접속

# master 연동 설정
CHANGE MASTER TO 
        MASTER_HOST="10.10.10.10", 
        MASTER_USER="slave_db", 
        MASTER_PASSWORD="12345678", 
        MASTER_PORT=3306, 
        MASTER_LOG_FILE="mysql-bin.000001",   # 수정
        MASTER_LOG_POS= 328;           # 수정
        
start slave; #slave 실행
show slave status;  # slave 상태 확인

 

# Master(MariaDB) Replication 확인(연동 확인)

## 1. DB 확인
# Master 
>create database TEST_DB;

# Slave 
show databases;   # 생성된 DB 확인

## 2. Table 확인
# Master 
mysql> use TEST_DB; 
mysql> CREATE TABLE USER_TB(
        user_idx INT PRIMARY KEY AUTO_INCREMENT,
        user_name VARCHAR(10) NOT NULL
        );

# Slave
mysql> use TEST_DB; 
mysql> show tables;   # 생성된 테이블 확인

## 3. DATA 확인
# Master 
mysql> INSERT USER_TB(user_name) values('daeun');

# Slave
mysql> SELECT * FROM USER_TB;   # 생성된 데이터 확인

좌) master DB / 우) slave DB   > DB 연동 확인

 

 

# HAProxy : Master DB 와 Slave DB 로드밸런싱(centos7 - DB없는서버)

yum -y install mariadb  # mariadb 클라이언트 다운로드

vi /etc/haproxy/haproxy.cfg
##################################################
# 마지막 줄 추가하기
frontend  mysql-in
    bind *:3306
    default_backend    db_servers

backend db_servers
    balance            roundrobin
    server             db01 10.10.10.10:3306 check
    server             db02 10.10.10.20:3306 check

##################################################저장

systemctl restart haproxy

#NGW => Mariadb 서버 TCP 3306 포트 허용
firewall-cmd --permanent --add-service=mysql --zone=external
firewall-cmd --reload
firewall-cmd --zone=external --list-all

# 로드밸런싱 확인을 위한 원격 접속
mysql -h 192.168.1.111 -u root -p

 

 

#HAProxy활용하여 로드밸런싱으로 DB 원격 로그인 설정

- DB없는 서버(centos7)에서 Master/Slave DB(centos8/8-2)로 원격 접속

# 원격 접속 설정

##centos7(DB 없는 원격접속 서버)
yum -y install mariadb  # mariadb 클라이언트 다운로드

##centos8/8-2(master/slave)
mysqladmin -u root password '1234'   #비번 설정
mysql -u root -p '1234'    # 로그인

mysql > show database;
mysql > use mysql
mysql > select user, host from user where user not like ' ';   # 원격접근권한 여부 확인(% 확인)
mysql > grant all privileges on *.* to  root@'192.168.1.%' identified by '4321';  # 원격접근권한 부여

#show tables;
#desc user;


# 로드벨런싱(haproxy) 설정

##centos7(DB 없는 원격접속 서버)
vi /etc/haproxy/haproxy.cfg
##################################################
# 마지막 줄 추가하기
frontend  mysql-in
    bind *:3306
    default_backend    db_servers

backend db_servers
    balance            roundrobin
    server             db01 10.10.10.10:3306 check
    server             db02 10.10.10.20:3306 check

##################################################저장

systemctl restart haproxy

# DB 포트 허용
##centos7(centos8, 8-2에도 방화벽이 있다면 해줘야함)
#NGW => Mariadb 서버 TCP 3306 포트 허용
firewall-cmd --permanent --add-service=mysql --zone=external
firewall-cmd --reload
firewall-cmd --zone=external --list-all


# 로드밸런싱 확인을 위한 원격 접속
##centos7(DB 없는 원격접속 서버)
mysql -h 192.168.1.111 -u root -p   # root 로 계정 원격 접속
>4321
#mysql -h 192.168.1.111 -u slave_db -p   # slave_db 용 계정 접속 #db가 1개만 뜬다
#>'1234'

원격접근권한 여부 확인(% 확인)

mysql -h 192.168.1.111 -u root -p 결과(하나는 kosa_test db 가 있음 > 로드밸런싱 확인)

 

# root가 아닌 slave_db 용 계정 접속

#mysql -h 192.168.1.111 -u slave_db -p   

DB 1개만 나옴

---

 

오늘은 MariaDB 복제를 통해 Master와 Slave DB를 구성하고, DB를 실시간으로 이중화하는 방법을 배워보았습니다. 또한 DB 원격접속으로 로드벨런싱(HAProxy)하는 방법에 대해서도 알아보았습니다. Master/Slave 형식의 DB 이중화는 많이 쓰이는 방식이니 잘 알아두시면 좋을 것 같습니다. 그럼 다음에 뵙겠습니다.