How to connect some graphical sql tool to database via ssh AND docker

Question

I would like to connect my GUI database tool to my SQL Database via ssh and docker. Currently a can connect do database via terminal using ssh user@host and some docker-compose exec mydb ... command. But then, it's of course a command line access to db.

My needs / my question

Is there a way to have my GUI database tool to connect to that DB that way ? To be more explicit, i would like to connect to that db without any server change of any kind (really important point). So with only local configuration change. Maybe we can use the same way i can use by hand ?

What i tried

I already tried to use some ssh configuration in my config file like ProxyCommand in my ssh config file, but these command are executed in my computer...so i don't find any way to success with this.

I also searched many times anyone with the same will without success.

Somebody with great idea ?

Answer 1

There are some things to consider here.

1. make sure the port mapping is valid in the docker-compose.yaml file, it should be something like

services:
  ...
  database:
    ...
    ports:
    - "3306:3306" # 3306 is the default Non-SSL port for MySQL database images

2. make sure the machine you are running the database container on has no firewall rule to block incoming traffic
3. make sure the in the database, the user you are trying to connect with has a whitelisted location. for example, if you want to be able to connect as root from anywhere, you need check the user table in the mysql database, and it should look something like:

mysql> select User, Host from user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> 

In this case, the root user can connect to this particular DB from anywhere, because of the wildcard (%) host.