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

Answer 3

Here is an example I used to initialise SQL Server 2017 database using container. https://www.handsonarchitect.com/2018/01/build-custom-sql-server-2017-linux.html

The trick is to use a shell script to run which will invoke the database initialisation script. You might have to wait for few seconds for the database engine service to start before executing the initialisation script.