Docker MySQL connection DBeaver

Question

I just finished following this Docker tutorial on youtube:

https://www.youtube.com/watch?v=SXY0MLHP3hA&lc=Ugzp3vKtSOp0rn2lYyd4AaABAg

I was able to create a couple of Docker containers for PHP and MySQL. The file structure is as follows:

>Docker_PHP_MySQL
 >DB
   -Dockerfile
 >src
   -index.php
 >www
   -Dockerfile
 development.env
 docker-compose.yml

The DB Dockerfile:

FROM mysql:8.0

index.php:

<?php
$mysqli = new mysqli('tut07-db', getenv('MYSQL_USER'), getenv('MYSQL_PASSWORD'), 'information_schema');
if($mysqli->connect_error) 
{
  echo 'Connection Error [', $mysqli->connect_errno, ']: ', $mysqli->connect_error;
} 
else 
{
  echo 'MySQLi Connected Successfully!';
}
?>

The www Dockerfile:

FROM php:7.2-apache

RUN docker-php-ext-install mysqli
RUN docker-php-ext-enable mysqli

Here is the development.env file:

MYSQL_USER=sys_admin
MYSQL_PASSWORD=sys_password
MYSQL_ROOT_PASSWORD=root_password

And then finally, the docker-compose.yml file:

version: "3"

networks:
  tut07-frontend:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.10.1.0/24
  tut07-backend:
    driver: bridge
    ipam: 
      driver: default
      config:
        - subnet: 172.10.2.0/23

services:
  tut07-db:
    build: ./db
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 3306:3306
    networks:
      tut07-backend:
        ipv4_address: 172.10.3.2
    env_file:
      - ./development.env
  tut07-www:
    build: ./www
    ports:
      - 8080:80
    volumes:
      - ./src:/var/www/html/
    networks:
      tut07-backend:
        ipv4_address: 172.10.2.2
      tut07-frontend:
        ipv4_address: 172.10.1.2
    depends_on:
      - tut07-db
    env_file:
      - ./development.env

Now here's where I know I'm going in completely blind...

In dbeaver, I'm trying to establish a connection:

But when I test the connection, I get the following response:

How do I fix this problem?

score 20 · Answer 1 · answered Jun 17 '20 at 08:50

For those who are running DB on different machine, you can do the following:

First, from where the container is, run docker ps to get the containers details including the Container ID

[root@test-001 ~]# docker ps 
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS              PORTS                    NAMES
1b02333fb3b9        tutum/nginx              "/usr/sbin/nginx"        6 weeks ago         Up 7 days           0.0.0.0:80->80/tcp       docker_nginx_1
8c1d234a3731        mariadb                  "docker-entrypoint.s…"   6 weeks ago         Up 7 days           0.0.0.0:3306->3306/tcp   docker_mysql_1

After you get the ID of your database container, run docker inspect CONTAINER_ID to get the related IP address of that container.

[root@test-001 ~]# docker inspect 8c1d234a3731 | grep -i IPAddress
            "SecondaryIPAddresses": null,
            "IPAddress": "",
                    "IPAddress": "172.23.0.3",

On Dbeaver, after selecting the DB type on adding new connection window, Go to network settings (SSH, ...) then put your docker machine details. and from the main page, in the Server Host: add the IP you got from the docker inspect command, then your credentials.

This should work

Hi!. what shall I enter for server-host in main page. I entered 127.0.0.1 or localhost, even tried the path to mysql but did not work — Yilmaz, Oct 06 '20 at 07:43
It should be your server IP, if you are running this on your local machine, then 127.0.0.1 should work. — Eng7, Oct 08 '20 at 08:14

score 12 · Accepted Answer · answered May 01 '19 at 04:16

12

Instead of giving localhost_compliant-db as the Server Host in dbeaver, try giving it localhost.

3306 port is bind on the host machine as well hence localhost:3306 from your host machine should work.

PS - I presume dbeaver and docker compose stack both are on the same machine. If not, you need to map localhost_compliant-db to certain IP which your host machine can understand.

answered May 01 '19 at 04:16

vivekyad4v

13,321
4
55
63

Thank you for your input. I will test and let you know. – John Beasley May 01 '19 at 12:35
3

I updated the server to localhost, but now I am receiving a new connection error, which reads as follows: Access denied for user 'root'@'172.10.2.1' (using password: YES) – John Beasley May 02 '19 at 01:30
That's an altogether different issue i.e w.r.t permissions in DB. Raise a new question if possible. This issue was mainly related to establishing connection to the DB container which seems to be resolved now. – vivekyad4v May 02 '19 at 03:42
I was thinking I should ask another question. Thanks for your help though. – John Beasley May 02 '19 at 03:47

Hamed Alizade · Answer 3 · 2022-11-23T07:50:05.567

6

A very clean way

1- docker compose up -d

2- docker ps

3- docker inspect <database container id> | grep -i IPAddress

4- Enter the IP value in the server-host field in DBeaver

edited Nov 23 '22 at 07:50

answered Oct 31 '22 at 13:00

Hamed Alizade

107
1
6

Easy, to the point. Brilliant :) – maxime1992 Feb 02 '23 at 17:29

score 4 · Answer 4 · answered May 29 '20 at 03:28

4

In my case I was using DBBeaver to connect to MariaDB on port 3306 however my container port was not mapped to my local 3306 port so I just needed to add the ports config into the docker-compose.yml

ports:
- "3306:3306"

answered May 29 '20 at 03:28

Oliver

99
4

score 4 · Answer 5 · edited Jan 11 '21 at 23:34

4

If it is possible change the permission in driver properties allowPublicKeyRetrieval to true. This is working for me.

AllowPublicKeyRetrieval=True

edited Jan 11 '21 at 23:34

DuDa

3,718
4
16
36

answered Jan 11 '21 at 23:15

Vagner Antonio da Silva

41
1
2

2

In my case I was getting a `Public Key Retrieval is not allowed` and this just solved the issue perfectly! – mdev Nov 25 '21 at 16:01

score 2 · Answer 6 · answered Oct 05 '21 at 10:23

Im my case, as I have different mysql containers and I need to set a different port mapping for each mysql container.

For example, I use the port setting below for one of mysql containers in the docker-compose.yml.

ports:
- "5500:3306"

So, I need to set the host: localhost, port: 5500 in DBeaver to connect the mysql container.

score 1 · Answer 7 · answered Aug 08 '22 at 01:10

1

It works fine when I fill the port mapping (-p) when initializing the container. ie:

docker run -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -v c:\Volumes\mysql:/var/lib/mysql mysql

answered Aug 08 '22 at 01:10

Ednilson Henrique Martins

11
2

Docker MySQL connection DBeaver

7 Answers7

Linked