[DB] MySQL Master-Slave 복제(Replica) 과정과 Binlog란?

0. 개요

회사에서 prd 환경에 Master, Slave MYSQL을 사용하고 있어, 해당 과정에 대해 공부해보았다. 
 
 

1. NCloud

https://www.ncloud.com/v2/product/database/cloudDbMysql#detail

NAVER CLOUD PLATFORM

DB에 대한 HA를 보장하기 위해 Master와 Slave, 그리고 Standby Master DB가 있다.
Standby Master DB는 Master DB가 죽었을때 Master로 승격된다.
 

2. Spring 설정

application-prd.properties

# Master Database (Write)
spring.datasource.master.jdbc-url=${DB_MASTER_URL}
spring.datasource.master.username=${DB_MASTER_USERNAME}
spring.datasource.master.password=${DB_MASTER_PASSWORD}
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
# Slave Database (Read)
spring.datasource.slave.jdbc-url=${DB_SLAVE_URL}
spring.datasource.slave.username=${DB_SLAVE_USERNAME}
spring.datasource.slave.password=${DB_SLAVE_PASSWORD}
spring.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver

 
DataSourceConfig

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * Production 환경 Master-Slave DB 설정
 * @Profile("prd") 조건으로 운영 환경에서만 활성화
 */
@Profile("prd")
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    public DataSource routingDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slaveDataSource") DataSource slaveDataSource
    ) {
        RoutingDataSource routingDataSource = new RoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("master", masterDataSource);
        dataSourceMap.put("slave", slaveDataSource);

        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);

        return routingDataSource;
    }

    @Primary
    @Bean
    public DataSource dataSource(@Qualifier("routingDataSource") DataSource routingDataSource) {
        // LazyConnectionDataSourceProxy를 사용하여 트랜잭션 시작 시점에 DataSource 결정
        return new LazyConnectionDataSourceProxy(routingDataSource);
    }
}

 
RoutingDataSource

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;

/**
 * Master-Slave DB 라우팅을 위한 DataSource
 * @Transactional(readOnly=true) -> Slave DB
 * @Transactional(readOnly=false) -> Master DB
 */
public class RoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        return isReadOnly ? "slave" : "master";
    }
}

 
Transation이 read-only라면, Slave DB를 보도록 되어 있고, read-only가 아니라면, Master DB를 보도록 설정되어 있다.
 
 

3. 내부 과정 이해

Slave DB에게 Master DB의 변경사항을 어떻게 전달해주냐가 궁금해져서 공부해봤다.
https://learning.oreilly.com/library/view/hyoyuljeogin-mysql-seongneung/9798341655560/ch07.html

효율적인 MySQL 성능

 
Master DB에게 SQL 쿼리가 전달되고, 이를 실행하면, row 별로의 변경 사항을 binlog에 적재한다.
그리고 이를 Replica DB에 전달해 실행하게 된다. 전달 과정은 반동기로 이루어진다. 

4.  Binlog에 대하여..

1. mysql 접속 & 비밀번호 작성

sh-5.1# mysql -u root -p

 
2. binlog 있는지 확인

mysql> SHOW BINARY LOGS;

 
3. binlog 이벤트 확인

mysql> SHOW BINLOG EVENTS IN 'binlog.000002' LIMIT 20;

• Event_type: 작업 종류 (예: Query, Write_rows, Xid)
• Info: 어떤 SQL문이 실행되었는지 간략 소개

4. 테이블 만들고 다시 확인하기

확연히 binlog.000005의 File size가 늘어남을 확인할 수 있다.
 
해당 binlog을 까보면 다음과 같이 변경분을 확인할 수 있다. 

로그 파일들은 db 서버가 시작될때마다 새로 생겨나고,

ROW 포맷이니, BINLOG에는 SQL 명령어가 아닌 데이터가 무엇에서 어떻게 바뀌었는지에 대한 정보를 Binary 형태로 가지고 있다.

 
[이벤트 타입]

• Table_map (370번): "이제부터 room_bed 테이블을 건드릴 거야"라고 선언
• Write_rows (455번): 실제로 삽입된 각 행의 데이터 값들이 이 안에 들어있음
• Xid (17401번): 이 모든 변경 사항이 성공적으로 Commit 되었음을 나타냄

[pos 455 분석해보기]

• 시작 위치(Pos): 455
• 종료 위치(End_log_pos): 17401
• 크기 계산: 17401 - 455 = 16,946 바이트 (약 16.5 KB)
• 단 하나의 Write_rows(데이터 삽입) 이벤트가 16.5 KB나 차지하고 있다. 보통 INSERT 한 줄은 수백 바이트면 충분한데, 16KB가 찍혔다는 건 room_bed라는 테이블에 상당히 많은 양의 데이터가 한 번에 들어왔음을 알 수 있다.

BINLOG는 604800초(7일) 후에 삭제되는 것으로 설정되어 있었다.

'binlog_space_limit'이 딱히 설정이 안되어있기에 현재로써는 7일이내의 로그들은 무조건 저장이 된다. 디스크 용량이 다 차서 공간이 없게 된다면, 수동으로 지울 수 있는데 다음의 명령어를 이용하면 된다.

 
- 특정 날짜/시간 기준으로 삭제

PURGE BINARY LOGS BEFORE '2023-12-15 00:00:00';

 
- 특정 파일 이름을 기준으로 삭제

PURGE BINARY LOGS TO 'binlog.000005';