Docker-compose with multiple databases

Question

I am trying to understand how to implement docker using docker-compose.yml with two 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 am trying to configure the entire stack set in a single docker file, create 2 databases and import the corresponding sql dumps. Anyone have any suggestions?

Answer 1

You are trying to associate both database containers with the same port - 3306 . Which is fundamentally impossible. You need to change the port mapping for one of the databases, for example mysql stores 3306:3306 , and mysql2 should use 3307:3306 .

Answer 2

As an update for anyone who can learn this.

I solved this by removing:

 MYSQL_DATABASE: dbname 

from docker-compose.yml and adding the appropriate database creation statements directly to the sql file passed to docker-entrypoint-initdb.d .

At this point, sql commands are run as root, so you also need to add an operator to grant the appropriate permissions to the user of the database that you want to use.

Answer 3

Multiple Databases in One Docker Container

The answers in other sections of this page set up a dedicated container for each database, but one MySQL server can host multiple databases. Whether this is necessary is another question , but if you need several databases in one container, here is 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 / position /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 it works?

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

Answer 4

 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