MySQL 도커 파일 구조

.
├── README.md
├── build.sh
├── docker-compose.yml
├── master
│   ├── conf
│   │   └── mysql.conf.cnf
│   ├── data
│   └── mysql_master.env
└── slave
    ├── conf
    │   └── mysql.conf.cnf
    ├── data
    └── mysql_slave.env

MySQL 세팅 파일

# master/conf/mysql.conf.cnf
[mysqld]
skip-name-resolve
default_authentication_plugin = mysql_native_password

server-id = 1
log_bin = 1
binlog_format = ROW
binlog_do_db = mydb

# slave/conf/mysql.conf.cnf
[mysqld]
skip-name-resolve
default_authentication_plugin = mysql_native_password

server-id = 2
log_bin = 1
binlog_do_db = mydb

# master/mysql_master.env
MYSQL_ROOT_PASSWORD=audrbekd
MYSQL_PORT=4406
MYSQL_USER=mydb_user
MYSQL_PASSWORD=mydb_pwd
MYSQL_DATABASE=temp
MYSQL_LOWER_CASE_TABLE_NAMES=0

# slave/mysql_slave.env
MYSQL_ROOT_PASSWORD=audrbekd
MYSQL_PORT=5506
MYSQL_USER=mydb_slave_user
MYSQL_PASSWORD=mydb_slave_pwd
MYSQL_DATABASE=temp
MYSQL_LOWER_CASE_TABLE_NAMES=0

# ./build.sh
#!/bin/bash

docker-compose down -v
rm -rf ./master/data/*
rm -rf ./slave/data/*
docker-compose build
docker-compose up -d

until docker exec mysql_master sh -c 'export MYSQL_PWD=audrbekd; mysql -u root -e ";"'
do
    echo "Waiting for mysql_master database connection..."
    sleep 4
done

priv_stmt='CREATE USER "mydb_slave_user"@"%" IDENTIFIED BY "mydb_slave_pwd"; GRANT REPLICATION SLAVE ON *.* TO "mydb_slave_user"@"%"; FLUSH PRIVILEGES;'
docker exec mysql_master sh -c "export MYSQL_PWD=audrbekd; mysql -u root -e '$priv_stmt'"

until docker-compose exec mysql_slave sh -c 'export MYSQL_PWD=audrbekd; mysql -u root -e ";"'
do
    echo "Waiting for mysql_slave database connection..."
    sleep 4
done

MS_STATUS=`docker exec mysql_master sh -c 'export MYSQL_PWD=audrbekd; mysql -u root -e "SHOW MASTER STATUS"'`
CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
CURRENT_POS=`echo $MS_STATUS | awk '{print $7}'`

start_slave_stmt="CHANGE MASTER TO MASTER_HOST='mysql_master',MASTER_USER='mydb_slave_user',MASTER_PASSWORD='mydb_slave_pwd',MASTER_LOG_FILE='$CURRENT_LOG',MASTER_LOG_POS=$CURRENT_POS; START SLAVE;"
start_slave_cmd='export MYSQL_PWD=audrbekd; mysql -u root -e "'
start_slave_cmd+="$start_slave_stmt"
start_slave_cmd+='"'
docker exec mysql_slave sh -c "$start_slave_cmd"

docker exec mysql_slave sh -c "export MYSQL_PWD=audrbekd; mysql -u root -e 'SHOW SLAVE STATUS \G'"

MySQL 도커 컴포즈 파일

version: '3'
services:
  mysql_master:
    image: mysql:8.0
    env_file:
      - ./master/mysql_master.env
    container_name: "mysql_master"
    restart: "no"
    ports:
      - 4406:3306
    volumes:
      - ./master/conf/mysql.conf.cnf:/etc/mysql/conf.d/mysql.conf.cnf
      - ./master/data:/var/lib/mysql
    networks:
      - overlay

  mysql_slave:
    image: mysql:8.0
    env_file:
      - ./slave/mysql_slave.env
    container_name: "mysql_slave"
    restart: "no"
    ports:
      - 5506:3306
    depends_on:
      - mysql_master
    volumes:
      - ./slave/conf/mysql.conf.cnf:/etc/mysql/conf.d/mysql.conf.cnf
      - ./slave/data:/var/lib/mysql
    networks:
      - overlay

networks:
  overlay:

MySQL 실행 방법

/bin/bash ./build.sh

Spring yaml 설정

spring:
  application:
    name: service_member
  datasource:   
    master:
      hikari:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:4406/members?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
        username: root
        password: audrbekd
    slave:
      hikari:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:5506/members?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
        username: root
        password: audrbekd
  jpa:
    database: mysql
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        format_sql: true
        show_sql: true