Setting up MySQL and importing dump within Dockerfile

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?

Solution 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.

Solution 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

Solution 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.