How to connect locally hosted MySQL database with the docker container

Solution 1:

Just use host.docker.internal instead of localhost.

Solution 2:

Find the host machine ip in the docker network. If you use docker-compose.yml version: "3" it's probably that that IP is: 172.18.0.1, but confirm it searching for the "Gateway" of your container (your host):

docker inspect <container-id-or-name> | grep Gateway
"Gateway": "",
            "IPv6Gateway": "",
            "Gateway": "172.18.0.1",
            "IPv6Gateway": "",

So inside your docker application point to MySQL as this: 172.18.0.1:3306 (maybe in a configuration file). Take into account that that IP is fixed as long as the docker network still the same (the network is created by docker-compose, and it is not removed unless you do docker-compose down)

Also, check that your MySQL is listening to all of its interfaces. In your my.cnf search for bind-address that should be 0.0.0.0 (consider security issues if your server has public IP).

As an alternative you can bring to the container the same networking as your host, in order to share the localhost, so the container will find mysql there. Use network mode as "host":

version: '3'
services:
  web-app:
    build:
      context: .
      dockerfile: web-app/Dockerfile
    ports:
       - 8080:8080
    network_mode: "host"

Then, point in your hibernate.properties to mysql as this: localhost:3306

Solution 3:

Allow permission using mysqld.cnf

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address            = 0.0.0.0

save your file and restart mysql server

sudo systemctl restart mysql.service

login to Mysql

mysql -h localhost -u root -p
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
FLUSH PRIVILEGES; 
EXIT;

Find your IP

ip addr show

And use it in your docker container 192.168.x.x

Solution 4:

Problem:

We had a same scenario where we wanted to setup a Development local machine setup for the services where a developer can just make docker-compose up -d and use all the latest dev builds in his local machine.

But we started facing issues while connecting our tomcat based web service configured as service in the docker-compose.yml to **mysql-8.0** service within the docker-compose. So as alternative and quick fix we thought to use local mysql configured on the local laptop with Ubuntu and docker-compose.

Our tomcat-application was using a application-dev.yml file with jdbc config variable to connect to the mysql, say something like

    url: jdbc:mysql://db:3306/abcDB?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: abcderf

Docker-compose.yml

version: '3'
services:
      db:
        image: mysql:8.0
        container_name: db
        restart: unless-stopped
        ports:
          - "3306:3306"
        environment:
          - MYSQL_ROOT_PASSWORD=abcd
          - MYSQL_PASSWORD=abcd
          - MYSQL_DATABASE=abcdDB
        volumes: 
          - ./sql-dump:/docker-entrypoint-initdb.d
        command: '--default-authentication-plugin=mysql_native_password'
      
      frontend:
        container_name: frontend
        image: abc/abc:frontend-1.1.36
        depends_on:
          - backend

      nginx:
        container_name: nginx
        image: nginx:1.19-alpine
        links:
          - "frontend"
          - "backend"
        volumes:
          - ./proxyConf:/etc/nginx/conf.d 
        ports:
          - "80:80"
          - "443:443"
      backend:
        container_name: backend
        image: abc/abc:server-3.1.161
        restart: unless-stopped
        env_file: .env
        environment:
          - SPRING_PROFILES_ACTIVE=devtest
          - DB_HOST=db:3306
          - DB_NAME=abcDB
        depends_on:
          - db

Solution worked for us:
As we wanted to connect our docker-compose tomcat service from the local mysql we tried the below routes.

docker inspect <container-id-or-name> | grep Gateway which returned the ip Gateway ip value as`

"Gateway": "",
        "IPv6Gateway": "",
        "Gateway": "172.18.0.1",
        "IPv6Gateway": ""

used the above Ip in the application-dev.yml as below ( URL post resolve of the ${DB_HOST} looks something as below But the IP changes to next highrt post the docker-compose down ex: 172.19.0.1)

    url: jdbc:mysql://172.18.0.1:3306/abcDB?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: abcderf

Modified docker-compose.yml

version: '3'
services:
      frontend:
        container_name: frontend
        image: abc/abc:frontend-1.1.36
        depends_on:
          - backend

      nginx:
        container_name: nginx
        image: nginx:1.19-alpine
        links:
          - "frontend"
          - "backend"
        volumes:
          - ./proxyConf:/etc/nginx/conf.d 
        ports:
          - "80:80"
          - "443:443"
      backend:
        container_name: backend
        image: abc/abc:server-3.1.161
        restart: unless-stopped
        env_file: .env
        environment:
          - SPRING_PROFILES_ACTIVE=devtest
          - DB_HOST=172.18.0.1:3306
          - DB_NAME=abcDB
        depends_on:
          - db

and Also updated the bind-address = 0.0.0.0 in the /etc/mysql/mysql.conf.d/mysqld.cnf and from the mysql command line allow the root user to connect from any Ip.

Below were the only commands worked for mysql-8.0 as other were failing with error syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'abcd'' at line 1

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
UPDATE mysql.user SET host='%' WHERE user='root';

Restart the mysql client

sudo service mysql restart