[Mysql + Docker] Docker-compose 에서 sql init 하는 방법

원티드 11월 프리온보딩 백엔드챌린지 사전과제 중

---

기본으로 있던 docker-compose.yml

services:
  mysql:
    image: mysql:8.0.33
    restart: always
    ports:
      - 13306:3306
    volumes:
      - ./mysql/db:/var/lib/mysql
      - ./src/main/resources/initdb:/docker-entrypoint-initdb.d
    command:
      - '--character-set-server=utf8mb4'
      - '--collation-server=utf8mb4_unicode_ci'
      - '--lower_case_table_names=1'
    environment:
      MYSQL_USER: wanted
      MYSQL_PASSWORD: backend
      MYSQL_ROOT_PASSWORD: wanted
      MYSQL_DATABASE: wanted_preonboarding

기존 파일에서 volumes에

- ./mysql/db:/var/lib/mysql

을 추가하고,

 ${DB_INIT_FILE}

이 빈칸을

./src/main/resources/initdb:

으로 지정해준다. 이때 수정한 저 경로는 init sql파일이 위치한곳!

바로여기....

docker-compose up -d 로 실행하면, docker desktop으로 컨테이너가 생성됨을 볼 수 있는데, 해당 컨테이너 터미널에서 mysql에 접속해보자

sh-4.4# mysql -u wanted -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| performance_schema   |
| wanted_preonboarding |
+----------------------+
3 rows in set (0.00 sec)

mysql> use wanted_preonboarding
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> exit
Bye

wanted_preonboarding DB는 만들어져있으나, 테이블이 비어있음.

create_schema.sql에 분명 테이블 생성 쿼리까지 있는데 왜 테이블이 없지?!

CREATE DATABASE IF NOT EXISTS `wanted_preonboarding`;
USE wanted_preonboarding;
CREATE USER IF NOT EXISTS `wanted`@`localhost` IDENTIFIED BY 'backend';
CREATE USER `wanted`@`%` IDENTIFIED BY 'backend';
GRANT all privileges ON `wanted_preonboarding`.* TO `wanted`@`localhost`;
GRANT all privileges ON `wanted_preonboarding`.* TO `wanted`@`%`;

CREATE TABLE `purchase_order`
(
    `order_id`     BINARY(16) default (uuid_to_bin(uuid())) NOT NULL COMMENT '주문번호',
    `name`         VARCHAR(255)                             NOT NULL COMMENT '주문자명',
    `phone_number` VARCHAR(255)                             NOT NULL COMMENT '주문자 휴대전화번호',
    `order_state`  VARCHAR(255)                             NOT NULL COMMENT '주문상태',
    `payment_id`   VARCHAR(255)                             NULL COMMENT '결제정보',
    `total_price`  INT                                      NOT NULL COMMENT '상품 가격 * 주문 수량',
    `created_at`   DATETIME   DEFAULT NOW()                 NOT NULL,
    `updated_at`   DATETIME   DEFAULT NOW()                 NOT NUll,
    PRIMARY KEY (order_id)
);

...

 

컨테이너 실행시에 sql init까지 되어야하는거 아닌가... 일단 mysql에서 나온다.

sh-4.4# cd docker-entrypoint-initdb.d/
sh-4.4# ls
create_schema.sql
sh-4.4# mysql -u root -p < create_schema.sql 
Enter password:

docker-compose.yml에서 initdb 디렉토리가 옮겨간 디렉토리로 이동해서, create_schema.sql 을 실행하고 비밀번호를 입력 (루트계정으로 함)

sh-4.4# mysql -u wanted -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use wanted_preonboarding
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------------+
| Tables_in_wanted_preonboarding |
+--------------------------------+
| card_payment                   |
| order_items                    |
| payment_settlements            |
| payment_transaction            |
| purchase_order                 |
+--------------------------------+
5 rows in set (0.00 sec)

테이블이 생성되어있음!