docker-compose with multiple databases

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.

Solution 4:

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