Support multiple databases creation

[visay]

Currently I only have the option to create only one database in my docker-compose.yml

db:
  image: mariadb:10.0
  environment:
    MYSQL_DATABASE: mydbname

It would be good if multiple databases is supported (something similar to this merge request in mysql https://github.com/docker-library/mysql/pull/18/files), so I can write:

db:
  image: mariadb:10.0
  environment:
    MYSQL_DATABASES:
      - mydbname1
      - mydbname2

[yosifkit]

Although your yaml syntax would not work, there was discussion of letting MYSQL_DATABASE be a space separated string of databases: docker-library/mysql#18.

[bennythomps]

Keep in mind @visay, best practice would be to have a separate container for each application if that's what you're trying to do. Maybe your one application for some reason needs more than one database, though.

[Crafter6432]

+1

[Vingtoft]

+1

[madalinignisca]

-1

the idea for docker is minimalism. one database per container. you can start multiple containers with different databases.

[robbinalexander]

@madalinignisca Probably ok during development. What about the additional resource required to run another container? And additional configurations needed, especially when I need a mysql cluster?

[madalinignisca]

You can make a simple Dockerfile using this one as the image source and just add your own extra env variables to add more databases to your service. Easy as that. The base one isn't supposed to cover all possible scenarios we think about, but to offer the foundation on which we build the solutions to our problems.

Building it it might take an extra second or less ;)

[amir20]

Reading the README file suggests

Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d.

So adding your own .sql file should be able to create multiple databases.

[alexanikanov]

+1

[qmcree]

Thanks, @amir20, that did it -- total duh moment LOL

[armansd]

Just update startup file under mysql directory:

DROP USER IF EXISTS 'MYSQL_USER';
CREATE USER 'MYSQL_USER'@'%';
CREATE DATABASE IF NOT EXISTS MYSQL_DATABASE;
GRANT ALL ON MYSQL_DATABASE.* TO 'MYSQL_USER'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';
--------------your new testDB----------------------
CREATE DATABASE IF NOT EXISTS testDB;
GRANT ALL ON testDB.* TO 'MYSQL_USER'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';

[sirgalleto]

What's the status of this issue? why is closed?

[visay]

@sirgalleto because you can already do it with our own script if they are mounted into /docker-entrypoint-initdb.d.

See the description at https://hub.docker.com/_/mariadb/ section "Initializing a fresh instance"

[Denis4yk]

To my point of view it could be quite useful and should be reopened

[abagayev]

Created clear example of docker-compose with multiple databases, just use for your purposes:
https://github.com/abagayev/docker-bootstrap-collection/tree/master/mysql-few-databases

[MKagesawa]

Take a look here: https://gist.github.com/MKagesawa/a03892b8c44c015cd991c2c5311f1768
You can pass a shell script creating the dbs

# The official MySQL (https://hub.docker.com/_/mysql/) supports only one MYSQL_DATABASE environment variable.
# By modifying the entrypoint and passing shell script, you can create multiple dbs without having to make a mysql image just for this purpose.
  
version: '3'

services:
  # Some other service connecting to mysql
  
  db:
    image: mysql:5.6
    environment:
      - MYSQL_USER=root
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes
    entrypoint:
      sh -c "
        echo 'CREATE DATABASE IF NOT EXISTS firstDB; CREATE DATABASE IF NOT EXISTS secondDB;' > /docker-entrypoint-initdb.d/init.sql;
        /usr/local/bin/docker-entrypoint.sh --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
      "
    ports:
      - 3306:3306 

[AndresPineros]

@MKagesawa Thanks!!!! Although this alternative is a little hacky. You could use a config file for Swarm or a ConfigMap for Kubernetes in order to inject the init.sql

[ghost]

ive got the same problem, so here my fix: https://hub.docker.com/r/ganjaaa/mariadb

[Dragas]

@amir20 But how many times does entrypoint.d get executed in container's lifecycle? In case I remember correctly it only gets executed when first starting the container. As a result if you were to relate more containers to the same database container (but not same schema) you wouldn't be able to use this approach.

[vulkanosaure]

In my case, the /docker-entrypoint-initdb.d/ only get executed after deleting the volumes (and losing all my datas)
making this approach useless :/

[grooverdan]

In my case, the /docker-entrypoint-initdb.d/ only get executed after deleting the volumes (and losing all my datas)

Why should it be any different? Everything else in init is once only.

making this approach useless :/

Useless for what requirement? Some "I want to create new database on the second startup"? Its sounding weird. Why not just created it using a SQL connection?

If you really want to force this in the container:

command: --init-file=/docker-entrypoint-initdb.d/init.sql if this is needed.

[vulkanosaure]

Why should it be any different? Everything else in init is once only.

Yeah, I guess I realized since then that my approach was wrong

Im trying to have multiple database in a single container, but it makes much more sense to just create them outside of the container startup logic, from a docker exec command or directly through a SQL GUI admin.

(Btw just to clarify, I wasn't calling the whole /docker-entrypoint-initdb.d/ functionality useless, only the approach of using it to answer the problem discussed here)

[Dragas]

If that's really the case, you should be using your application's migrations mechanism instead. For example flyway https://flywaydb.org/ or liquibase https://www.liquibase.org/

…

On Wed, Apr 5, 2023, 10:31 Vincent Huss ***@***.***> wrote: Why should it be any different? Everything else in init is once only. Yeah, I guess I realized since then that my approach was wrong Im trying to have multiple database in a single container, but it makes much more sense to just create them outside of the container startup logic, from a docker exec command or directly through a SQL GUI admin. (Btw just to clarify, I wasn't calling the whole /docker-entrypoint-initdb.d/init.sql functionality useless, only in the context of using it to answer the problem discussed here) — Reply to this email directly, view it on GitHub <#15 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABOMALFETSGFK7G2WCLQL2DW7UNWNANCNFSM4BJ2SMKA> . You are receiving this because you commented.Message ID: ***@***.***>