Hi - does anyone have any experience of connection to a remote MySQL database which is hosted on Digital Ocean? I can happily connect to a local copy of the database but can’t work out how to connect to the remote (live) system. I have tried ‘Connect directly’, entering the database name, server IP, port and then username/password but it just times out. There doesn’t appear to be any option to use SSH (which I use to connect when using DBeaver). Is it even possible? Any suggestions gratefully received!
Thanks, Richard
If you are using ssh couldn’t it work like here with PostgreSQL:
You have to connect to localhost and the port, which is connected to the external server through ssh.
ssh -L 63333:localhost:lotest@3306 192.168.178.32
Connection from localhost with port 63333 to external server, port 3306 as user lotest.
Port 3306 is default port for MySQL/MariaDB
If you try to connect to the external database through SSH you have to choose localhost and port 63333
Have created a connection to PostgreSQL with Base the same way - only a different port on the external server.
Hi Robert,
Thanks for the suggestion. In fact I’ve just tried it again, doing exactly the same as before (I thought) and it is working… so apologies but I have no idea why it didn’t work before and it is now! Anyway the good news is it’s fixed!
Thanks again,
Richard
Nope - talking rubbish, it’s not working - apologies. I think the problem is that when trying to connect to a MySQL database there does not appear to be any way of using an SSH tunnel. The database I am trying to access only allows SSH access unfortunately.
How did you create the tunnel? Please also show the 3. step of the connection wizard for the direct connection to MySQL.
Tested it: Installed MariaDB on another PC2, couldn’t connect direct from my own PC1.
Database works directly on PC2.
Then connected to PC2 through SSH:
ssh -L 63333:localhost:3306 192.168.178.32
Now I could connect from PC1 to the database through localhost and port 63333. See screenshot
Could input data - so no problem here.
To my knowledge, the direct driver connector doesn’t support SSL connections (unless that changed recently and I missed it), which is why Robert suggested setting up a SSH tunnel mapping your remote mysql instance to a local IP address and then using that to set up a local direct mysql connector connection in the ODB.
Thanks, that’s very helpful - it’s beginning to make sense. I have now set up an SSH tunnel using Terminal on my Macbook with the command:
ssh -L 6333:127.0.0.1:3306 :<remote_ip>
That gets me a connection to the remote server and seems to be working fine. I can get in to mysql from the command line.
I then try to connect to an existing database in Libreoffice:
but the connection times out. Is there a way to get more details of the reason it is failing? I just get SQL Status HY000, Error code 2002.
Thanks, Richard
Connection to the server will be established by the SSH tunnel. You have to connect to the “local end” of the tunnel.
Server → ‘localhost’
And, please, don’t connect with port 6333. This port isn’t a port for free use. Take a port between 49152 and 65535.
Of course, obvious when you point it out! That works and I have changed port number as you suggested. That’s brilliant - very many thanks!
If you’ve mapped the remote instance to 127.0.0.1 or localhost, that’s what you should be putting in the address field rather than the remote IP address. I seem to recall that macOS doesn’t like localhost as the host address and that you should use 127.00.1 instead.
I have to say that I haven’t had to do this in a while (the whole remote mapping thing) , so might have got things mixed up. Remember that you also need to keep the terminal app running with the SSH connection mapping otherwise the remote connection will be dropped.
Hi - yes, it seems to work with 127.0.0.1 and localhost, and yes, I do need to keep the terminal app running, but at least it works.
Thanks to all for your help.
Richard
1 Like