DEV Community

Cover image for Run MySQL on Docker and use in your Java App
Sandro Giacomozzi
Sandro Giacomozzi

Posted on • Updated on

Run MySQL on Docker and use in your Java App

Running MySQL in Docker is very useful to use in development. In this tutorial, we will see how easy it is to start MySQL in Docker and use it in our Java application with Spring Boot.

Prepare

First, you need a docker runtime on you machine. If you don't have it, please install following this docker documentation: https://docs.docker.com/install/

Start Mysql container

To start the container, type the command below in your terminal:



docker run --name mysql57 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 -d mysql/mysql-server:5.7


Enter fullscreen mode Exit fullscreen mode

The command above, pulled image if not found in local repository and run mysql container in detached mode. Configure local port equals to container port and set root password to 1234.

Check your container running:



docker ps
CONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS                   PORTS                               NAMES
380eca8a553c        mysql/mysql-server:5.7    "/entrypoint.sh mysq…"   8 minutes ago       Up 8 minutes (healthy)   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql


Enter fullscreen mode Exit fullscreen mode

As we can see, mysql5.7 is running on localhost:3306
At this point, you have a local mysql server without install it.

Note: You will not have external access to the container with root user.

Create a database

Type:



docker exec -it mysql57 bash


Enter fullscreen mode Exit fullscreen mode

This command access the mysql container and allow to execute commands on database. Inside a container, type:



mysql -h localhost -u root -p


Enter fullscreen mode Exit fullscreen mode

Remember, the password is 1234. First, we need to create a user for out-of-container access because root access is not allowed:



CREATE USER 'demo_java' IDENTIFIED BY 'java';
grant all on *.* to 'demo_java'@'%' identified by '1234';
FLUSH PRIVILEGES;



Enter fullscreen mode Exit fullscreen mode

To create a database, paste DDL bellow:



CREATE DATABASE hello_java CHARACTER SET utf8 COLLATE utf8_general_ci;


Enter fullscreen mode Exit fullscreen mode

Next, type:



show databases;
+--------------------+
| Database           |
+--------------------+
| hello_java         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


Enter fullscreen mode Exit fullscreen mode

Create a database automatically

Another way to start a database and automatic create a hello_java database and demo_java user:



docker run --name mysql57 -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=1234 \
-e MYSQL_USER=demo_java \
-e MYSQL_PASSWORD=1234 \
-e MYSQL_DATABASE=hello_java \
-d mysql/mysql-server:5.7


Enter fullscreen mode Exit fullscreen mode

With the above command, you do not have to enter the database and create a database and a user. Cool, is not it?

Create Demo App

Go to https://start.spring.io/ and create a demo app with the Web and Jpa dependencies.

Import application in your IDE and follow steps below:

1 - Edit application.properties file:



spring.datasource.url=jdbc:mysql://localhost:3306/hello_java
spring.datasource.username=demo_java
spring.datasource.password=1234
spring.jpa.hibernate.ddl-auto=update



Enter fullscreen mode Exit fullscreen mode

spring.jpa.hibernate.ddl-auto=update creates the person table if it doesn't exist.

Note: It's not recommended to use in production.

2 - Edit pom.xml file and add mysql connector java



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


Enter fullscreen mode Exit fullscreen mode

3 - Add domain, service and persistence classes:
Domain

Repository

Service

4 - Change main class to create two new Person:

Run Demo App

Start demo app just running a main class:

Application is running and data is persistent in database

Bonus

To expose your data as a Rest endpoint, just add this dependency:



<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>


Enter fullscreen mode Exit fullscreen mode

Access http://localhost:8080/persons

And Wow! Your data exposed as json.

Source code






Top comments (9)

Collapse
 
franzisk profile image
Francisco Vieira Souza

Hey friend,

Very nice your tutorial.

On the first command (to install mysql on the container), what happens if I have already a mysql local running on 3306 port?

I also would like to know how to create a container with mysql + java + MyApplication.jar, how can I do that?

Collapse
 
sandrogiacom profile image
Sandro Giacomozzi

Thanks for your feedback. See my other posts about this subject. About port, you received an error. Just change port of left side.

dev.to/sandrogiacom/kubernetes-for...

Collapse
 
gauravkohirkar profile image
GauravKohirkar • Edited

Great Tutorial !!
I guess I was stuck on the container access issue.
CREATE USER 'demo_java' IDENTIFIED BY 'java';
grant all on . to 'demo_java'@'%' identified by '1234';
FLUSH PRIVILEGES;
This solved it. Thanks!!
Can you please also do an Enterprize level Spring Boot Running in docker and orchestrated on Kubernetes tutorial? That'd be great.. :)

Collapse
 
sandrogiacom profile image
Sandro Giacomozzi

Thanks for your feedback. I wrote one serie about this.
dev.to/sandrogiacom/kubernetes-for...

Collapse
 
robson96 profile image
Robson96

Pode explicar pfv esse trecho de codigo:
De 'demo_java' pro final do codigo nao entende!
CREATE USER 'demo_java' IDENTIFIED BY 'java';
grant all on . to 'demo_java'@'%' identified by '1234';
FLUSH PRIVILEGES;

O que faz essas tres linhas?

Collapse
 
sandrogiacom profile image
Sandro Giacomozzi

Ola. Ele cria um usuário no banco e da permissão necessárias para criar as tabelas

Collapse
 
laurebaugnies profile image
laurebaugnies

Hi ! Thanks for your post, it has been really helpfull ! I have yet a question. How can we save the state of the container in order to reuse it later on a different computer ? How to prevent data loss ?

Collapse
 
sinha123 profile image
sinha123

Hi friend,
I tried all the three option of jdbc url, but did not work. Request your help.

spring.datasource.url=jdbc:mysql://localhost:3306/hello_java

spring.datasource.url=jdbc:mysql://:3306/hello_java

spring.datasource.url=jdbc:mysql://:3306/hello_java

Just to tell you My Machine is Windows 10 pro.
Spring boot codebase running on my local eclipse.

And I have Docker Desktop installed in my System where I have created Mysql container.

Following error comes when I Run as Spring Boot App:

2020-05-23 20:28:02.279 INFO 76 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-05-23 20:28:24.354 ERROR 76 --- [ main] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.16.jar:8.0.16]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.16.jar:8.0.16]

Collapse
 
balajich profile image
Balaji Sankar Chopparapu

I ran to the same situation. The root cause is MySQL Server and Clients are running in two different versions. Thats the reason you are seeing handshake connection issue because of mysql protocol version mismatch.

Run you server with most recent version that should solve the problem.

docker run --name mysql57 -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=1234 \
-e MYSQL_USER=demo_java \
-e MYSQL_PASSWORD=1234 \
-e MYSQL_DATABASE=hello_java \
-d mysql/mysql-server

or Specify the compatible JDBC driver version in pom.xml