Initialize MySQL data in Docker Container

Question

I tried to create Node.js Container and MySQL Container by docker-compose, so I wrote below files.

○tree

.
├── docker-compose.yml
├── mysql
│   ├── Dockerfile
│   ├── conf
│   │   ├── default_authentication.cnf
│   │   └── my.cnf
│   ├── data
│   └── init
│       └── create.sql
└── nodejs
    ├── Dockerfile
    └── app

○./docker-compose.yml

version: '3'
services: 
  webserver:
    build: nodejs
    image: node-express-dev:1.0
    container_name: nodejs
    tty: true
    volumes:
      - ./nodejs/app:/app
    ports:
      - "8080:3000"
  db:
    build: mysql
    container_name: mysql
    ports:
      - "3306:3306"
    volumes:
      - ./mysql/init:/docker-entrypoint-initdb.d
      - dbata:/var/lib/mysql
      - ./mysql/conf:/etc/mysql/conf.d
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_USER: user
      MYSQL_PASSWORD: mysql
volumes:
  dbata:
    driver_opts:
      type: none
      device: /home/.../mysql/data
      o: bind

○./mysql/Dockerfile

FROM mysql:latest

RUN mkdir -p /var/log/mysql/

RUN touch /var/log/mysql/mysqld.log

○./mysql/init/create.sql

SET CHARSET UTF8;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test DEFAULT CHARACTER SET utf8;

use test;

SET CHARACTER_SET_CLIENT = utf8;
SET CHARACTER_SET_CONNECTION = utf8;

DROP TABLE IF EXISTS users;
CREATE TABLE users(
id INT,
name VARCHAR(20),
byear INT
);

INSERT INTO users VALUES (1, 'user1', 1995);
INSERT INTO users VALUES (2, 'user2', 1995);

GRANT ALL ON *.* TO 'user';

Then I use sudo docker-compose up --build -d and access to MySQL by mysql -h 127.0.0.1 -u user -p. And in database test I INSERT one tuple to users table.

I thought ./mysql/init/create.sql is executed whenever I run sudo docker-compose up --build -d, so once I remove containers by sudo docker-compose down and recreate containers by sudo docker-compose up --build -d. But when I checked test.users, there are three tuples(create.sql inserts TWO tuples).

My thought (./mysql/init/create.sql is executed whenever I run sudo docker-compose up --build -d) is wrong?

docker-compose stop won't remove your containers, just stop them :) — Javier Aviles
– Javier Aviles, Commented Jan 31, 2019 at 9:29
1) Init scripts are executed each time the container starts 2) docker-compose down doesn't remove containers — Mike Doe
– Mike Doe, Commented Jan 31, 2019 at 9:50
docs.docker.com/compose/reference/down these docs say containers are removed with docker-compose down, dont they? — Javier Aviles
– Javier Aviles, Commented Jan 31, 2019 at 9:54
@emix When I use docker-compose down output is stopping ~ and removing ~, so I thought container is removed. Is it wrong or how should I do to remove container. — tbt
– tbt, Commented Jan 31, 2019 at 9:55

David Maze · Accepted Answer · 2019-01-31 12:14:32Z

4

The scripts in /docker-entrypoint-initdb.d are only run if the MySQL data directory doesn't exist and the container is being launched to actually run mysqld. See this part of the entrypoint script and "Initializing a fresh instance" in the image documentation.

If you want to re-run it, you need to cause Docker Compose to delete and recreate the data volume. I believe docker-compose rm will do it; simply down and up won't.

(If your application has a more sophisticated migration system, it seems to be fairly common to run the migrations in an entrypoint script, and I feel like the SO examples I've seen recently have all been around the Python Django framework, though beyond the specific command it's a useful generic technique.)

answered Jan 31, 2019 at 12:14

David Maze

165k47 gold badges262 silver badges302 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan Carrasco Quiroz · Accepted Answer · 2022-03-22 18:25:12Z

1

Just add the line

COPY create.sql /docker-entrypoint-initdb.d/

into your Dockerfile.

answered Mar 22, 2022 at 18:25

Ivan Carrasco Quiroz

63511 silver badges9 bronze badges

Collectives™ on Stack Overflow

Initialize MySQL data in Docker Container

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related