How to connect to a database inside a docker

If you host your database on a VPS and using docker. You do not want to map your database port to the VPS port. Example your docker-compose.yml is:

version: '3.1'
services:
  your_db:
    image: mysql:8.0.31
    container_name: your_db
    restart: always
    networks:
      main_default:
    env_file:
      - ./db.env
    volumes:
      - ./volumes/your_db:/var/lib/mysql

And you want to connect to this database from your local machine. Here is the way:

With above docker-compose file, we can't connect to to mysql database from your local machine, also from the VPS. However, if we have another docker container also in that docker compose file, it can connect to mysql using port 3306.
Here's the thing we will do:

• We will add new docker container with ssh server installed. We open ssh port to a random port. We also need to enable tcp forwarding in ssh configuration. I already created an image and push to docker hub, let's use my image and update above docker-compose.yml to

version: '3.1'
services:
  ssh:
    container_name: ssh
    image: trngon/alpine-ssh
    ports:
      - "1234:22"
    volumes:
      - ./your-ssh-dir:/root/.ssh
    restart: always
  your_db:
    image: mysql:8.0.31
    container_name: your_db
    restart: always
    networks:
      main_default:
    env_file:
      - ./db.env
    volumes:
      - ./volumes/your_db:/var/lib/mysql

Now, you can use the tool like Sequel Ace or dbeaver to using using ssh tunnel option. Put your database user/password as you set. The hostname is your_db (use your own if you changed it). Then the ssh host is your vps IP, user name is root and the key is the key you mapped above.The port is 1234 (if you do not change)

If you want to customize the ssh image, here is the docker file that you can re-use

FROM alpine:3.18.5

LABEL authors="Tran Trung Ngon"

RUN apk add --update --no-cache openssh

# You can remove it if do not want to add a key to this image
COPY ./ssh/authorized_keys /root/.ssh/authorized_keys

RUN apk add openrc && rc-update add sshd

COPY ./ssh/sshd_config /etc/ssh/sshd_config

COPY entrypoint.sh /entrypoint.sh

EXPOSE 22

ENTRYPOINT ["/entrypoint.sh"]

entrypoint.sh

#!/bin/sh
ssh-keygen -A
/usr/sbin/sshd -D -e

sshd_config

Port 22
AuthorizedKeysFile  .ssh/authorized_keys
AllowTcpForwarding yes
GatewayPorts no
X11Forwarding no
Subsystem   sftp    internal-sftp