How to connect locally hosted MySQL database with the docker container

Through docker-compose.yml I am able to run the application. Now we want to move the application to production, But we don't want to use the container database. So is there any way so that I can connect my local MySQL database with the application using docker-compose?

My docker-compose.yml looks like:

version: '3'
services:
  web-app:
    build:
      context: .
      dockerfile: web-app/Dockerfile
    ports:
      - 8080:8080
    links:
      - app-db

  app-db:
    build:
      context: .
      dockerfile: app-db/Dockerfile

    environment:
    - MYSQL_ROOT_PASSWORD=password
    - MYSQL_DATABASE=Optimize
    ports:
      - 3306:3306

Instead of app-db part I just want to connect to my locally hosted mysql database.

Just use host.docker.internal instead of localhost.

To make it work on Linux, you just need to start your Docker container passing the parameter --add-host host.docker.internal:host-gateway

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

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

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

The easist way is use host.docker.internal instead localhost.

MONGO_SERVER = {
'host': 'host.docker.internal',
'port': 27017,
'username': 'admin',
'password': '123456'}

If anybody is using mac os then please use docker.for.mac.host.internal instead of host.docker.internal as the environment variable.

locally I had MySQL running on port 3306 and MySQL container running on the same port 3306

You have to be sure that MySQL or whatever database service that you are using in your container is stopped in your local envirmint before connecting to it

In my case, I had MySQL running through Homebrew

brew services stop mysql

It works for me by adding network_mode in docker-compose.yml

version: '3'
services:
  aservice:
    image: image:latest
    ports:
      - "8080:8080"
      - "8787:8787"
    # Allow docker to connect to localhost
    network_mode: "host"