How to create database in database docker container?

Question

I'm new in docker, so cant understand - if I want to build container of mysql/postgresql/clickhouse etc - how to create database and schema of database/table? Maybe in Dockerfile or i can do it from docker-compose.yml?

I mean, that I dont know when and where to use CREATE DATABASE; CREATE TABLE ...; queries if I use docker containers of popular databases

Answer 1

You can use both docker and docker-compose. For example with docker compose.

Create a file called docker-compose.yml like:

version: '3'
services:
  db:
    image: percona:5.7
    container_name: whatever_you_want
    environment:
      - MYSQL_DATABASE=${DATABASE}
      - MYSQL_ROOT_PASSWORD=${ROOT_PASSWORD}
      - MYSQL_USER=${USER}
      - MYSQL_PASSWORD=${PASSWORD}
    volumes:
      - ./data:/docker-entrypoint-initdb.d
    ports:
      - "3306:3306"

Additionally you need a file under ./data with whatever SQL commands you want to run and and .env file where you define the environmental variables I used in the docker-compose.yml file above like: ${DATABASE}

Your .env file:

# MySQL
DATABASE=db_name_here
ROOT_USER=root
ROOT_PASSWORD=root
USER=dev
PASSWORD=dev

Your file with SQL commands to execute ./data/init.sql (you can name the file whatever you want)

CREATE DATABASE 'whatever';
DROP DATABASE 'whatever';
-- you can do whatever you want here

This file will be executed each time you do:

docker-compose up -d db

Answer 2

At first you need to create docker a image for your db server, or use an already existing image.

Bellow is an example of mysql docker image.

version: "3"

services:

    ****************

    mysql:
        container_name: mysql
        image: mysql:5.7
        restart: on-failure
        environment:
            - MYSQL_DATABASE=YOUR_DB_NAME
            - MYSQL_ROOT_PASSWORD=YOUR_ROOT_USER_PASSWORD
            - MYSQL_USER=YOUR_USER
            - MYSQL_PASSWORD=YOUR_USER_PASSWORD
        ports:
            - "33060:3306"
        volumes:
            - "./data/db/mysql:/var/lib/mysql"

Let's describe some sections:

volumes:
   - "./data/db/mysql:/var/lib/mysql"

This is like "mounting" container's /var/lib/mysql to system's ./data/db/mysql. So your data will be on your system drive, because in debian the default path to MySQL data is /var/lib/mysql.

ports:
   - "33060:3306"

This will map port 3306 from container to system's 33060 port, to avoid conflicts if you have installed MySQL server on system as well.

environment:
   - MYSQL_DATABASE=YOUR_DB_NAME
   - MYSQL_ROOT_PASSWORD=YOUR_ROOT_USER_PASSWORD
   - MYSQL_USER=YOUR_USER
   - MYSQL_PASSWORD=YOUR_USER_PASSWORD

This will create a database with the defined parameters: name, root password, ..., or if a database already exists it will try to access with the defined credentials. Functionality to check/create database is already defined in the image.

If you want to define your own functionality you can define your image (e.g. dockerfile: ./Dockerfile instead of image: mysql:5.7). Dockerfile can be something like this:

FROM mysql:5.7

ARG MYSQL_DATABASE
ARG MYSQL_USER
ARG MYSQL_PASSWORD
ARG MYSQL_ROOT_PASSWORD

ENV MYSQL_DATABASE=${MYSQL_DATABASE}
ENV MYSQL_USER=${MYSQL_USER}
ENV MYSQL_PASSWORD=${MYSQL_PASSWORD}
ENV MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}

# copy predefined config file
COPY configs/default.cnf /etc/mysql/conf.d/

# To be sure that MySQL will not ignore configs
RUN chmod og-w /etc/mysql/conf.d/default.cnf

# DO SOMETHING ELSE YOU WANT

EXPOSE 3306

CMD ["mysqld"]

So you can build and up your container with command docker-compose up -d --build