[MySQL, Spring] 로컬 Replication 세팅

목표

MySQL 8.0, Spring Boot 3 을 이용하여 Replication 환경세팅.
Master 1개 인스턴스 - Slave 2개 인스턴스 환경 세팅을 목표로한다.

Spring 에서는 Transaction Readonly 인 경우 slave DB들에만 접근하고,
그 외의 경우 Master DB 에 접근하도록 세팅한다.

MySQL 세팅

아래와 같은 디렉토리 구조를 만들어서 docker compose 를 이용한다.

docker-compose.yml

version: "3"
services:
  mysql_one:
    image: mysql:8.0.35-debian
    container_name: mysql_1
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Seoul
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: repl
    volumes:
      - ./data_one:/var/lib/mysql
      - ./conf.d_one:/etc/mysql/conf.d
  mysql_two:
    image: mysql:8.0.35-debian
    container_name: mysql_2
    ports:
      - 3307:3306
    environment:
      TZ: Asia/Seoul
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: repl
    volumes:
      - ./data_two:/var/lib/mysql
      - ./conf.d_two:/etc/mysql/conf.d
  mysql_three:
    image: mysql:8.0.35-debian
    container_name: mysql_3
    ports:
      - 3308:3306
    environment:
      TZ: Asia/Seoul
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: repl
    volumes:
      - ./data_three:/var/lib/mysql
      - ./conf.d_three:/etc/mysql/conf.d

conf.d_one/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
gtid_mode=ON
enforce-gtid-consistency=ON
server_id = 1

[mysqldump]
default-character-set = utf8mb4

conf.d_two/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
gtid_mode=ON
enforce-gtid-consistency=ON
server_id = 2

[mysqldump]
default-character-set = utf8mb4

conf.d_three/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
gtid_mode=ON
enforce-gtid-consistency=ON
server_id = 3

[mysqldump]
default-character-set = utf8mb4

그리고 docker compose up -d 로 실행해준다.
우리는 mysql_three 을 마스터로 사용할 예정이다.

1. mysql_three 인스턴스에서 아래 명령어 실행

CREATE USER 'young'@'%' IDENTIFIED WITH mysql_native_password BY 'young';
GRANT REPLICATION SLAVE ON *.* TO 'young'@'%';

2. mysql_one, mysql_two 인스턴스에서 각각 아래 명령어 실행

CHANGE REPLICATION SOURCE TO
          SOURCE_HOST = 'mysql_three',
          SOURCE_PORT = 3308,
          SOURCE_USER = 'young',
          SOURCE_PASSWORD = 'young',
          SOURCE_AUTO_POSITION = 1;

start replica;

show slave status;

MYSQL 설정은 완료되었다.

Spring 설정

application.yml 파일을 아래와 같이 만들어준다.

app:
  datasource:
    one:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/repl
      username: root
      password: root
    two:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3307/repl
      username: root
      password: root
    three:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3308/repl
      username: root
      password: root


spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true

MyDataSourceConfiguration 파일을 만들어준다.

@Configuration
public class MyDataSourceConfiguration {

    @Primary
    @Bean
    public DataSource dataSource() {
        return new LazyConnectionDataSourceProxy(routingDataSource());
    }

    @Bean
    public DataSource routingDataSource() {
        DoubleRouting routing = new DoubleRouting();
        routing.setTargetDataSources(targetDataSources());
        return routing;
    }

    private Map<Object, Object> targetDataSources() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(0, dataSourceOne());
        targetDataSources.put(1, dataSourceTwo());
        targetDataSources.put(2, dataSourceThree());
        return targetDataSources;
    }

    @Bean
    @ConfigurationProperties("app.datasource.one")
    public DataSource dataSourceOne() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ds_2")
    @ConfigurationProperties("app.datasource.two")
    public DataSource dataSourceTwo() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ds_3")
    @ConfigurationProperties("app.datasource.three")
    public DataSource dataSourceThree() {
        return DataSourceBuilder.create().build();
    }
}

DoubleRouting 파일은 다음과 같다.

public class DoubleRouting extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {

        if(TransactionSynchronizationManager.isCurrentTransactionReadOnly()) {
            Random random = new Random();
            Integer randomValue = random.nextInt(2);
            return randomValue;
        }
        return 2;
    }
}

Spring 세팅이 완료되었다.

이렇게 세팅해주게되면 @Transactional(readOnly = true) 서비스 메소드 경우
Slave DB 인 mysql_one, mysql_two 에만 접근하게 된다.

Reference

https://hoing.io/archives/18445
https://velog.io/@max9106/DB-Spring-Replication