Create database on docker-compose startup

Question

I would like to create a MySQL database using environment variables in docker-compose.yml file, but it is not working. I have the following code:

# The Database
database:
  image: mysql:5.7
  volumes:
    - dbdata:/var/lib/mysql
  restart: always
  environment:
    MYSQL_ROOT_PASSWORD: secret
    MYSQL_DATABASE: homestead
    MYSQL_USER: root
    MYSQL_PASSWORD: secret
  ports:
    - "33061:3306"

Could someone explain the function of this vars?

Answer 1

There is also an option to provide an init file for mysql container which will be applied each time a container is created.

database:
    image: mysql:5.7
    ports:
        - "33061:3306"
    command: --init-file /data/application/init.sql
    volumes:
        - ./init.sql:/data/application/init.sql
    environment:
        MYSQL_ROOT_USER: root
        MYSQL_ROOT_PASSWORD: secret
        MYSQL_DATABASE: homestead
        MYSQL_USER: root
        MYSQL_PASSWORD: secret

Such file (init.sql) could contain your initial database structure and data - for example:

CREATE DATABASE IF NOT EXISTS dev;
CREATE DATABASE IF NOT EXISTS test;
USE dev;
CREATE TABLE IF NOT EXISTS (...);

Answer 2

The database is probably already initialized and the configuration is stored in /var/lib/mysql. Since you defined a volume for that location the config will survive a restart. The MySQL image will not reconfigure the database over and over again, it only does this once.

volumes:
    - dbdata:/var/lib/mysql

If your database is empty you can reset the database by performing docker-compose down -v where the -v removes the volumes defined in the volume section. See https://docs.docker.com/compose/reference/down/. On the next docker-compose up the MySQL image will start fresh and will initialize the database with the configuration you've provided throug the environment section.

Answer 3

The official MySQL docker image added support for init scripts in their base image. They document the feature under their "Initializing a fresh instance" on the Docker Hub page.

Here are the steps I took to solve creating multiple database and users in the MySQL docker image:

1. Create the init file (the Docker image recognizes .sh, .sql, and .sql.gz files) named setup.sql in the local directory named .docker
2. Place the commands inside setup.sql (see below for an example)
3. Mount the setup script into the directory f within the docker-compose.yaml file (see below for an example)
4. Run docker-compose up -d and MySQL will run the code inside setup.sql

Note: the script will run files alphabetically, so keep that in mind.

Example docker-compose.yaml

version: "3.5"
services:
    mysql:
        image: mysql
        ports:
            - 3306:3306
        environment:
            MYSQL_ROOT_PASSWORD: SomeRootPassword1!
            MYSQL_USER: someuser
            MYSQL_PASSWORD: Password1!
            MYSQL_DATABASE: somedatabase
        volumes:
            - .docker/setup.sql:/docker-entrypoint-initdb.d/setup.sql
            - db_data:/var/lib/mysql
volumes:
    db_data:

Example setup.sql

-- create the databases
CREATE DATABASE IF NOT EXISTS projectone;

-- create the users for each database
CREATE USER 'projectoneuser'@'%' IDENTIFIED BY 'somepassword';
GRANT CREATE, ALTER, INDEX, LOCK TABLES, REFERENCES, UPDATE, DELETE, DROP, SELECT, INSERT ON `projectone`.* TO 'projectoneuser'@'%';

FLUSH PRIVILEGES;

Answer 4

Answering your question ...

One thing I use to do when building a new docker container is understand what the image I pull from does when is builded.

In your docker-compose.yml tou have this

# The Database
database:
  image: mysql:5.7

This is the image you pull from, "mysql:5.7"

Dockerhub is a repository where you can find info of this images.

Do a google search "mysql:5.7 dockerhub"

First result is https://hub.docker.com/_/mysql/

There you have your image 5.7, if you click on 5.7 you have this

https://github.com/docker-library/mysql/blob/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7/Dockerfile

Which is the Dockerfile from the image, you can have a look at interesting things that happen when building the image.

One of this is ENTRYPOINT ["docker-entrypoint.sh"]

This is the file that got executed when image is ready

I you go one level up in the repo you will see this file

https://github.com/docker-library/mysql/tree/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7

The you can see your environment variables being used to create new database etc...

file_env 'MYSQL_DATABASE'
        if [ "$MYSQL_DATABASE" ]; then
            echo "CREATE DATABASE IF NOT EXISTS \`$MYSQL_DATABASE\` ;" | "${mysql[@]}"
            mysql+=( "$MYSQL_DATABASE" )
fi

Answer 5

For version 2 of docker-compose you'll .yml or .yaml can look like this:

version: '2'
volumes:
 dbdata:

services:
 mysql:
  image: mysql:5.7
  container_name: mysql
  volumes:
    - dbdata:/var/lib/mysql
  restart: always
  environment:
    - MYSQL_ROOT_PASSWORD=secret
    - MYSQL_DATABASE=homestead
    - MYSQL_USER=root
    - MYSQL_PASSWORD=secret
  ports:
    - "33061:3306"

start it with docker-compose up -d and check:

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES
a3567fb78d0d        mysql:5.7           "docker-entrypoint..."   2 minutes ago       Up 2 minutes        0.0.0.0:33061->3306/tcp   mysql

docker exec -it a3567fb78d0d bash
root@a3567fb78d0d:/# mysql -u root -p homestead
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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 |
| homestead          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Your volume will be persisted in docker volume nameoffolder_dbdata (/var/lib/docker/volumes/...)

Answer 6

If I understand your question correctly, you want to to have a container with a specific database in it. Like have a MySQL container with CREATE DATABASE mydb, etc. already executed. If so you need to use docker-entrypoint-initdb.d: https://docs.docker.com/samples/library/mysql/#docker-secrets

When the official MySQL container is started for the first time, a new database will be created first. Then it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. So all you need to do is create /docker-entrypoint-initdb.d directory and put your initialisation script there.

Answer 7

The "Initializing a fresh instance" here https://hub.docker.com/_/mysql/ states to copy all .sql,.sh,.sql.gz files into /docker-entrypoint-initdb.d folder. Below is the docker-compose file that I am using for plenty of my projects and all working fine with latest version of MySQL

version: '3.6'
services:   
  mysql:
      environment:
          - MYSQL_DATABASE=root
          - MYSQL_ROOT_PASSWORD=changeme
          - MYSQL_USER=dbuser
          - MYSQL_PASSWORD=changeme
      command: 
         - --table_definition_cache=100
         - --performance_schema=0
         - --default-authentication-plugin=mysql_native_password
         - --innodb_use_native_aio=0
      volumes: 
          - ./init:/docker-entrypoint-initdb.d
      container_name: mysqldb
      image: mysql  

and my init folder has an init.sql file containing the entire SQL data dump that I need to recreate when my container starts.

Below commands are used to limit memory consumed by MySQL container to 100 MB:

         - --table_definition_cache=100
         - --performance_schema=0

NOTE: This doesn't persist your MySQL data if you wish to persist your data then use the below configuration

version: '3.6'
services:   
  mysql:
      environment:
          - MYSQL_DATABASE=root
          - MYSQL_ROOT_PASSWORD=changeme
          - MYSQL_USER=dbuser
          - MYSQL_PASSWORD=changeme
      command: 
         - --table_definition_cache=100
         - --performance_schema=0
         - --default-authentication-plugin=mysql_native_password
         - --innodb_use_native_aio=0
      volumes: 
          - ./init:/docker-entrypoint-initdb.d
          - ./dbdata:/var/lib/mysql
      container_name: mysqldb
      image: mysql  

where dbdata is a folder you need to create on your host system.

Answer 8

This setup implies your database already exists. Otherwise you have to create it with an init script or manually.

Init scripts(any .sh, .sql and .sql.gz files) are expected in /docker-entrypoint-initdb.d/ folder within your docker container.

All you have to do is to simply add this init script as a volume to your docker-compose file.

database:
 image: mysql:latest
  volumes:
    - ./init-script.sql:/docker-entrypoint-initdb.d/init-script.sql

  ...

Your init-script.sql file might look like this:

CREATE DATABASE IF NOT EXISTS some_name;
USE some_name;

Answer 9

From the documentation of the Mysql docker image

MYSQL_DATABASE This variable is optional and allows you to specify the name of a database to be created on image startup. If a user/password was supplied (see below) then that user will be granted superuser access (corresponding to GRANT ALL) to this database.

So, just add it to your docker-compose.yml file:

    db:
        image: mysql:8.0.29
        environment:
            MYSQL_DATABASE: '[your-database-name]'

Answer 10

Keep in mind if you want to docker-compose automatically create database from MYSQL_DATABASE your data folder (dbdata) must be empty!

    # The Database
    database:
      image: mysql:5.7
      volumes:
        - dbdata:/var/lib/mysql   <--- check local dbdata is empty!
      restart: always
      environment:
        MYSQL_ROOT_PASSWORD: secret
        MYSQL_DATABASE: homestead 

    etc...

No need initial scripts, just:

docker-compose down -v

Clean dbdata

docker-compose up

./dbdata/homestead - exists

Answer 11

if you want to create database your docker-compose.yml will looks like if you want to use Dockerfile

version: '3.1'

services:
  php:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - 80:80
    volumes:
      - ./src:/var/www/html/
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    volumes:
      - mysql-data:/var/lib/mysql

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
volumes:
  mysql-data:

your docker-compose.yml will looks like if you want to use your image instead of Dockerfile

version: '3.1'   

services:
  php:
    image: php:7.4-apache
    ports:
      - 80:80
    volumes:
      - ./src:/var/www/html/
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    volumes:
      - mysql-data:/var/lib/mysql

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
volumes:

if you want to store or preserve data of mysql then must remember to add two lines in your docker-compose.yml

volumes:
  - mysql-data:/var/lib/mysql

and

volumes:
  mysql-data:

after that use this command

docker-compose up -d

now your data will persistent and will not be deleted even after using this command

docker-compose down

extra:- but if you want to delete all data then you will use

docker-compose down -v