MySQL/MariaDB :: 단방향, 양방향 이중화 (Replication)

데이터베이스 이중화에 대한 내용
2대 이상의 DBMS를 나눠서 데이터를 저장한다.
삽입, 수정, 삭제 모두 복제할 수 있으며, 함수까지도 복제가 가능하다.
부분적으로 복제하는 것도 가능하다.

CentOS 7, mysql Ver 15.1 Distrib 10.3.37-MariaDB

---

데이터베이스 양방향 이중화

my.cnf 설정

1번 서버 my.cnf

...
[mysqld]
server-id=1
log-bin=binlog
binlog_format=mixed
sync_binlog=1
binlog_cache_size=2M
max_binlog_size=512M
expire_logs_days=7
log-bin-trust-function-creators=1
...

2번 서버 my.cnf

...
[mysqld]
server-id=2
log-bin=binlog
binlog_format=mixed
sync_binlog=1
binlog_cache_size=2M
max_binlog_size=512M
expire_logs_days=7
log-bin-trust-function-creators=1
...

MySQL/MariaDB 를 재시작 한다.
systemctl restart mariadb 또는 systemctl restart mysql

파라미터 설명

• server-id
  서버를 지칭하는 번호, 각 서버마다 구분되는 번호이므로 숫자만 다르면 된다.
• log-bin
  binlog의 파일명
  • binlog란 DBMS에서 발생하는 모든 내역들이 기록되는 파일
• binlog_format
  binlog에 어떻게 저장할 것인지 결정하는 유형(row | mixed | statement)
  • statement
    전통적으로 사용하던 방식으로 실행된 쿼리 구문 그대로 Binlog에 남겨지는 방식 입니다.
  • row
    생성/변경된 데이터의 Before/After row image가 binlog에 저장하여, 사이즈가 statement보다 크게 생성 됩니다.
  • mixed
    row와 statement 중간 형태로 날짜와 같이 determine이 필요한 경우 row형태로 저장되는 형식 입니다.
• sync_binlog
  몇번의 트랜잭션마다 바이너리 로그를 디스크에 동기화를 설정하는 매개 변수
• binlog_cache_size
  DBMS는 binlog를 기록하는 내용을 캐시하며 그 사이즈를 설정
• max_binlog_size
  바이너리 로그 파일의 최대 크기 지정
• expire_logs_days
  바이너리 로그 파일 보관 기간(일 단위)
• log-bin-trust-function-creators
  함수, 프로시저, 트리거 등의 권한

Master 서버 연결

단, 양방향 이므로 서로가 Master이며 Slave가 된다.

서버 정보 확인

각 서버의 데이터베이스 연결 후 show master status

• 1번 서버

  +---------------+----------+--------------+------------------+
  | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +---------------+----------+--------------+------------------+
  | binlog.000001 |    11111 |              |                  |
  +---------------+----------+--------------+------------------+

• 2번 서버

  +---------------+----------+--------------+------------------+
  | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +---------------+----------+--------------+------------------+
  | binlog.000002 |    22222 |              |                  |
  +---------------+----------+--------------+------------------+

File과 Position은 추후 Slave 설정에 필요하므로 따로 메모한다.
해당 과정을 1번 서버, 2번 서버 반복하여 2개의 서버 모두 확인한다.

1번 서버 MASTER 연결

• 1번에서 실행

  CHANGE MASTER TO
  MASTER_HOST='0.0.0.2',
  MASTER_USER='user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='binlog.000002',
  MASTER_LOG_POS=22222;

• MASTER_HOST
  2번 DB서버의 IP를 입력
• MASTER_USER, MASTER_PASSWORD
  2번 DB서버의 접속 계정
• MASTER_LOG_FILE, MASTER_LOG_POS
  위에서 확인한 2번 DB서버의 MASTER 정보를 입력

변경 후 start slave;
서버 연결 후 MySQL/MariaDB 를 재시작 한다.

2번 서버 MASTER 연결

• 2번에서 실행

  CHANGE MASTER TO
  MASTER_HOST='0.0.0.1',
  MASTER_USER='user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=11111;

• MASTER_HOST
  1번 DB서버의 IP를 입력
• MASTER_USER, MASTER_PASSWORD
  1번 DB서버의 접속 계정
• MASTER_LOG_FILE, MASTER_LOG_POS
  위에서 확인한 1번 DB서버의 MASTER 정보를 입력

변경 후 start slave;
서버 연결 후 MySQL/MariaDB 를 재시작 한다.

이제 쿼리 실행시키면서 각 서버에서 데이터를 확인한다.
만약 정상적으로 작동하지 않는다면 SHOW SLAVE STATUS;로 Slave_SQL_Running이 Yes인지 확인하고,
Master Log File, Master Log Pos 설정을 다시 한 번 확인한다.
만약 제대로 실행이 안 되었다면 Master 연결 과정을 다시 진행한다.

---

데이터베이스 단방향 이중화

Master와 Slave로 나누어 단방향 이중화를 하는 방법

my.cnf 설정

1번 서버(Master) my.cnf

...
[mysqld]
server-id=1
log-bin=binlog
binlog_format=mixed
sync_binlog=1
binlog_cache_size=2M
max_binlog_size=512M
expire_logs_days=7
log-bin-trust-function-creators=1
...

2번 서버(Salve) my.cnf

...
[mysqld]
server-id=2
log-bin=binlog
binlog_format=mixed
sync_binlog=1
binlog_cache_size=2M
max_binlog_size=512M
expire_logs_days=7
log-bin-trust-function-creators=1
log-slave_updates # master에서 받아온 변경사항을 자신의 log에 기록
read_only=1
...

서버 연결 후 MySQL/MariaDB 를 재시작 한다.

Master 서버 연결

Master 서버는 정보만 확인하고 Slave 서버에서 Master 서버를 연결한다.

서버 정보 확인

Master 서버의 데이터베이스 연결 후 show master status

• Master 서버

  +---------------+----------+--------------+------------------+
  | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +---------------+----------+--------------+------------------+
  | binlog.000001 |    11111 |              |                  |
  +---------------+----------+--------------+------------------+

File과 Position은 Slave 설정에 필요하므로 따로 메모한다.

Slave 서버 MASTER 연결

• Slave서버에서 실행

  CHANGE MASTER TO
  MASTER_HOST='0.0.0.1',
  MASTER_USER='user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=11111;

• MASTER_HOST
  MASTER DB서버의 IP를 입력
• MASTER_USER, MASTER_PASSWORD
  MASTER DB서버의 접속 계정
• MASTER_LOG_FILE, MASTER_LOG_POS
  위에서 확인한 MASTER DB서버의 정보를 입력

변경 후 start slave;
서버 연결 후 MySQL/MariaDB 를 재시작 한다.

이제 쿼리 실행시키면서 각 서버에서 데이터를 확인한다.
만약 정상적으로 작동하지 않는다면 SHOW SLAVE STATUS;로 Slave_SQL_Running이 Yes인지 확인하고,
Master Log File, Master Log Pos 설정을 다시 한 번 확인한다.
만약 제대로 실행이 안 되었다면 Master 연결 과정을 다시 진행한다.

---

참고자료

🔥MySQL Replication 단방향 이중화

🔥mysql or mariadb replication

---