Introduction

The Spring Boot Java application was initially using the H2 embedded database, first the in-memory version (losing all data each run), then the file based version. The database structure is created using Liquibase.

Then the requirement was to have the Spring Boot Java application still not dockerized (just started with mvn spring-boot:run), but have it connect to a MySql 8 database than runs inside a Docker container. Most examples you can find have also the Spring Boot application in a Docker container.
The official Spring documentation only shortly mentions the possibility of running MySql in Docker, but no more details on how to do that: https://spring.io/guides/gs/accessing-data-mysql/

This is how the setup should be working:

I set up that docker container using the Windows 10 WSL 1 shell within IntelliJ (note WSL 2 is now recommended):

docker pull mysql/mysql-server:8.0
docker run --name recipesmysql8 -d mysql/mysql-server:8.0

After changing the One Time Password (OTP) for root, creating the database user, creating a 'recipes' database, this is how docker ps looked:

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3a507fc66690 mysql/mysql-server:8.0 "/entrypoint.sh mysq…" 6 days ago Up 6 days (healthy) 3306/tcp, 33060-33061/tcp recipesmysql8

And MySql is indeed running: docker logs recipesmysql8 shows:

2022-08-31T19:02:05.722742Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30) starting as process 1
2022-08-31T19:02:05.755538Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-31T19:02:06.104813Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-31T19:02:06.416522Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-08-31T19:02:06.416574Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-08-31T19:02:06.443572Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-08-31T19:02:06.443740Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MyS
QL Community Server - GPL.

So all running fine, ports seemed fine. The Spring Boot application configuration for JDBC is this:

spring.datasource.url=jdbc:mysql://localhost:3306/recipes
spring.datasource.username=recipes
spring.datasource.password=mypasswd
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

But then the mvn spring-boot:run gave this relatively vague error:

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.
...
Caused by: java.net.ConnectException: Connection refused: no further information

So unable to connect, but why? It does not seem an incorrect username/password combination, then I would expect some unauthorized/not authenticated type of message.
Then I found this handy command determining what the real IP should be of the machine that the MySql docker runs in:

docker inspect -f '{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $(docker ps -aq)

I found my MySql container at: /recipesmysql8 - 172.17.0.2
Same result, shorter answer: docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' recipesmysql8
Or even just without the filtering: docker inspect recipesmysql8

So the JDBC configuration I changed to:

spring.datasource.url=jdbc:mysql://172.17.0.2:3306/recipes
spring.datasource.username=recipes
spring.datasource.password=mypasswd
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

But then the mvn spring-boot:run gave this timeout error:

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: 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.
...
Caused by: java.net.ConnectException: Connection timed out: no further information

So a similar error message, but this time a connection timeout. Is maybe the problem connecting from within IntelliJ where I execute the mvn spring-boot:run command, to Docker that runs in WSL?

I tried by disabling the antivirus software and local firewall and WIFI, but those didn't work either, at most a different error message like:

Caused by: java.net.NoRouteToHostException: No route to host: no further information

And even with the mysql shell client does it give an error (bit less clear):

mysql -h 172.17.0.2 -P 3306 --protocol=tcp -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '172.17.0.2' (11)

Solution

Then I stopped my container, started a new one with explicitly ports-mapping specified:

docker run --name recipesmysql8v2 -p 3306:3306 -d mysql/mysql-server:8.0

docker ps output:
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS                   PORTS
    NAMES
819333cf718e        mysql/mysql-server:8.0   "/entrypoint.sh mysq…"   2 minutes ago       Up 2 minutes (healthy)   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060-33061/tc
p   recipesmysql8v2

Then a different error message for mysql -h 127.0.0.1 -P 3306 --protocol=tcp -u recipes -p appeared:

ERROR 1130 (HY000): Host '172.17.0.1' is not allowed to connect to this MySQL server

That looked promising.

Note: due to having a new container, I had to reset the OTP for user 'root' again of course. You can find that OTP by issuing docker logs recipesmysqlv2 | grep GENERATED.
Then issue these commands:

docker exec -it your_container_name_or_id bash
mysql -u root -p
Enter password: <enter the one found with the above grep shell command>
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your secret password';

Create the Spring Boot application database again: create database recipes;
Add the Spring Boot application user again: create user 'recipes'@'%' identified by 'L7z$11Oeylh4';

Give that user only the necessary permissions:
grant create, select, insert, delete, update on recipes.* to 'recipes'@'%';

After that, these entries should be in the mysql.user table:
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | recipes          |
| localhost | healthchecker    |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

Note the recipes user entry with host '%', which allows access from any host, which you probably want to make more secure in a production environment. See https://downloads.mysql.com/docs/mysql-secure-deployment-guide-8.0-en.pdf for tips.

Now you should be able to connect from the mysql client prompt in several ways:

mysql -h 127.0.0.1 -P 3306 --protocol=tcp -u recipes -p
mysql -h localhost -P 3306 --protocol=tcp -u recipes -p
mysql -h 0.0.0.0 -P 3306 --protocol=tcp -u recipes -p

Note that the 172.17.0.2 still does not connect! For that to work you'll have to probably add that host to the above mysql.user table (not tried to see if that works).

Instead of mysql client you can also use the standard *nix command telnet to see if at least the port is reachable:

telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.

(I don't remember what it showed when the initial issue with the docker container named 'recipesmysql8' was there)

And after starting the Spring Boot application: connection worked and tables and indexes were created!

Note: only from within the docker image can you connect like this: docker exec -it recipesmysql8v2 bash and execute mysql -u root -p after having issued ALTER USER 'root'@'localhost' IDENTIFIED BY 'nR128^n8f3kx';
Because running mysql -h 127.0.0.1 -P 3306 --protocol=tcp -u root -p from within the commandline WSL still gives: ERROR 1045 (28000): Access denied for user 'root'@'172.17.0.1' (using password: YES)

Probable cause: when you only have MySql in a Docker container, it is not in the same "network" as Docker containers, so the port 3306 is not reachable from outside Docker runtime. So you will have to tell Docker how you want to expose the port(s). Another way to solve this is to have the Spring Boot application also as a Docker application; and potentially configure it via docker-compose, see here for an example on how to do this: https://www.javainuse.com/devOps/docker/docker-mysql.