Efficient Multi-Database Setup in Docker Compose Using Initialization Scripts

In Docker Compose setups involving multiple databases, a common issue arises from port conflicts when attempting to bind multiple containers to the same host port. This article addresses this challenge by leveraging initialization scripts to efficiently manage database creation and permissions.

Core Problem Analysis

The error message "Bind for 0.0.0.0:3306 failed: port is already allocated" indicates a port conflict, as both MySQL services in the docker-compose.yml file are mapped to port 3306 on the host. Additionally, relying solely on environment variables like MYSQL_DATABASE can limit flexibility in database initialization, leading to issues when importing SQL dumps.

Solution Based on Best Practices

To resolve this, we combine port remapping with the use of SQL initialization scripts. First, modify the docker-compose.yml to avoid port conflicts, for example by mapping the second database service to a different host port such as 3307. Second, remove the MYSQL_DATABASE environment variable and instead place database creation and user permission statements in a SQL file mounted to the /docker-entrypoint-initdb.d directory.

Implementation Steps

Here is a revised docker-compose.yml file:

version: '3'

services:
  mysql:
    image: mysql:latest
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: dbuser
      MYSQL_PASSWORD: dbpass
    volumes:
      - ./init:/docker-entrypoint-initdb.d

  mysql2:
    image: mysql:latest
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: dbuser
      MYSQL_PASSWORD: dbpass
    volumes:
      - ./init2:/docker-entrypoint-initdb.d

In the init directory, create a SQL file, e.g., init.sql, with the following content:

CREATE DATABASE IF NOT EXISTS dbname1;
CREATE DATABASE IF NOT EXISTS dbname2;
GRANT ALL PRIVILEGES ON dbname1.* TO 'dbuser'@'%';
GRANT ALL PRIVILEGES ON dbname2.* TO 'dbuser'@'%';

This approach ensures that databases are created during container initialization, and port conflicts are avoided by mapping to distinct host ports.

Supplementary Insights

Other answers suggest alternative methods, such as hosting multiple databases in a single container or using different initialization techniques. However, the combined method of port remapping and script-based initialization offers a robust and scalable solution.

In conclusion, by integrating these strategies, developers can efficiently set up multiple databases in Docker Compose without encountering common pitfalls like port allocation errors.