Setting up MySQL and importing dump within Dockerfile

by

I’m trying to setup a Dockerfile for my LAMP project, but i’m having a few problems when starting MySQL. I have the folowing lines on my Dockerfile:

VOLUME ["/etc/mysql", "/var/lib/mysql"]
ADD dump.sql /tmp/dump.sql
RUN /usr/bin/mysqld_safe & sleep 5s
RUN mysql -u root -e "CREATE DATABASE mydb"
RUN mysql -u root mydb < /tmp/dump.sql

But I keep getting this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

Any ideas on how to setup database creation and dump import during a Dockerfile build?

Contents hide
Answers:
Method 1
Method 2
Solution 1: use a one-line RUN
Solution 2: use a script
Method 3
Method 4
Method 5
Method 6
Method 7
Method 8

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The latest version of the official mysql docker image allows you to import data on startup. Here is my docker-compose.yml

data:
  build: docker/data/.
mysql:
  image: mysql
  ports:
    - "3307:3306"
  environment:
    MYSQL_ROOT_PASSWORD: 1234
  volumes:
    - ./docker/data:/docker-entrypoint-initdb.d
  volumes_from:
    - data

Here, I have my data-dump.sql under docker/data which is relative to the folder the docker-compose is running from. I am mounting that sql file into this directory /docker-entrypoint-initdb.d on the container.

If you are interested to see how this works, have a look at their docker-entrypoint.sh in GitHub. They have added this block to allow importing data

    echo
    for f in /docker-entrypoint-initdb.d/*; do
        case "$f" in
            *.sh)  echo "$0: running $f"; . "$f" ;;
            *.sql) echo "$0: running $f"; "${mysql[@]}" < "$f" && echo ;;
            *)     echo "$0: ignoring $f" ;;
        esac
        echo
    done

An additional note, if you want the data to be persisted even after the mysql container is stopped and removed, you need to have a separate data container as you see in the docker-compose.yml. The contents of the data container Dockerfile are very simple.

FROM n3ziniuka5/ubuntu-oracle-jdk:14.04-JDK8

VOLUME /var/lib/mysql

CMD ["true"]

The data container doesn’t even have to be in start state for persistence.

Method 2

Each RUN instruction in a Dockerfile is executed in a different layer (as explained in the documentation of RUN).

In your Dockerfile, you have three RUN instructions. The problem is that MySQL server is only started in the first. In the others, no MySQL are running, that is why you get your connection error with mysql client.

To solve this problem you have 2 solutions.

Solution 1: use a one-line RUN

RUN /bin/bash -c "/usr/bin/mysqld_safe --skip-grant-tables &" && 
  sleep 5 && 
  mysql -u root -e "CREATE DATABASE mydb" && 
  mysql -u root mydb < /tmp/dump.sql

Solution 2: use a script

Create an executable script init_db.sh:

#!/bin/bash
/usr/bin/mysqld_safe --skip-grant-tables &
sleep 5
mysql -u root -e "CREATE DATABASE mydb"
mysql -u root mydb < /tmp/dump.sql

Add these lines to your Dockerfile:

ADD init_db.sh /tmp/init_db.sh
RUN /tmp/init_db.sh

Method 3

What I did was download my sql dump in a “db-dump” folder, and mounted it:

mysql:
 image: mysql:5.6
 environment:
   MYSQL_ROOT_PASSWORD: pass
 ports:
   - 3306:3306
 volumes:
   - ./db-dump:/docker-entrypoint-initdb.d

When I run docker-compose up for the first time, the dump is restored in the db.

Method 4

Here is a working version using v3 of docker-compose.yml. The key is the volumes directive:

mysql:
  image: mysql:5.6
  ports:
    - "3306:3306"
  environment:
    MYSQL_ROOT_PASSWORD: root
    MYSQL_USER: theusername
    MYSQL_PASSWORD: thepw
    MYSQL_DATABASE: mydb
  volumes:
    - ./data:/docker-entrypoint-initdb.d

In the directory that I have my docker-compose.yml I have a data dir that contains .sql dump files. This is nice because you can have a .sql dump file per table.

I simply run docker-compose up and I’m good to go. Data automatically persists between stops. If you want remove the data and “suck in” new .sql files run docker-compose down then docker-compose up.

If anyone knows how to get the mysql docker to re-process files in /docker-entrypoint-initdb.d without removing the volume, please leave a comment and I will update this answer.

Method 5

I used docker-entrypoint-initdb.d approach (Thanks to @Kuhess)
But in my case I want to create my DB based on some parameters I defined in .env file so I did these

1) First I define .env file something like this in my docker root project directory

MYSQL_DATABASE=my_db_name
MYSQL_USER=user_test
MYSQL_PASSWORD=test
MYSQL_ROOT_PASSWORD=test
MYSQL_PORT=3306

2) Then I define my docker-compose.yml file. So I used the args directive to define my environment variables and I set them from .env file

version: '2'
services:
### MySQL Container
    mysql:
        build:
            context: ./mysql
            args:
                - MYSQL_DATABASE=${MYSQL_DATABASE}
                - MYSQL_USER=${MYSQL_USER}
                - MYSQL_PASSWORD=${MYSQL_PASSWORD}
                - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
        ports:
            - "${MYSQL_PORT}:3306"

3) Then I define a mysql folder that includes a Dockerfile. So the Dockerfile is this

FROM mysql:5.7
RUN chown -R mysql:root /var/lib/mysql/

ARG MYSQL_DATABASE
ARG MYSQL_USER
ARG MYSQL_PASSWORD
ARG MYSQL_ROOT_PASSWORD

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

ADD data.sql /etc/mysql/data.sql
RUN sed -i 's/MYSQL_DATABASE/'$MYSQL_DATABASE'/g' /etc/mysql/data.sql
RUN cp /etc/mysql/data.sql /docker-entrypoint-initdb.d

EXPOSE 3306

4) Now I use mysqldump to dump my db and put the data.sql inside mysql folder

mysqldump -h <server name> -u<user> -p <db name> > data.sql

The file is just a normal sql dump file but I add 2 lines at the beginning so the file would look like this

--
-- Create a database using `MYSQL_DATABASE` placeholder
--
CREATE DATABASE IF NOT EXISTS `MYSQL_DATABASE`;
USE `MYSQL_DATABASE`;

-- Rest of queries
DROP TABLE IF EXISTS `x`;
CREATE TABLE `x` (..)
LOCK TABLES `x` WRITE;
INSERT INTO `x` VALUES ...;
...
...
...

So what happening is that I used “RUN sed -i ‘s/MYSQL_DATABASE/’$MYSQL_DATABASE’/g’ /etc/mysql/data.sql” command to replace the MYSQL_DATABASE placeholder with the name of my DB that I have set it in .env file.

|- docker-compose.yml
|- .env
|- mysql
     |- Dockerfile
     |- data.sql

Now you are ready to build and run your container

Method 6

edit: I had misunderstand the question here. My following answer explains how to run sql commands at container creation time, but not at image creation time as desired by OP.

I’m not quite fond of Kuhess’s accepted answer as the sleep 5 seems a bit hackish to me as it assumes that the mysql db daemon has correctly loaded within this time frame. That’s an assumption, no guarantee. Also if you use a provided mysql docker image, the image itself already takes care about starting up the server; I would not interfer with this with a custom /usr/bin/mysqld_safe.

I followed the other answers around here and copied bash and sql scripts into the folder /docker-entrypoint-initdb.d/ within the docker container as this is clearly the intended way by the mysql image provider. Everything in this folder is executed once the db daemon is ready, hence you should be able rely on it.

As an addition to the others – since no other answer explicitely mentions this: besides sql scripts you can also copy bash scripts into that folder which might give you more control.

This is what I had needed for example as I also needed to import a dump, but the dump alone was not sufficient as it did not provide which database it should import into. So in my case I have a script named db_custom_init.sh with this content:

mysql -u root -p$MYSQL_ROOT_PASSWORD -e 'create database my_database_to_import_into'
mysql -u root -p$MYSQL_ROOT_PASSWORD my_database_to_import_into < /home/db_dump.sql

and this Dockerfile copying that script:

FROM mysql/mysql-server:5.5.62
ENV MYSQL_ROOT_PASSWORD=XXXXX
COPY ./db_dump.sql /home/db_dump.sql
COPY ./db_custom_init.sh /docker-entrypoint-initdb.d/

Method 7

Based on Kuhess response, but without hard sleep:

RUN /bin/bash -c "/usr/bin/mysqld_safe --skip-grant-tables &" && 
  while ! mysqladmin ping --silent; do sleep 1; echo "wait 1 second"; done && 
  mysql -u root -e "CREATE DATABASE mydb" && 
  mysql -u root mydb < /tmp/dump.sql

Method 8

I have experienced the same problem, but managed to get it working by separating the MySQL start-up commands:

sudo docker build -t MyDB_img -f Dockerfile.dev
sudo docker run --name SomeDB -e MYSQL_ROOT_PASSWORD="WhatEver" -p 3306:3306 -v $(pwd):/app -d MyDB_img

Then sleep for 20 seconds before running the MySQL scripts, it works.

sudo docker exec -it SomeDB sh -c yourscript.sh

I can only presume that the MySQL server takes a few seconds to start up before it can accept incoming connections and scripts.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x