MariaDB 리플리케이션(Replication) 이해 및 구축(Rocky Linux)
이번 시간에는 리플레이케이션 개념에 대해 알아보고, 구축 방법에 대해 기술하고자 합니다.
리플리케이션의 목적
DB 서버가 한대 있다고 가정해 봅시다.
사이트의 이용자가 많아져서 DB의 트랜잭션이 많아졌고, 결국 DB의 부하로 인하여 사이트가 느려지고 있습니다.
이럴 때 어떻게 해야 할까요?
이때 사용되는 기법 중 하나가 DB의 리플리케이션을 구축하는 것입니다.
서버를 다수개를 이용하여 각 서버에 DB를 설치합니다. Master DB에 데이터를 insert, update, delete 등을 수행하면 Slave DB들에 자동으로 동일한 명령어를 실행시켜 데이터를 동일하게 만듭니다. (Master DB에만 insert 등을 실행해야 합니다.)
웹서버에서는 Master DB에만 inset, update, delete를 실행하고, Slave DB에는 Select만 실행시키게 구현합니다.
이렇게 함으로써, DB의 부하를 분산하여 사용할 수 있습니다.
위의 설명은 DB 부하분산의 용도를 설명한 것이며,
이외에도 백업, 데이터배포, 데이터분석용도로도 사용할 수 있습니다.
아래의 구축 설명은 DB 분산 부하를 줄이기 위한 설계방법으로 보시면 됩니다.
리플리케이션 구축하기
[개발환경]
OS : Rocky Linux
DB : MariaDB 10.2
서버 두대에 각각 Master, Slave로 구성하여 단방향 이중화를 구현해 보도록 하겠습니다.
각 서버에 MariaDB를 설정했다는 가정하에 그 이후의 설정이라고 보시면 됩니다.
마스터(Master) 설정하기 (192.168.0.1)
1. 환경설정
$ vi /etc/my.cnf.d/server.cnf
[mysqld]
# replication setting (master)
server_id=1
log-bin=mysql-bin
binlog-format=row
expire_logs_days=30서버 설치에 따라서 /etc/my.cnf.d/server.cnf가 아닌 /etc/my.cnf에 설정내용이 작성되어있을 수도 있습니다.
- server_id : 고유번호입니다. slave 설정할 때는 2라고 적어줄 겁니다.
- log-bin : bin log 파일명을 설정합니다.
mysql-bin 이라고 적으면 데이터 폴더에 가면mysql-bin.003371,mysql-bin.003372와 같은 파일이 생성되는 것을 확인할 수 있습니다.
이 파일을 이야기 하기에는 하나의 별도 포스팅이 필요한데, 간단하게 이야기하면
해당 파일에는 슬레이브가 당겨갈 명령어들이 기술돼 있습니다.
예를 들어 inset 구문을 마스터에서 실행하면 bin파일에 inset 구문, 해당 구문의 고유번호, 실행시간 등의 기록이 쌓이고, 고유번호를 슬레이브에서 당겨와서 해당 구문을 실행하여 동기화를 시켜준다. 대충 이렇게만 알고 우선 넘어갑시다. - binlog-format : bin파일에 저장 시 어떤 방식으로 저장할래?
- expire_logs_days : bin 파일 보존기간을 며칠로 잡을래? 설정한 기간 지나면 자동으로 파일 삭제됩니다.
2. DB 재 실행
$ systemlctl restart mariadb
3. DB 계정 생성
# 원형
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '계정명'@'%' IDENTIFIED BY '패스워드';
# 실행한 명령어
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user1'@'%' IDENTIFIED BY 'pw123!@#';슬레이브와 통신할 MariaDB 계정을 생성하였습니다.
4. 마스터 상태 확인
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.003371
Position: 71120585
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)마스터 설정을 끝났습니다. (간단하죠?)
상태 값을 확인하면(show master status)File과 Position 항목이 보입니다. (이 값은 슬레이브 설정할 때 필요합니다. 우선 기억해 놓고 있습니다.)
$ ls -alh /var/lib/mysql/
rw-rw---- 1 mysql mysql 1.1G 11월 22 15:04 mysql-bin.003367
rw-rw---- 1 mysql mysql 1.1G 11월 22 16:38 mysql-bin.003368
rw-rw---- 1 mysql mysql 1.1G 11월 22 17:59 mysql-bin.003369
rw-rw---- 1 mysql mysql 1.1G 11월 22 21:05 mysql-bin.003370
rw-rw---- 1 mysql mysql 82M 11월 22 21:37 mysql-bin.003371
rw-rw---- 1 mysql mysql 73M 11월 22 21:30 mysql-bin.err
rw-rw---- 1 mysql mysql 3.1K 11월 22 21:05 mysql-bin.index우선 MariaDB 설치된 파일을 확인하면 mysql-bin.으로 시작하는 파일들이 보입니다.
- File : 현재 마스터의 bin파일이 가리키고 있는 파일명을 말합니다.
ls를 통해 확인 한 목록 중mysql-bin.003371파일 보이시죠? 그 파일을 가리키고 있다는 의미입니다.
- Position : 마지막 실행된 쿼리의 고유번호
mysql-bin.003371은 vi 등으로 열어봐도 깨진 문자만 보이는데,mysqlbinlog명령어를 이용하여 평문으로 변경하는 방법 있습니다. 위의 이미지는 평문으로 변경 후 특정 부분을 발췌한 내용입니다.
at 옆의 숫자가 고유번호입니다. 해당 고유번호에는 click_log2 테이블에 뭔가를 inset를 했네요.
Position의 71120585 라는 숫자는 저 고유번호 숫자를 의미합니다.
즉, "마스터는mysql-bin.003371파일의71120585번까지 실행했다."라는 의미입니다.
슬레이브(Slave) 설정하기 (192.168.0.1)
1. 환경설정
$ vi /etc/my.cnf.d/server.cnf
[mysqld]
# replication setting (slave) =====================================
server_id=2
relay-log=mysql-relay-bin
relay_log_purge=1
log-bin=mysql-bin
binlog-format=row
expire_logs_days=30마스터 설정과 거의 유사합니다. 추가된 부분만 확인해 보겠습니다.
- relay-log
마스터의 bin파일의 포지션의 숫자가 달라지면 슬레이브는 이벤트에 의하여 마스터의 포지션에 해당되는 쿼리를 가져와 실행하게 됩니다. 그 로그를 관리하는 역할이 relay-log이며, log-bin처럼 파일명을 설정하게 됩니다. - relay_log_purge
위 값을 설정 안 하면 디폴트가 1입니다. 즉 위의 설정은 안 해도 되는 상황이지만 설명을 위해 추가하였습니다.
해당 값이 1이면 더 이상 사용하지 않는 relay-log는 삭제하라는 의미입니다.
0은 삭제하지 말라는 의미입니다. log-bin=mysql-bin,binlog-format=row,expire_logs_days=30이 3개의 설정은 마스터 설정에서도 작성했었습니다.
기입해도 되고, 안해도 되는 상황인데, 추후 작성할 다중소스복제(Multi-Source Replication)를 위해 남겨놨습니다.
쉽게 말해, 슬레이브로만 사용하려면 해당 설정은 안해도 되고, 나중에 슬레이브면서 마스터로 설정하려면 해당 설정은 해야 합니다.
2. DB 재 실행
$ systemlctl restart mariadb
3. DB 계정 설정
# 원형
mysql> CHANGE MASTER TO MASTER_HOST = '마스터IP', MASTER_USER='마스터계정', MASTER_PASSWORD = '마스터계정의패스워드'
mysql> CHANGE MASTER TO MASTER_LOG_FILE='마스터LogFile명', MASTER_LOG_POS=포지션
# 실제 실행 쿼리
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.1', MASTER_USER='replication_user1', MASTER_PASSWORD = 'pw123!@#'
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.003371', MASTER_LOG_POS=71120585마스터에서 설정한 계정에 맞게 설정하고,
MASTER_LGO_FILE, MASTER_LOG_POS 에는 마스터에서 show master status를 통해 확인한 File과 Position을 기입합니다.
마스터 부분에서 File과 Postion 부분을 이해하셨다면 쉽게 이해하실 겁니다.
4. slave 시작 및 상태 확인
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: replication_user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.003371
Read_Master_Log_Pos: 71120585
Relay_Log_File: mysql-relay-bin.003328
Relay_Log_Pos: 118201274
Relay_Master_Log_File: mysql-bin.003371
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: 208948682
Relay_Log_Space: 118201630
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: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itstart salve; stop salve; 명령어를 통해 슬레이브를 실행 또는 멈추게 할 수 있습니다.
show slave status; 명령어를 통해 슬레이브 상태 값을 확인할 수 있습니다.
중요한 몇 가지 항목만 살펴보도록 하겠습니다.
- Slave_IO_State : 마스터와 연결 시도, 대기 등의 상태를 알려줍니다.
- Master_Host : 마스터 서버의 IP
- Master_User : 마스터와의 통신 계정
- Master_Log_File : I/O 스레드에서 현재 읽고 있는 로그 파일명
- Read_Master_Log_Pos : I/O 스레드에서 마지막으로 읽은 마스터 로그에서 읽은 고유번호
- Slave_IO_Running : I/O 스레드가 시작되어 마스터와 연결되어 있는지 여부
- Slave_SQL_RUNNING : SQL 쓰레드가 시작되어있는지의 여부
- Slave_SQL_Running_State : 슬레이브 연결 상태 / 리플리케이션이 깨졌다면 깨진 사유를 보여줍니다.
이렇게 하여 마스터/슬레이브 구축이 완료되었습니다.
자주 사용하는 명령어
슬레이브 에러 한 단계 건너뛰기
mysql> stop slave;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;실수로 슬레이브에 inset를 하고, 마스터에서 같은 테이블에 inset를 하면 리플리케이션이 깨지게 됩니다.
이럴 경우 슬레이브, 마스터에서 inset 한 것을 각각 삭제를 해주고, (어떻게 해서든지 데이터를 맞춰주고)
슬레이브가 바라보는 포지션을 하나 올려주면 다시 리플리케이션이 붙게 됩니다.
이런 경우 마스터에서 포지션 확인(show master status) 후
슬레이브에서 포지션 변경(CHANGE MASTER TO MASTER_LOG_FILE='마스터LogFile명' 어쩌고저쩌고)을 할 수 있겠지만 SQL_SLAVE_SKIP_COUNTER를 올려줌으로써 해당 포지션을 스킵하도록 간단하게 처리할 수도 있습니다.
상태값 확인
# 마스터에서 상태값 확인
mysql> show master status;
# 슬레이브에서 상태값 확인
mysql> show slave status;
Bin Log 파일 안 맞을 경우(Slave에서 실행)
# 원형
mysql> change master to master_log_file='binlog파일명', master_log_pos=포지션번호;
# 실 쿼리
mysql> change master to master_log_file='mariadb-bin.000279', master_log_pos=329;
빈로그 평문으로 변경하는 방법
# DB7-bin.000143 파일을 DB7-bin.000143.txt 파일로 평문으로 변경해라.
$ mysqlbinlog ./DB7-bin.000143 > DB7-bin.000143.txt
# 해당 파일에서 member단어를 찾아서 앞뒤 10줄씩 DB7-bin.000143.txt에 저장해라
$ mysqlbinlog ./DB7-bin.000143 | grep -A10 -B10 'member' > DB7-bin.000143.txt
# -d : test DB의
# -T : tb_test 테이블 중
# 해당 날짜에에 실행한 쿼리를 뽑아줘~
$ mysqlbinlog mysql-bin.000254 -d test -T tb_test --start-datetime="2022-11-22 00:00:00" --stop-datetime="2022-11-22 12:00:00" >recover_1.sql
쌓인 빈로그 확인 및 삭제
# 빈로그 확인
Mysql> show binary logs;
| mysql-bin.003365 | 1073742529 |
| mysql-bin.003366 | 1073950436 |
| mysql-bin.003367 | 1073743276 |
| mysql-bin.003368 | 1073742875 |
| mysql-bin.003369 | 1080361832 |
| mysql-bin.003370 | 1073743178 |
| mysql-bin.003371 | 356668405 |
+------------------+-------------+
# mysql-bin.003367 이전 로그는 다 삭제해라 (용량 부족할때 가끔 실행했다)
mysql> purge master logs to 'mysql-bin.003367';
리플리케이션 동작 방식
리플리케이션 동작 흐름은 아래와 같습니다.
- 마스터에서 데이터 변경(inset, update 등)이 일어나면 마스터에 데이터를 반영합니다.
- 마스터에서 변경된 이력을 Binary Log에 기록 후 관련 이벤트를 슬레이브에 전달합니다.
[슬레이브] - 슬레이브는 IO_THREAD에서 마스터 이벤트를 감지하고
- 마스터 Binay Log의 데이터를 Relay Log 공간에 저장합니다.
- SQL_THREAD는 Relay Log를 읽고 슬레이브 데이터베이스에 기록합니다.
'개발 > MariaDB' 카테고리의 다른 글
| MariaDB 다중 소스 복제(Multi-Source Replication) 구축 및 명령어 (0) | 2022.11.29 |
|---|---|
| Mariadb 리플리케이션(Replication) 은 어떻게 동작하는 걸까? (0) | 2022.11.25 |
| XMPP mariadb 데이터 초기화 하는 방법 (0) | 2022.10.28 |
| MySQL innoDB는 어떻게 생겼나요? [InnoDB Architecture] (0) | 2022.10.26 |
| [MySQL] MySQL 리플리케이션 UUID 관련 에러 (0) | 2022.10.25 |