docker-compose with multiple databases

Multiple databases in a single Docker container

The answers elsewhere on this page set up a dedicated container for each database, but a single MySQL server is capable of hosting multiple databases. Whether you should is a different question, but if you want multiple databases in a single container, here's an example.

docker-compose.yml:

version: '3'

volumes:
  db:
    driver: local

services:
  db:
    image: mysql:5.7
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
        - ./docker/provision/mysql/init:/docker-entrypoint-initdb.d
    environment:
      MYSQL_ROOT_PASSWORD: local

docker/provision/mysql/init/01-databases.sql:

# create databases
CREATE DATABASE IF NOT EXISTS `primary`;
CREATE DATABASE IF NOT EXISTS `secondary`;

# create root user and grant rights
CREATE USER 'root'@'localhost' IDENTIFIED BY 'local';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

How does this work?

This works because the MySQL Docker project has an entrypoint script that will run through all files in the /docker-entrypoint-initdb.d folder, if it exists. This is useful for setting up databases and initializing their schema and data. In docker-compose, we're using volumes to map that virtual folder to a folder on the host system.

After struggling, 3 days found this Article to solve this issue saved my life

File Structure

Project
├── docker-compose.yml (File)
├── init (Directory)
│   ├── 01.sql (File)

then point init directory inside the volumes in the docker-compose.yml file as following

volumes: 
  - ./init:/docker-entrypoint-initdb.d

01.sql

CREATE DATABASE IF NOT EXISTS `test`;
GRANT ALL ON `test`.* TO 'user'@'%';

docker-compose.yml

version: '3.6'
    
services: 
    # MySQL
    db:
        image: mysql
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
            MYSQL_ROOT_PASSWORD: root
            MYSQL_DATABASE: mydb
            MYSQL_USER: user
            MYSQL_PASSWORD: user

        volumes: 
            - ./init:/docker-entrypoint-initdb.d
    
    adminer:
        image: adminer
        restart: always
        ports:
            - 8080:8080

You're trying to bind both database containers to the same port - 3306. Which is essentially impossible. You need to change the port-mapping for one of the databases, for example mysql keeps 3306:3306, and mysql2 should use 3307:3306.

version: '3'
services:
  mysql1:
    image: mysql:5.6.26
    environment:
     MYSQL_ROOT_PASSWORD: asdf
     MYSQL_USER: asdf
     MYSQL_HOST: localhost
     MYSQL_PASSWORD: asdf
     MYSQL_DATABASE: asdf
    ports:
      - "3307:3306"
  mysql2:
    image: mysql:5.6.26
    environment:
     MYSQL_ROOT_PASSWORD: asdf
     MYSQL_USER: asdf
     MYSQL_HOST: localhost
     MYSQL_PASSWORD: asdf
     MYSQL_DATABASE: asdf
    ports:
      - "3308:3306"

• After docker-compose up

• Connect to mysql1

  mysql -h localhost -uasdf -P 3307 -pasdf asdf --protocol=tcp -D asdf
  

• Connect to mysql2

  mysql -h localhost -uasdf -P 3308 -pasdf asdf --protocol=tcp -D asdf
  

Run multiple services in a container

From my point of view none of the answers above are complete. In original question, I see an attempt to start two containers. Is it good or bad to run two database containers? Docker documentation says

It is generally recommended that you separate areas of concern by using one service per container. That service may fork into multiple processes (for example, Apache web server starts multiple worker processes). It’s ok to have multiple processes, but to get the most benefit out of Docker, avoid one container being responsible for multiple aspects of your overall application. You can connect multiple containers using user-defined networks and shared volumes.

It seems like logically correct to apply the same rule to databases as well. So, there is nothing wrong to use database container per schema. It gives flexibility.

Below I added networks and fixed databases initialisation in more elegant way. By extending 'mysql' container the same 3306 port is exposed as external to provide access to mysql database from console or external applications. That port must be unique. To fix this error I added port mapping for mysql2 container as well. These ports provides access to databases from console or external applications. If databases access required from app container only, these mappings are not needed. Docker has its own internal DNS. That's why database connections for app container can be configured by using container names as domain names. So, database connection strings will be dbuser@mysql:3306/dbname1 and dbuser@mysql2:3306/dbname2.

version: '3'
services:

 httpd:
    container_name: webserver
    build: ./webserver/
    ports:
        - 80:80
    links:
        - mysql
        - mysql2
    volumes_from:
        - app
   
 mysql:
    container_name: sqlserver
    image: mysql:latest
    ports:
        - 3306:3306
    volumes:
        - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql
    environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: dbname1
        MYSQL_USER: dbuser
        MYSQL_PASSWORD: dbpass
    networks:
      - app-network
   
 mysql2:
    extends: mysql
    container_name: sqlserver2
    ports:
        - 3307:3306
    volumes:
        - ./dump2.sql:/docker-entrypoint-initdb.d/dump.sql
    environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: dbname2
        MYSQL_USER: dbuser
        MYSQL_PASSWORD: dbpass
   networks:
      - app2-network

 app:
    container_name: webdata
    image: php:latest
    volumes:
        - ../php:/var/www/html
    command: "true"
    networks:
      - app-network
      - app2-network

#Docker Networks
networks:
  app-network:
  app2-network:
    driver: bridge
#Volumes
volumes:
  dbdata:
    driver: local

you can have multiple databases in a single container service:

docker-compose.yml

version: '3.7'
services:
  postgres-db:
    restart: always
    image: postgres:latest
    volumes:
      - db-volume:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    env_file:
      - ./db-prod.env

db-prod.env

POSTGRES_USER=myusername
POSTGRES_PASSWORD=mypassword
POSTGRES_MULTIPLE_DATABASES=db1,db2

Sources :

• GitHub | Docker PostgresSQL Multiple Databases
• Dev.To | Multiple Postgres Databases in a single Docker Container
• Stack Overflow | Multiple databases in docker and docker-compose

When you study the /usr/local/bin/docker-entrypoint.sh of the mysql image, you will notice different modes of treating *.sh files in your mapped docker-entrypoint-initdb.d (look for HERE markers):

        case "$f" in
            *.sh)
                if [ -x "$f" ]; then
                    "$f"
                else
                    . "$f" # HERE!!
                fi
                ;;
            *.sql)    mysql_note "$0: running $f"; docker_process_sql < "$f"; echo ;; # HERE!!
            # ...
        esac

With this you can create a 000-databases.sh without the executable bit, containing the following:

echo "
CREATE DATABASE IF NOT EXISTS \`other-database\`;
GRANT ALL PRIVILEGES ON \`other-database\`.* TO '$MYSQL_USER'@'%' IDENTIFIED BY '$MYSQL_PASSWORD';
" | docker_process_sql

The key utility here is docker_process_sql, which handles SQL scripts. The MYSQL_* variables come from your docker-compose.yml environment config.

For me, helped next:

  db:
    image: postgres
    container_name: mgt_db
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: mgtdb
      POSTGRES_HOST_AUTH_METHOD: trust
    ports:
      - 5432:5432
  db2:
    image: postgres
    container_name: pay_vault_db 
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: payvaultdb
      POSTGRES_HOST_AUTH_METHOD: trust
    ports:
      - 5433:5432

Some answers mentioned to place .sql files in the /docker-entrypoint-initdb.d directory in order to create the extra databases. However, this approach requires the database names to be hardcoded in the sql file.

Fortunately, upon examining the source code of the entrypoint.sh script in both the mysql and postgresql image, it was discovered that files under the /docker-entrypoint-initdb.d directory can be not only sql files, but also shell scripts. Therefore, .sh files can be placed in this directory. As a result, environment variables can be used in the script without having to hardcode them.

Additionally, if the script file is not executable, it will be sourced instead of invoked. This means that functions defined in the entrypoint.sh script can be reused directly!

So here comes the solution:

docker-compose.yml

mysql:
  image: "mysql:8.0"
  command: --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
  environment:
    MYSQL_ROOT_PASSWORD: root
    MYSQL_ROOT_HOST: "%"
    MYSQL_DATABASE: db1
    MORE_DATABASES: db2 db3
  volumes:
    - ./docker/provision/mysql/init/:/docker-entrypoint-initdb.d

postgres:
  image: "postgres:15"
  environment:
    POSTGRES_USER: dev
    POSTGRES_PASSWORD: dev
    POSTGRES_DB: db1
    MORE_DATABASES: db2 db3
  volumes:
    - ./docker/provision/postgres/init/:/docker-entrypoint-initdb.d

docker/provision/mysql/init/01-create-more-databases.sh

# Keep this file plain. Do not `chmod +x` to make it executable.

create_more_databases() {
    local databases=( $MORE_DATABASES )
    local db
    for db in "${databases[@]}"; do
        mysql_note "Creating database ${db}"
        docker_process_sql --database=mysql <<<"CREATE DATABASE IF NOT EXISTS \`$db\` ;"

        if [ -n "$MYSQL_USER" ] && [ -n "$MYSQL_PASSWORD" ]; then
            mysql_note "Giving user ${MYSQL_USER} access to schema ${db}"
            docker_process_sql --database=mysql <<<"GRANT ALL ON \`${db//_/\\_}\`.* TO '$MYSQL_USER'@'%' ;"
        
        fi
    done
}

if [ -n "$MORE_DATABASES" ]; then
    create_more_databases
fi

docker/provision/postgres/init/01-create-more-databases.sh

# Keep this file plain. Do not `chmod +x` to make it executable.

create_more_databases() {
    local databases=( $MORE_DATABASES )
    local db
    for db in "${databases[@]}"; do
        POSTGRES_DB="$db" docker_setup_db
    done
}

if [ -n "$MORE_DATABASES" ]; then
    create_more_databases
fi

Found the solution below, which creates two docker containers with a DB in each.

docker-compose.yml:

volumes:
  db-vol:
  hist-vol:

services:
  pgsql:
    image: postgres:12.11
    container_name: live-db-container
    restart: always
    environment:
      POSTGRES_PASSWORD: pwd
      POSTGRES_USER: usr
      POSTGRES_DB: live-db
    volumes:
      - db-vol:/var/lib/postgresql/data
    ports:
      - 5432:5432

  pgsql-hist:
    image: postgres:12.11
    container_name: hist-db-container
    restart: always
    environment:
      POSTGRES_PASSWORD: pwd
      POSTGRES_USER: usr
      POSTGRES_DB: hist-db
    volumes:
      - hist-vol:/var/lib/postgresql/histdata
    ports:
      - 3306:5432

  app:
    build:
      context: .
      dockerfile: ./Dockerfile
    depends_on:
      - pgsql
      - pgsql-hist
    ports:
      - 5000:5000
    volumes:
      - ./:/app

networks:
  talk: {}

Then when creating engines to initialise and connect to the databases, use the following connection strings:

live_url = "postgresql+psycopg2://usr:pwd@live-db-container:5432/live-db"
hist_url = "postgresql+psycopg2://usr:pwd@hist-db-container:5432/hist-db"

Note that the ports specified are the same (5432) in the urls! They are mapped differently in the .yml file though (5432 --> 5432) and (3306 --> 5432). Using sqlalchemy_utils' create_engine on these urls should work.

Here's my working piece of code.

  mysql:
    image: mysql:5.6
    container_name: db-service               
    restart: on-failure
    ports: 
      - "3306:3306"
    env_file:
       - 'docker.env'    
    networks:
      - ektamart-tier
    volumes:
      - data:/var/lib/mysql   
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    healthcheck:
      test: mysqladmin ping -h 127.0.0.1 -u $$MYSQL_USER --password=$$MYSQL_PASSWORD
      interval: 30s
      timeout: 30s
      retries: 3  

Structure:

- docker-compose.yml
- docker-entrypoint-initdb.d
   |- init.sql 

Code inside init.sql

CREATE DATABASE IF NOT EXISTS user_service_db;

CREATE DATABASE IF NOT EXISTS order_service_db;