CODEWITHSUNDEEP
databasedockerdocker-compose
Avanche
Avanche

Reputation: 1820

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?

Upvotes: 101

Views: 154179

Answers (12)

hongqn
hongqn

Reputation: 11

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

Upvotes: 1

Prince
Prince

Reputation: 163

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;

Upvotes: 0

Anu
Anu

Reputation: 3450

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 :

Upvotes: 5

Edward Renton
Edward Renton

Reputation: 51

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.

Upvotes: 0

mahemoff
mahemoff

Reputation: 46509

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.

Upvotes: 132

Jacek Krysztofik
Jacek Krysztofik

Reputation: 1396

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.

Upvotes: 4

Oleksii Zubovsky
Oleksii Zubovsky

Reputation: 61

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

Upvotes: 6

Munish Kapoor
Munish Kapoor

Reputation: 3359

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

Upvotes: 30

Griha Mikhailov
Griha Mikhailov

Reputation: 743

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

Upvotes: 2

david_adler
david_adler

Reputation: 11002

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

Upvotes: 11

Avanche
Avanche

Reputation: 1820

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.

Upvotes: 26

scadge
scadge

Reputation: 9753

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.

Upvotes: 28

Related Questions