Question
How to Allow Remote MySQL Database Connection
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×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
I also ran the command below, after yours
sudo service mysql restart
And voila, it works normally now
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.
trying to work with heidisql its not working and workbench keep freezing https://www.digitalocean.com/community/questions/unable-to-connect-to-mysql-server-using-heidisql
No need to change MySQL conf.
I’ve tried with HeidiSQL 9.2 @ Win7 Ultimate , and working well.
Settings
Networktype: MySQL (SSH tunnel)
Hostname / IP : 127.0.0.1
Prompt for credentials: unchecked
User: root
Password: [root_password]
Port: 3306
Compressed client/server protocol: unchecked
SSH Tunnel
plink.exe location: C:\Program Files (x86)\PuTTY\plink.exe
SSH host + port: [your_droplet_ip_address]
Username: root
Password: -leave empty-
plink.exe.timeout: 5
Private key file: PATH_TO\private_key.ppk
Local port: 3306
This worked for me https://rbgeek.wordpress.com/2014/09/23/enable-remote-access-of-mysql-on-ubuntu/ Enable remote access of MySql on Ubuntu Posted by rbgeek on September 23, 2014
Also worked for me but you also need to know that in my case ’/etc/mysql/my.cnf’ was empty and in fact you are looking for ’/etc/mysql/mysql.conf.d/mysqld.cnf’ so that you can remove the bound IP
#bind-address = 127.0.0.1
Then it will work
this works for me as well by commenting bind IP from the file /etc/mysql/mysql.conf.d/mysqld.cnf
Hi, all
We have an existing tutorial on how to allow remote access to MySQL which you can check here:
https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql
Hope that this helps!
Regards,
Alex
It’s not sucure way. You can connect to MySQL through ssh tunnel - it’s more secure. Or if it doesn’t matter you have to wtite bind-address = <vps-ip-address> or bind-address = 0.0.0.0