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

When you have a database in a docker container, using a volume is the only way to go. Maybe we should state that "database inside your container" means not using a volume and having the data in the standard 10GB (or bigger if the default size has been modified) and database in a volume means docker run -v myvolume...

the doc docs.docker.com/engine/tutorials/dockervolumes is a must-see

I completely agree with Alexandre's answer, but I think it is wrong to put your database inside your container, also for another reason. By default, the size of an image (so a container when you run it) is 10 GB. This should be quite comfortable for some executables, libraries and configuration files. You can modify this default, and put a larger value when creating your image. But what happens when you hit this new limit ? Suppose you think the size your database will be 100 GB, and in fact, you put more data than planned, and you eventually need 5 or 20 times more? A volume can be on a SAN and extended if needed.

Answer 2

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.