New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Two databases in one MySQL container #616

Closed
jmazurpl opened this Issue Feb 18, 2017 · 10 comments

Comments

Projects
None yet
7 participants
@jmazurpl
Copy link

jmazurpl commented Feb 18, 2017

My project requires two databases to be created in single container. As far as I know I can create single databse in docker-compose.yml. How can I create the second one? Is it possible to do in docker-compose.yml?

My current setup:

mysql:
        build:
            context: ./mysql
            args:
                - MYSQL_DATABASE=db-master
                - MYSQL_USER=myuser
                - MYSQL_PASSWORD=secret
                - MYSQL_ROOT_PASSWORD=root
        volumes:
            - mysql:/var/lib/mysql
        ports:
            - "3306:3306"

I want to create additional database names db-sat, with same owner as the first database. Is it possible?

@francislavoie

This comment has been minimized.

Copy link
Contributor

francislavoie commented Feb 18, 2017

You have two options. You can either make a second container with a different name (it's relatively cheap to run another container), or you can write an SQL script which gets run on startup.

See here https://hub.docker.com/_/mysql/

Initializing a fresh instance
When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

So essentially, you'll want to mount a volume with those SQL files in them, and they'll automatically get executed.

@francislavoie

This comment has been minimized.

Copy link
Contributor

francislavoie commented Feb 18, 2017

Actually, I just realized there's a startup file in this repo (wasn't there last I looked a few months ago) that you can use to set up additional DBs and users. https://github.com/laradock/laradock/blob/master/mysql/startup

You can also modify the mysql Dockerfile to accept additional arguments, such as MYSQL_DATABASE_2 which can get written into the startup script at build-time.

Should do the trick for you.

@jmazurpl

This comment has been minimized.

Copy link

jmazurpl commented Feb 19, 2017

I will go with adding new parametrs to the Dockerfile. Thanks!

@jmazurpl

This comment has been minimized.

Copy link

jmazurpl commented Feb 20, 2017

@francislavoie I modified the files as below.

\docker-compose.yml

mysql:
        build:
            context: ./mysql
            args:
                - MYSQL_DATABASE=db
                - MYSQL_DATABASE_MASTER=db-master
                - MYSQL_USER=user
                - MYSQL_PASSWORD=secret
                - MYSQL_ROOT_PASSWORD=root
        volumes:
            - mysql:/var/lib/mysql
        ports:
            - "3306:3306"

\mysql\Dockerfile

(...)
ARG MYSQL_DATABASE=homestead
ARG MYSQL_DATABASE_MASTER=homestead-master
ARG MYSQL_USER=homestead
ARG MYSQL_PASSWORD=secret
ARG MYSQL_ROOT_PASSWORD=root

ENV MYSQL_DATABASE=$MYSQL_DATABASE
ENV MYSQL_DATABASE_MASTER=$MYSQL_DATABASE_MASTER
ENV MYSQL_USER=$MYSQL_USER
ENV MYSQL_PASSWORD=$MYSQL_PASSWORD
ENV MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD

RUN sed -i 's/MYSQL_DATABASE/'$MYSQL_DATABASE'/g' /etc/mysql/startup && \
    sed -i 's/MYSQL_DATABASE_MASTER/'$MYSQL_DATABASE_MASTER'/g' /etc/mysql/startup && \
    sed -i 's/MYSQL_USER/'$MYSQL_USER'/g' /etc/mysql/startup && \
    sed -i 's/MYSQL_PASSWORD/'$MYSQL_PASSWORD'/g' /etc/mysql/startup
(...)

\mysql\startup

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';
CREATE DATABASE IF NOT EXISTS MYSQL_DATABASE_MASTER; 
GRANT ALL ON MYSQL_DATABASE_MASTER.* TO 'MYSQL_USER'@'%' IDENTIFIED BY 'MYSQL_PASSWORD';

But unfortunately after these changes and creating and running this container I'm not able to login into mysql. I get the Access Denied error from mysql.

Is there any way to debug what is going wrong during container creation? I assume that there is an error while creating the db users but I don't have any idea what is the problem.

@francislavoie

This comment has been minimized.

Copy link
Contributor

francislavoie commented Feb 20, 2017

You should be able to do

docker-compose exec mysql sh -c 'export MYSQL_PWD="$MYSQL_ROOT_PASSWORD"; mysql'

to get a mysql shell as the root user and debug from there. You example looks OK to me. I'm no MySQL expert, I probably won't be able to help much with that part, sorry 😞

@infacq

This comment has been minimized.

Copy link

infacq commented Feb 22, 2017

how do I dump data to existing using bash

@jmazurpl

This comment has been minimized.

Copy link

jmazurpl commented Feb 22, 2017

I figured out that problem is not connected with my changes to the scripts but even if I pull clean laradock from git and i change its folder name from "laradock" to anything else i always get the Access denied for user 'root'@'172.18.0.3' (using password: YES).

Since I have separate laradock instances for each of my projects I would like to distinguish between the containers using different folder name. Does anybody knows why it is like this? Is it required to stick with "laradock" folder name?

@francislavoie

This comment has been minimized.

Copy link
Contributor

francislavoie commented Feb 22, 2017

@jmazurpl See here: docker/compose#745
There's currently no proper way to do this persistently, but essentially you need to either use -p in all your docker-compose commands, or use a .env file alongside docker-compose.yml, and set the COMPOSE_PROJECT_NAME environment variable.

Specifically, edit this file https://github.com/laradock/laradock/blob/master/.env and add that variable.

Also, you should be using mysql, i.e. the container name, as the domain to connect to your container from inside of it if you aren't already. Compose guarantees that the names of the containers are set up in the hosts files of each container in the stack to point to the correct container.

Some reading:

@abagayev

This comment has been minimized.

Copy link

abagayev commented Jun 23, 2018

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

This comment has been minimized.

Copy link

MKagesawa commented Aug 21, 2018

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 
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment