MySQL 분산처리(Replication with docker)

Hyunho·2023년 8월 30일

MySQL replication

데이터베이스 쓰기와 읽기 전용을 분리하기 위해 테스트로 local환경에 Dokcer를 활용하여 구성한 내용입니다.

환경 정보
MAC OS
Mysql 8.0.33
Spring boot 3.1.0

container를 실행 시키기 위한 준비

Diretory 구조는 아래와 같이 구성 하였으며, docker-compose up -d 명령어를 실행하여 contailer를 실행 하였습니다.

MacBook-Pro ~/sw/mysql-replication tree
.
├── docker-compose.yml
├── master
│   ├── Dockerfile
│   └── my.cnf
└── slave
    ├── Dockerfile
    └── my.cnf

MacBook-Pro ~/sw/mysql-replication docker-compose up -d

docker-compose.yml

version: "3"
services:
  db-write:
    build: 
      context: ./
      dockerfile: master/Dockerfile
    restart: always
    environment:
      MYSQL_DATABASE: 'reservation'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'password'
    ports:
      - '3321:3306'
    # Where our data will be persisted
    volumes:
      - master:/var/lib/mysql
      - master:/var/lib/mysql-files
    networks:
      - net-mysql
  
  db-read:
    build: 
      context: ./
      dockerfile: slave/Dockerfile
    restart: always
    environment:
      MYSQL_DATABASE: 'reservation'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'password'
    ports:
      - '3322:3306'
    # Where our data will be persisted
    volumes:
      - slave:/var/lib/mysql
      - slave:/var/lib/mysql-files
    networks:
      - net-mysql
  
# Names our volume
volumes:
  master:
  slave: 

networks: 
  net-mysql:
    driver: bridge

master(write)용 Dockerfile

FROM mysql:8.0.33
ADD ./master/my.cnf /etc/mysql/my.cnf

master(write)용 설정 파일

[mysqld]
log_bin = mysql-bin //로그 파일명
server_id = 10 //서버 식별번호
binlog_do_db=reservation // replication DB명
default_authentication_plugin=mysql_native_password //인증 암호화 플러그인

slave(read)용 Dockerfile

FROM mysql:8.0.33
ADD ./slave/my.cnf /etc/mysql/my.cnf

slave(read)용 설정 파일

[mysqld]
log_bin = mysql-bin
server_id = 11 //서버 식별번호 (slave 서버가 여러대일 경우 각각 다른 번호를 할당해야 합니다.)
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 'ON' //Master(마스터)로 부터 수신한 변경 내용에 대해서 자신(Replica/Slave)의 바이너리 로그에 기록
read_only = 'ON'
default_authentication_plugin=mysql_native_password

mysql에는 read_only 설정 이 있습니다.
위에서 설정한 read_only는 클라이언트 접속 시 읽기만 허용되고 쓰기는 허용하지 않는 설정입니다.

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.02 sec)

DB설정

사용자 권한 추가

mysql> create user 'replication_user'@'%' identified by '1234';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Query OK, 0 rows affected (0.00 sec)

master(write) db 정보확인
아래 file명(mysql-bin.000004)과 position(20771) 번호를 기억 해주셔야 합니다.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |    20771 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master container IP확인

MacBook-Pro  ~/sw/mysql-replication  docker network ls
NETWORK ID     NAME                              DRIVER    SCOPE
2ac0b0bc1604   bridge                            bridge    local
da4ffaac9ba9   docker-elk_elk                    bridge    local
dc94a09ac0b2   host                              host      local
f012581bc1e0   replica_net-mysql                 bridge    local

master(replica-db-write-1) IPv4Address 확인

MacBook-Pro  ~  docker inspect replica_net-mysql
[
    {
        "Name": "replica_net-mysql",
        //... 생략
        "Containers": {
            "7b51620fe77581e3fd0bc98ef1f6c5eff083db4db5e418a6c264dbc30b37cebe": {
                "Name": "replica-db-write-1",
                "EndpointID": "20c20722b0b1e789f53d64219b00ded3a4c5b890288cee34aa6221570cccc8e3",
                "IPv4Address": "172.26.0.2/16",
                "IPv6Address": ""
            },
            "8abe6315b021f08c6df880c729e40069832c10c7a469fb9337adf6e8beee2d7d": {
                "Name": "replica-db-read-1",
                "EndpointID": "db817000edd436bcd278e04fa9d07d691c6fe3f399443c4d06fae9852cf19db1",
                "IPv4Address": "172.26.0.3/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {
            "com.docker.compose.network": "net-mysql",
            "com.docker.compose.project": "replica",
            "com.docker.compose.version": "2.12.1"
        }
    }
]

위에서 정보를 확인 하였으면 slave(read)용 DB에 아래와 같이 명령어를 입력해줍니다.

CHANGE MASTER TO MASTER_HOST='172.26.0.2', 
MASTER_USER='replication_user', 
MASTER_PASSWORD='1234', 
MASTER_LOG_FILE='mysql-bin.000004', 
MASTER_LOG_POS=20771, 
GET_MASTER_PUBLIC_KEY=1;

MASTER_USER, MASTER_PASSWORD 는 위에서 생성한 계정의 이름과 비밀번호 이며,
MASTER_LOG_FILE, MASTER_LOG_POS는 master db에서 조회한 정보입니다.

master_host : 마스터의 IP 주소 혹은 hostname
master_user : 슬레이브가 서버에 접속할 때 인증하는 유저 정보.
master_password : 서버에 접속할 때 인증하는 사용자 패스워드
master_log_file : bin-log 파일 번호
master_log_pos : position 값

위 쿼리를 싱행 후 연동 확인을 해줍니다.
확인할 값은 Slave_IO_Running, Slave_SQL_Running 이며 두 값 모두 YES로 표시가 되어야 합니다.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.26.0.2
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004 //master db의 bin-log파일 이름
          Read_Master_Log_Pos: 20353 //master position
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 20569
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
                         ...
        Seconds_Behind_Master: 0 // slave db의 쿼리가 지연되는지 나타내는 값(0이어야 정상)

       ...

Spring Boot에서 DB master(write), slave(read) 분기 처리

application.yml파일 설정

spring:
  datasource:
    master:
      hikari:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3321/reservation
        read-only: false
        username: root
        password: password
    slave:
      hikari:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3322/reservation
        read-only: true
        username: root
        password: password

DataSource Bean 등록

import com.zaxxer.hikari.HikariDataSource;
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 javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

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

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

AbstractRoutingDataSource 구현

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

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return (TransactionSynchronizationManager.isCurrentTransactionReadOnly()) ? "slave" : "master";
    }
}

connection 분기 처리
spring은 transaction에 진입하는 순간 설정된 datasource의 connection을 가져옵니다.
이러한 점 때문에 Multi datasource 환경에서 transaction에 진입한 후 datasource를 결정할때 이미 transaction 진입 시점에 datasource가 결정되어 분기가 불가능합니다.
이떄문에 master, slave 분기가 되지 않아 이러한 점을 해결 하기 위해 LazyConnectionDataSourceProxy를 사용해 필요한 시점에 connection 점유

public class LazyConnectionDataSourceProxy extends DelegatingDataSource {
    ...

	/**
	 * Return a Connection handle that lazily fetches an actual JDBC Connection
	 * when asked for a Statement (or PreparedStatement or CallableStatement).
	 * <p>The returned Connection handle implements the ConnectionProxy interface,
	 * allowing to retrieve the underlying target Connection.
	 * @return a lazy Connection handle
	 * @see ConnectionProxy#getTargetConnection()
	 */
	@Override
	public Connection getConnection() throws SQLException {
		return (Connection) Proxy.newProxyInstance(
				ConnectionProxy.class.getClassLoader(),
				new Class<?>[] {ConnectionProxy.class},
				new LazyConnectionInvocationHandler());
	}
}

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.jdbc.datasource.LazyConnectionDataSourceProxy;

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

@Configuration
public class DataSourceConfig {

    //...생략

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

    @Bean
    public DataSource routingDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slaveDataSource") DataSource slaveDataSource)
    {
        RoutingDataSource routingDataSourceImpl = new RoutingDataSource();
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put("master", masterDataSource);
        targetDataSource.put("slave", slaveDataSource);
        routingDataSourceImpl.setTargetDataSources(targetDataSource);
        routingDataSourceImpl.setDefaultTargetDataSource(masterDataSource);
        return routingDataSourceImpl;
    }
}

docker container에서 Mysql query 로그 확인하는 방법

테스트 진행하기전에 실제 데이터가 쓰기와 읽기용에 따라 master db와 slave db에 각각 query요청을 제대로 하고 있는지 확인하기 위해 위한 docker환경 에서 로그를 보는 방법 입니다.

general_log 설정 확인 및 변경
general_log 값을 ON 으로 변경
(DB 쿼리 실행 내역을 기록하여 확인 할 수 있도록 하는 설정 입니다.)

mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /var/lib/mysql/7b51620fe775.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)

mysql> set global general_log='ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /var/lib/mysql/7b51620fe775.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)

참고
innodb_print_all_deadlocks 는 모든 교착 상태에 대한 정보를 오류로그에 출력하는 설정입니다.

mysql> SHOW VARIABLES LIKE "innodb_print_all_deadlocks%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_print_all_deadlocks='ON';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE "innodb_print_all_deadlocks%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

테스트

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    @Transactional //등록
    public SavedUserValue join(CreateUser createUser) {
        if (userRepository.exists(createUser.userId())) {
            throw new IllegalArgumentException(ErrorCode.ALREADY_REGISTERED_USER.name());
        }

        User savedUser = userRepository.save(createUser.toEntity());

        return new SavedUserValue(savedUser);
    }

    @Transactional(readOnly = true)  //조회
    public SearchUserResult searchBy(Long id) {
        User user = userRepository.findById(id)
            .orElseThrow(() -> new IllegalArgumentException(ErrorCode.USER_NOT_FOUND.name()));

        return new SearchUserResult(user);
    }
}

위 두 로직을 실행 한 결과입니다.
(왼쪽 master, 오른쪽 slave)
회원 등록 시 쓰기를 할때 master서버에 insert쿼리가 실행되며, 회원 조회 시 읽기전용인 slave서버에서 select쿼리가 실행되는걸 확인 할 수 있습니다.
Untitled

Hyunho

hyunho

이전 포스트

JdbcTemplate batchUpdate()를 활용한 Bulk Insert

다음 포스트