Also, if there’s a firewall installed, one should open the port on the firewall;

Ubuntu:

sudo ufw allow 3306/tcp
sudo service ufw restart

Hey there. Just stopping by to tell you that I’ve been working hard on this all day, and your comment is the only thing I found that worked.

THANK YOU SO FRIKKING MUCH I LOVE YOU <3

If you followed the guide from http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html and got to this thread and still not working,

please note that if you are as stupid as me (or just woke up) and blindly follow the guide and edit /etc/mysql/mysql.conf/mysql.cnf , you will write bind-address = your external ip right below skip-external-locking. And you will fail to see that it is redefined, fewer lines down, to bind-address = 127.0.0.1

TLDR: JUST MAKE SURE that you don’t have multiple bind-address lines in mysql.conf.d and make sure that they’re all commented out

I’m a newbie and this gave me the idea to change my user’s hostname to ’%’. thanks.

@bruns.nicholas Thank you !!!
Could you please help because I can'f find such documentation about it…
I’m still stuck with a

The SSH Tunnel has unexpectedly closed.

I did the following:

ssh -NC me@remote_ip -p my_custom_port -L 9999:localhost:3306

Then on Sequel:

- Mysql Host = 127.0.0.1
- Username = root (I'll create a dedicated user later :p)
- Password = my_password
- Database = database_name
- Port = 9999
- SSH Host = localhost

With best regards,

J.

[UPDATE]
I guess I found something…

debug1: Connecting to 127.0.0.1 [127.0.0.1] port 22.

I have set remote SSH to run on a custom port, not on the default port 22.
So, within Sequel, how can I set the port to my custom one for the SSH Host entry?
Or maybe I’m totally wrong…
Any help is welcome.

J.

[UPDATE 2]
I found something (again).
First I missed some field within Sequel… Now it’s ok.

But when I attempt to “Test connection” I have the following:

SSH port forwarding failed

Unable to connect to host 127.0.0.1 because the port connection via SSH was refused.

Please ensure that your MySQL host is set up to allow TCP/IP connections (no --skip-networking) and is configured to allow connections from the host you are tunnelling via.

You may also want to check the port is correct and that you have the necessary privileges.

Checking the error detail will show the SSH debug log which may provide more details.

MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

It’s weird because within my my.cnf I have the following line saying:

Instead of skip-networking the default is now to listen only on localhost which is more compatible and is not less secure.
bind-adress = 127.0.0.1

(I don’t have any “skip-networking” entry ..)

We are close…

J.

[UPDATE 3]
I found!

I keep all the steps, maybe it will help someone.
Step by step:

Terminal

ssh -NC remote_username@remote_ip [-p my_custom_port] -L 9999:localhost:3306

[-p mycustomport] only if you set a custom port to access with SSH ; if not, leave empty and default port 22 will be used.

Sequel Pro:

- Name = Connection's name (it's up to you)
- Mysql Host = 127.0.0.1
- Username = mysql user (it can be root or a custom user)
- Password = password for that user
- Database = database name or "mysql" as default
- Port = 3306 (here was one of my mistakes)
- SSH Host = remote ip
- SSH User = username used to connect with SSH
- SSH Key = target your id_rsa.pub file or it will try to find it to it's default location
- SSH port = by default it's 22 but you could have set a custom one...

Regards,

J.

Awesome post Bruns! Thank you for posting this, saved me a lot of time and effort and no need to go mucking around with the settings. This worked like a charm using sequel pro and my SSH user.

Sweet!!!

@bruns.nicholas : Thanks for pointing this out. Had tried a great many suggested ways to get access to mysql remotely, and nothing worked. Doing it via SSH was instant and super easy. Cheers.