MySQL – Docker Development Environment (DDE Part 4)

MySQL

Let’s start with creating a container for MySQL.

.cnf File

Let’s create a file called zz-mysqld.cnf  in the docker/mysql directory. This file will allow us to set database character sets and collation.

[mysqld]
character-set-server=#CHAR_SET_SERVER#
collation-server=#COLLATION_SERVER#

[client]
default-character-set=#DEFAULT_CHAR_SET#

Dockerfile

In the docker/mysql directory, create a file called Dockerfile. A Dockerfile is a text document that contains commands that a user could call on the command line to assemble the image. This allows us to automate our build and automatically execute command-line instructions.

The contents of the file should look like this:

FROM mysql:5.7
MAINTAINER Your Name <yourname@youremail.com>

ARG CHAR_SET_SERVER
ARG COLLATION_SERVER
ARG DEFAULT_CHAR_SET

COPY zz-mysqld.cnf /etc/mysql/mysql.conf.d

RUN sed -i "s/#CHAR_SET_SERVER#/${CHAR_SET_SERVER}/g" /etc/mysql/mysql.conf.d/zz-mysqld.cnf && \
    sed -i "s/#COLLATION_SERVER#/${COLLATION_SERVER}/g" /etc/mysql/mysql.conf.d/zz-mysqld.cnf && \
sed -i "s/#DEFAULT_CHAR_SET#/${DEFAULT_CHAR_SET}/g" /etc/mysql/mysql.conf.d/zz-mysqld.cnf

That’s it for our Dockerfile. Let’s look at the contents of this file.

FROM mysql:5.7
MAINTAINER Your Name <yourname@youremail.com>

This says that our container is going to be based on the official mysql repository on Docker Hub and that we are going to use version 5.7.

ARG CHAR_SET_SERVER
ARG COLLATION_SERVER
ARG DEFAULT_CHAR_SET

These are the arguments that we are going to pass into our container.

COPY zz-mysqld.cnf /etc/mysql/mysql.conf.d

This copies the file zz-mysqld.cnf that we created earlier to the /etc/mysql/mysql.conf.d directory in the container.

RUN sed -i "s/#CHAR_SET_SERVER#/${CHAR_SET_SERVER}/g" /etc/mysql/mysql.conf.d/zz-mysqld.cnf && \
    sed -i "s/#COLLATION_SERVER#/${COLLATION_SERVER}/g" /etc/mysql/mysql.conf.d/zz-mysqld.cnf && \
sed -i "s/#DEFAULT_CHAR_SET#/${DEFAULT_CHAR_SET}/g" /etc/mysql/mysql.conf.d/zz-mysqld.cnf

This runs three sed commands that do inline replacements to set our character set and collation.

Docker Hub

Before going any farther, let’s talk about Docker Hub tags.

mysql docker hub

If we take a look at the first line in the Dockerfile, FROM mysql:5.7mysql is the name of the Docker Hub repository image and the tag 5.7 is the version of the application. So, every time we build our mysql container we will get mysql 5.7. Using tags 5.7.17, 5.7, 5, and latest will also get mysql 5.7.

If we use the tag 5.7.17, we will build mysql 5.7.17. And, if we use use the tag 5, we will build the latest version of mysql that is less than version 6. By using 5.7 we are saying that every time we build our container, we will build the latest image that is less than 5.8.

Be careful when using the latest tag. Currently, this would get mysql 5.7 but, if the tag is moved to mysql 8, the next time you built the image you would get mysql 8.

Docker Store

Docker also has Docker Store. More information about Docker Store can be found in this blog post.

docker-compose.yml

Now, edit the docker-compose.yml file you created earlier. Here we will define the mysql service.

version: '2'

services:
    mysqldb:
        build:
            context: ./docker/mysql
            args:
                CHAR_SET_SERVER: ${CHAR_SET_SERVER}
                COLLATION_SERVER: ${COLLATION_SERVER}
                DEFAULT_CHAR_SET: ${DEFAULT_CHAR_SET}
        ports:
            - "${MYSQL_PORT}:3306"
        restart: always
        environment:
            MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
            MYSQL_DATABASE: ${MYSQL_DATABASE}
            MYSQL_USER: ${MYSQL_USER}
            MYSQL_PASSWORD: ${MYSQL_PASSWORD}
        volumes:
            - ./data/mysql:/var/lib/mysql

 This defines a service called mysqldb that uses the Dockerfile located in the myproject/docker/mysql directory that we created earlier, sets some arguments that are going to be passed to the Dockerfile, sets environment variables that will be used by the mysql container, and defines the ports and volumes that will be used by the host and the container.

            args:
                CHAR_SET_SERVER: ${CHAR_SET_SERVER}
                COLLATION_SERVER: ${COLLATION_SERVER}
                DEFAULT_CHAR_SET: ${DEFAULT_CHAR_SET}
        environment:
            MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
            MYSQL_DATABASE: ${MYSQL_DATABASE}
            MYSQL_USER: ${MYSQL_USER}
            MYSQL_PASSWORD: ${MYSQL_PASSWORD}

When Docker Compose reads this file, it will look at our .env file first to get any variables and their values. These variables and their values are then passed into our docker-compose.yml file. We can let Docker Compose know where to use those variables by using the format: ${VARIABLE_NAME}.

For example, in the above section, we are assigning the value “utf8” to the CHAR_SET_SERVER argument. We are also assigning values to the environment values needed by the MySQL image.

        ports:
            - "${MYSQL_PORT}:3306"

In order to keep a persistent database, we need to map it to a folder on the host computer. So, we will map the /var/lib/mysql directory on the container to the ./data/mysql directory to the host.

        volumes:
            - ./data/mysql:/var/lib/mysql

Run It!

You should now be able to run the mysql container with the following command:

$ docker-compose up --build -d mysqldb

 This builds the mysqldb container and starts it.

Note:  The -d flag will run the command and give you back the command line prompt.

Running:

$ docker-compose ps

should show that the container is running.

Name               Command                 State        Ports
-------------------------------------------------------------------------
mysqldb_1    docker-entrypoint.sh mysqld    Up     0.0.0.0:3306->3306/tcp

In the next post, we will create our PHP container.

Leave a Reply

Your email address will not be published. Required fields are marked *