docker-compose with multiple databases

I'm trying to figure out how to implement docker using docker-compose.yml with 2 databases imported from sql dumps.

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:
        - ./sqlserver:/docker-entrypoint-initdb.d
    environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: dbname1
        MYSQL_USER: dbuser
        MYSQL_PASSWORD: dbpass

mysql2:
    extends: mysql
    container_name: sqlserver2
    environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: dbname2
        MYSQL_USER: dbuser
        MYSQL_PASSWORD: dbpass

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

The above returns the following:

Kronos:mybuild avanche$ ./run.sh 
Creating sqlserver
Creating webdata
Creating sqlserver2

ERROR: for mysql2  driver failed programming external connectivity on endpoint sqlserver2 (6cae3dfe7997d3787a8d59a95c1b5164f7431041c1394128c14e5ae8efe647a8): Bind for 0.0.0.0:3306 failed: port is already allocated
Traceback (most recent call last):
  File "<string>", line 3, in <module>
  File "compose/cli/main.py", line 63, in main
AttributeError: 'ProjectError' object has no attribute 'msg'
docker-compose returned -1

Basically, I'm trying to get my whole stack setup in a single docker compose file, create 2 databases and import the respective sql dumps. Anyone have any suggestions?

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.

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.

Just as an update to anyone else who may look into this.

I solved this by removing:

MYSQL_DATABASE: dbname 

from docker-compose.yml and adding the relevant create database statements directly to the sql file being passed to docker-entrypoint-initdb.d.

At that stage, sql commands are performed under root, so you'll also need to add a statement to grant relevant permissions to the database user you want to use.

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

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
  

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

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

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.

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 : 1 2 3