Home

#Spring Boot Docker MySQL Demo

I have been using Docker for a while and really like it, so I wanted to try running Spring Boot application in Docker container with MySQL. And it was a quite interesting learning experience. Here I wrote it down and you can find the demo project at GitHub.

###Start with Spring Boot Flyway Sample

First, I want to start with a small and running sample project. There is a good one in Github: Spring Boot Flyway Sample.

I copied the code, and changed it into a Web application. See DemoApplication.java. Tested locally by opening http://localhost:8080, and everything was good and simple.

###Change Database to MySQL

The Flyway sample project is using HSQL embedded database, and I changed to MySQL database by replacing hsql dependency with mysql.

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
</dependency>

I also started a local MySQL Server, with a new database "demo", and added a new user "demo-user" with password "demo-pass". The application config file was changed to YAML format:

spring:
    jpa:
        database: MYSQL
        hibernate:
            ddl-auto: validate

    datasource:
        url: jdbc:mysql://localhost/demo
        username: demo_user
        password: demo_pass
        driver-class-name: com.mysql.jdbc.Driver

The first problem I had was the database migration script for creating table PERSON:

CREATE TABLE PERSON (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    first_name varchar(255) not null,
    last_name varchar(255) not null
);

I got SQL syntax error when Flyway ran the script in MySQL. It turned out SQL IDENTIFY standard was not followed by MySQL. See the blog Subtle SQL differences: IDENTITY columns talked about it in details.

So I have to change the script for MySQL:

CREATE TABLE PERSON (
    id BIGINT NOT NULL AUTO_INCREMENT,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

After that, the demo application was working again.

###Run MySQL Server in Docker Container

There are many MySQL Docker images on the Internet, and I even created one by myself. But for this simple demo, I feel this image is good enough.

Run MySQL Server in Docker container by:

docker run --name demo-mysql -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=demo -e MYSQL_USER=demo_user -e MYSQL_PASSWORD=demo_pass -p 3306:3306 -d mysql:5.6

• --name demo-mysql to assign a name to the container.
• -e to pass environment variables to the container, and create a demo database with a user demo-user and password. This user will be granted superuser permissions for the demo database.
• -p 3306:3306 to expose MySQL port to the local host.
• -d to tell Docker to daemonize the container and keep it running.
• mysql:5.6 to download MySQL 5.6 Server image from Docker public repo if not in your computer yet.

nRun docker logs demo-mysql to see the logging output from MySQL Server, and make sure the server is running OK.

Since I'm using Mac OSX and Boot2Docker, the Docker host is not local, but in a different VM. Run boot2docker ip to get the IP address of the VM, then use this IP address to access the MySQL Server through SQL Client UI Tool. For example, Boot2Docker IP usually is 192.168.59.103, so I used MySQL Workbench to open a connection to it, with user demo-user, password demo-pass, to make sure the Server can be connected, and an empty database demo in there.

If you are using Linux, and you have MySQL Server running locally with stabdard port 3306, you may have to change the expoed port to different number, like 3307. So the running command will be:

docker run --name demo-mysql -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=demo -e MYSQL_USER=demo_user -e MYSQL_PASSWORD=demo_pass -p 3307:3306 -d mysql:5.6

And you can access the server by localhost:3307/demo

###Connect to Dockerized MySQL Server

After I have a running MySQL server in a Docker container, I want my demp application connect to it. So I updated the config to point to the new server url:

spring:
    jpa:
        database: MYSQL
        hibernate:
            ddl-auto: validate

    datasource:
        url: jdbc:mysql://192.168.59.103/demo
        username: demo_user
        password: demo_pass
        driver-class-name: com.mysql.jdbc.Driver

Then I had my second problem, I got exception saying Hibernate could not find table "person". That was really strange, I could see the table "PERSON" in the demo database through MySQL Workbench, so Flyway did run the migration script and created the table.

After some research, I found out it is because Spring Boot is using ImprovedNamingStrategy, which changes table name to all lower case. Because of Identifier Case Sensitivity in MySQL, database and table names are not case sensitive in Windows and Mac OS, but case sensitive in most varieties of Unix. That is why the application works when connected to locally running MySQL Server in Mac OS, but not when connected to Dockerized MySQL Server, which is running in Linux.

So there are many solutions to this issue. I can change naming strategy to EJB3NamingStrategy and annotate the entity class with table name. Or I can change the migration script to use all lower-case for table names. In order to make the code consistent across different platform, I prefer the second sloution, to use lower case and snake case for all identities in the sql script. The migration script became:

CREATE TABLE person (
    id BIGINT NOT NULL AUTO_INCREMENT,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

insert into person (first_name, last_name) values ('Yuan', 'Ji');

After this fix, I deleted the old MySQL container and ran the docker command again to create a new container, the application was runnung again.

###Dockerize Demo Applicaton and Link to Dockerized MySQL Server

The last step is to dockerize my demo application. This time, I followed the example Spring Boot with Docker, to add dependency to docker-maven-plugin:

<build>
    ...
    <plugins>
        ...
        <plugin>
            <groupId>com.spotify</groupId>
            <artifactId>docker-maven-plugin</artifactId>
            <version>0.2.6</version>
            <configuration>
                <imageName>${docker.image.prefix}/${project.artifactId}</imageName>
                <dockerDirectory>src/main/docker</dockerDirectory>
                <resources>
                    <resource>
                        <targetPath>/</targetPath>
                        <directory>${project.build.directory}</directory>
                        <include>${project.build.finalName}.jar</include>
                    </resource>
                </resources>
            </configuration>
        </plugin>
    </plugins>
</build>

And add Dockerfile at /src/main/docker/Dockerfile:

FROM java:8
VOLUME /tmp
ADD demo.jar demo.jar
RUN bash -c 'touch /demo.jar'
ENTRYPOINT ["java","-Djava.security.egd=file:/dev/./urandom","-jar","/demo.jar"]

Before I run the demo, there is a very important change to applicaiton.yml:

spring:
    jpa:
        database: MYSQL
        hibernate:
            ddl-auto: validate

    datasource:
        url: jdbc:mysql://mysql:3306/demo
        username: demo_user
        password: demo_pass
        driver-class-name: com.mysql.jdbc.Driver

The data source url was changed to jdbc:mysql://mysql:3306/demo, and then we can build our application image jiwhiz/spring-boot-docker-mysql by Docker:

mvn clean package docker:build

And run our demo application in a Docker conainer:

docker run -p 8080:8080 --name demo-app --link demo-mysql:mysql -d jiwhiz/spring-boot-docker-mysql

Open browser at http://192.168.59.103:8080/ and it is working! I can check the application log by docker logs demo-app.

So what is the magic? It is the Docker linking system that link multiple containers together and send connection information from one to another. Because of the option --link demo-mysql:mysql, Docker creates a secure tunnel between two containers, and passes demo-mysql server info to demo-app. If you run:

docker exec -it demo-app bash

You get into demo-app container and open a bash shell inside. Look at the /etc/hosts:

root@3f0a35178def:/# cat /etc/hosts
172.17.0.5    f0a35178def
127.0.0.1     localhost
::1           localhost ip6-localhost ip6-loopback
fe00::0       ip6-localnet
ff00::0       ip6-mcastprefix
ff02::1       ip6-allnodes
ff02::2       ip6-allrouters
172.17.0.3    mysql a120627328c8 demo-mysql

The first line is an entry for the demo-app container that uses the Container ID as a host name. And the last entry uses the link alias to reference the IP address of the demo-mysql container. So we can use mysql for data source url in our application.yml.

###Conclusion

Using Docker it is extremely easy and cheap to get a server running. I can run different version of MySQL with different server configurations to test my code or trouble shooting issues. Each contaier is much smaller compared to a new VM. Since they are very well isolated, I can put as many servers or applications as I need into one VM, and deploy to cloud.

There are still lots to learn about Docker, for example, how to use Docker to run integration test with Jenkins, so I can automate the build and deployment. My TODO list never ends.

The final code can be found at https://github.com/jiwhiz/spring-boot-docker-mysql. Let me know if you find any issue or you have better idea to improve it.