Persisting MySQL data in Docker

Question

I am new to Docker. I have successfully created a Docker image with the following Dockerfile:

From alpine:3.4
MAINTAINER SomeName - domain.tld

# Timezone
ENV TIMEZONE Asia/Kolkata

# RUN sed -i 's#dl-cdn\.alpinelinux\.org#mirrors\.aliyun\.com#' /etc/apk/repositories

# install mysql, apache and php and php extensions, tzdata, wget
RUN echo "@community http://dl-cdn.alpinelinux.org/alpine/edge/community" >> /etc/apk/repositories && \
    apk add --update \
    mysql mysql-client \
    apache2 \
    curl wget \
    tzdata \
    php5-apache2 \
    php5-cli \
    php5-phar \
    php5-zlib \
    php5-zip \
    php5-bz2 \
    php5-ctype \
    php5-mysqli \
    php5-mysql \
    php5-pdo_mysql \
    php5-opcache \
    php5-pdo \
    php5-json \
    php5-curl \
    php5-gd \
    php5-gmp \
    php5-mcrypt \
    php5-openssl \
    php5-dom \
    php5-xml \
    php5-iconv \
    php5-xdebug@community

RUN curl -sS https://getcomposer.org/installer | \
    php -- --install-dir=/usr/bin --filename=composer

# configure timezone, mysql, apache
RUN cp /usr/share/zoneinfo/${TIMEZONE} /etc/localtime && \
    echo "${TIMEZONE}" > /etc/timezone && \
    mkdir -p /run/mysqld && chown -R mysql:mysql /run/mysqld /var/lib/mysql && \
    mysql_install_db --user=mysql --verbose=1 --basedir=/usr --datadir=/var/lib/mysql --rpm > /dev/null && \
    mkdir -p /run/apache2 && chown -R apache:apache /run/apache2 && chown -R apache:apache /var/www/localhost/htdocs/ && \
    sed -i 's#AllowOverride none#AllowOverride All#' /etc/apache2/httpd.conf && \
    sed -i 's#ServerName www.example.com:80#\nServerName localhost:80#' /etc/apache2/httpd.conf && \
    sed -i '/skip-external-locking/a log_error = \/var\/lib\/mysql\/error.log' /etc/mysql/my.cnf && \
    sed -i '/skip-external-locking/a general_log = ON' /etc/mysql/my.cnf && \
    sed -i '/skip-external-locking/a general_log_file = \/var\/lib\/mysql\/query.log' /etc/mysql/my.cnf

# Configure xdebug
RUN echo "zend_extension=xdebug.so" > /etc/php5/conf.d/xdebug.ini && \ 
    echo -e "\n[XDEBUG]"  >> /etc/php5/conf.d/xdebug.ini && \ 
    echo "xdebug.remote_enable=1" >> /etc/php5/conf.d/xdebug.ini && \  
    echo "xdebug.remote_connect_back=1" >> /etc/php5/conf.d/xdebug.ini && \ 
    echo "xdebug.idekey=PHPSTORM" >> /etc/php5/conf.d/xdebug.ini && \ 
    echo "xdebug.remote_log=\"/tmp/xdebug.log\"" >> /etc/php5/conf.d/xdebug.ini

#start apache
RUN echo "#!/bin/sh" > /start.sh && \
    echo "httpd" >> /start.sh && \
    echo "nohup mysqld --skip-grant-tables --bind-address 0.0.0.0 --user mysql > /dev/null 2>&1 &" >> /start.sh && \
    echo "sleep 3 && mysql -uroot -e \"create database db;\"" >> /start.sh && \
    echo "tail -f /var/log/apache2/access.log" >> /start.sh && \
    chmod u+x /start.sh

WORKDIR /var/www/localhost/htdocs/

EXPOSE 80
EXPOSE 3306

#VOLUME ["/var/www/localhost/htdocs","/var/lib/mysql","/etc/mysql/"]
ENTRYPOINT ["/start.sh"]

Ideally I want the docker image to contain Apache, PHP and MySQL -- all in one image. I want the image to be as portable as possible. So that every time I need to start a new project, I can just create a new container and mount volumes and go ahead and code.

When I start a new container, I get everything as expected. But the mysql data cannot be mounted. I think it is because the mounting is one-way (or is it?) If I add -v /project_dir/data:/var/lib/mysql while starting a new container, mysql doesn't start up because the project_dir/data is empty. My questions are:

1. How to mount data from my project_dir to /var/lib/mysql inside the container?
2. How can I set different mysql passwords to different containers?

Edit: If I don't mount my local volume (i.e. project_dir/data) and start the container, get a shell tty, I see that /var/lib/mysql has several files, which I assume are needed to run mysql and its initial databases. But if I mount my local volume and ls inside /var/lib/mysql (inside the container), it is empty. That is why MySQL is not starting up and I cannot persist data from outside the container. My question is: how I can get it running and persist data from local directory.

Answer 1

The problem:

What's going on here is that you create an empty database while the Docker image is being built:

mysql_install_db --user=mysql --verbose=1 --basedir=/usr --datadir=/var/lib/mysql --rpm > /dev/null && \

But when you create a container from this image, you mount a volume on the /var/lib/mysql folder. This hides your containers data, to expose your host's folder. Thus, you're seeing an empty folder.

The solution:

If you take a look at https://hub.docker.com/_/mysql/, you'll see that this problem is addressed by creating the database when the container actually starts, not when the image is created. This answers both your questions:

1. If you start your container with your mounted volume, then the database init will be executed afterwards, and your data will actually be written in your host's FS
2. You have to pass those information as env variables

In other words, init your DB with a script in an ENTRYPOINT, rather than directly in the image.

Warnings:

Some warnings, though. The way you did your image is not really recommended, because Docker's philosophy is "one process per container". The difficulty you have here is that you'll have to start multiple services on the same container (apache, Mysql, etc.), so you may have to do things on both in your entrypoint, which is confusing. Also, is one service fails, your container will still be up, but not working as expected.

I then would suggest to split what you did in as 1 image per process, then either start them all with raw Docker, or user something like docker-compose.

Also, this will benefit you in the way that you'll be able to use already existing and highly configurable images, from the Docker Hub : https://hub.docker.com. Less job for you and less error prone.