Mysql connection with port forwarding

Questions
1

Hi

I am trying to establish a connection to a remote mysql database.

I am running n8 docker.

I have created a tunnel on the n8 server to the remote mysql and have checked this and am able to connect manually.

This is what my .ssh/config file looks like:

Host myremotename
Hostname myremotename.com
User root
IdentitiesOnly yes
IdentityFile /root/.ssh/id_rsa
LocalForward 3307 127.0.0.1:3306

The inside n8n UI i go and create a connection to the mysql with the following values:

Host: localhost (I have tried with 127.0.0.1 as well)
Database: myDB
User: myuser
Password: mypassword
Port: 3307 (I have tried with both 3307 + 3306)

I then created a new workflow with MySQL and did an execute Query with a simple select and got ERROR: connect ECONNREFUSED 127.0.0.1:3307

I also updated my docker-compse.yml file to include the port 3306 as follows:

image
image680×651 114 KB

So any help here would be much appreciated!!

Thanks,
Mike

2

Hey @Surge.Media,

I suspect the problem here is while the host is listening on 3306 the n8n docker container won’t be which is why it is failing.

Can you try using 172.17.0.1 as the IP and see if that works?

3

Hey @Jon

Thanks for the quick response.

I tried this with port 3306 and got the following error: ERROR: Connection lost: The server closed the connection.

Error: Connection lost: The server closed the connection.
    at Object.createConnection (/usr/local/lib/node_modules/n8n/node_modules/mysql2/promise.js:241:31)
    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/MySql/MySql.node.js:220:41)
    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/src/Workflow.js:492:37)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/src/WorkflowExecute.js:424:62
    at processTicksAndRejections (internal/process/task_queues.js:93:5)

I also tried with port 3307 and got this error: ERROR: connect ECONNREFUSED 172.17.0.1:3307

Error: connect ECONNREFUSED 172.17.0.1:3307
    at Object.createConnection (/usr/local/lib/node_modules/n8n/node_modules/mysql2/promise.js:241:31)
    at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/MySql/MySql.node.js:220:41)
    at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/src/Workflow.js:492:37)
    at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/src/WorkflowExecute.js:424:62
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
4

Ah just looked at your config again that appears to be for Traefik for port 3306. What port are you using for MySQL?

When you are connecting manually are you first running ssh myremotename then from there running the mysql command?

5

I am creating a tunnel with ssh -Nv to initiate the tunnel from port 3306 to 3307 (my understanding of this is not great, but this is how i have set it up for other services we use)

And yes when connecting manually, ssh -Nv on the config file is running so the tunnel is created

6

Alright so in theory then we should be using 172.17.0.1 which is the default docker IP for the host machine and then it should be 3307 for the port.

When you did the test from n8n did you have the tunnel running on the host?

7

Yes the tunnel is running.

I am also unfamiliar with docker - is there somewhere to provide more detailed logs ?

8

You can connect to the docker container using the docker exec command but I suspect the issue is more around the SSH tunnel, What if you change LocalForward to LocalForward 0.0.0.0:3307 127.0.0.1:3306 it could be that for some reason the docker instance is not able to connect to it and that will sort it.

9

That did the trick!!

Well done @Jon and thank you so much for the assistance and quick response - it is very much appreciated