Spring boot on host with MySQL running on a docker container

I have read a lot about docker and didn’t get a chance to use it. The reason is that there was no use case till now and I thought that if I tried it just for the sake of learning it, I will soon forget it if there is no use case.

Now, there is a use case. I need to shard a MySQL database at my work and I do not know how sharding works. No problem, I will learn it. But how do I try it I thought. Docker to rescue.

The idea is to create a MySQL instances on the fly in a container and write a spring boot application on host that will use it with some sort of sharding logic. The reason I thought of using docker for this is because it will save me the hassle of setting up multiple MySQL instances on my host machine.

To begin with, I decided to create a Spring Boot application that would use a single MySQL instance running on the docker container. This should be simple enough to start. As it turns out, it is!

Let us get started then.

Things we would need

1. JDK 8 or later
2. Docker and basic understanding of what it is and how it works
3. IDE of your choice
4. Basic knowledge of working with Java and Spring Boot
5. OS of your choice: I will use Linux for this article

Now, let us begin by installing docker.

Installing Docker

Follow the instructions on the following link for Linux:

Install Docker Engine on Ubuntu

This is the quickest way.

Once docker is up and running, we need to have a MySQL instance running in a Docker container.

Bringing up a MySQL docker instance

Execute the below command to get the latest MySQL image:

shell> docker pull mysql/mysql-server:latest

Note: You might have to execute docker commands with super user privileges depending on your OS and user permissions.

Once that is successful, check if the image is there or not by executing the following command:

shell> docker images
REPOSITORY           TAG       IMAGE ID       CREATED       SIZE
mysql/mysql-server   latest    1504607f1ce7   7 weeks ago   391MB

Now we are good to start a MySQL instance:

shell> docker run -p 3306:3306 --name=mysql0 --restart on-failure -d mysql/mysql-server:latest

Here, -p flag specifies that we need to map <hostPort>:<containerPort>. This would be significant when we will try to access the instance using our Spring Boot application running on host machine.

After a couple of minutes max, running:

shell> docker ps

should provide you the status of your container. It should be showing health: healthy.

Now, we need to configure the MySQL instance.

Configuring the MySQL instance

Run the following command:

shell> docker logs mysql0 2>&1 | grep GENERATED
GENERATED ROOT PASSWORD: Axegh3kAJyDLaRuBemecis&EShOs

Password would be different for you. Copy the password and then execute the below command:

docker exec -it mysql0 mysql -uroot -p

Enter the copied password on the password prompt.

Now, execute the following queries one after the another:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER 'root'@'172.17.0.1' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.17.0.1' WITH GRANT OPTION;
mysql> flush privileges;
mysql> create database db_example;

This is what we did by executing these queries:

1. Changed the default auto-generated password to ‘password’ — for ease of use — we will need this later in our Spring Boot application.
2. We created a user ‘root’ for ip ‘172.17.0.1’ and granted all privileges — This means that root user can connect to itself from the specified ip — ip of our container.
3. Created a database named — db_example

Now, we will create a spring boot application that will work with the MySQL instance that we just started with the help of docker.

Creating a sample Spring Boot application

Follow the official Spring Boot guide from the below link — See the exception section just below before clicking the link:

Accessing data with MySQL

Exceptions in the guide

In the above link, do not follow the Create the Database section. We have already created the database db_example as part of the queries that we executed.

Use the below content in application.properties file.

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://0.0.0.0:3306/db_example
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name =com.mysql.jdbc.Driver
#spring.jpa.show-sql: true

Results

Once the Spring Boot application is up and running, execute the below curl command to add a new user in the application:

shell> curl localhost:8080/demo/add -d name=First -d email=someemail@someemailprovider.com
shell> Saved

Now, we should be able to get the user that we just added:

shell> curl 'localhost:8080/demo/all'
shell> [{"id":1,"name":"First","email":"someemail@someemailprovider.com"}]

Conclusion

We saw that we were able to bring up a MySQL instance in a docker container and connect to it from a Spring Boot application running on the host machine. This saved us the hassle of setting up a MySQL instance on our host machine and in our later use cases, will allow us the flexibility to bring up more MySQL instances and we will then be able to try our hand at sharding. This is a good first step!