Link to home
Start Free TrialLog in
Avatar of Durga Ojha
Durga OjhaFlag for India

asked on

Enable mysql connection from outside to access over TCP/IP

How to enable Mysql server access through TCP/IP
where mysql installed in Centos machine.,.I am trying to access using mysql workbench it through TCP/IP but throwing error .. attached screenshot.
Where as when I select TCP/IP over SSH .. I can able to connect .
mysql-TCPIP.jpg
Avatar of arnold
arnold
Flag of United States of America image

My.cnf my.ini us where you configure port/bind ip.
Run
Netstat -an | grep ":3306"
What are the results?

Make sure you enable access on port 3306 if you have firewalld or iptables enabled.
'root' is normally only allowed thru 'localhost' connections.  That's why it works when you SSH to the server.  For a remote connection, you need to create a user with privileges from remote hosts.  More info here:  https://dev.mysql.com/doc/refman/5.7/en/user-account-management.html
Avatar of Durga Ojha

ASKER

Thank you all
I just tried all also by allowing port 3306 through iptables, still I was not able to connect
What I did is just commented out bind address from /etc/my.cnf and now able to connect by both TCP/IP over SSH and only TCP/IP
As Dave pointed out, make sure you have a user on the MySQL that is authorized to login either from a specifically designated host or from anywhere (%)

Since you are connecting to an external, I would suggest for security administration of MySQL should left to the local user account (look at ssh tunnels where you can remotely ssh to the server while at the same time using MySQL GUI tools access the remote MySQL through the ssh tunnel by reference.
ssh -L Localport:localhost:3306 user@mysqlserver
Once the above session establishes,
Using any MySQL GUI tool your connection to the remote MySQL is achieved by using localhost:Localport as the destination which will be passed through the ssh tunnel to the remote MySQL server. Consider this is secures the access to MySQL in a similar way a VPN if setup would.
There are GUI ssh for other platforms, commonly tunnels are under the ssh settings/configs.
-L local initiating connection from the local system
-R remote tunnel, this allows a path from the remote system via the tunnel, back to the local system. Note these terms/options are based in relation to the system from which the ssh connection is initiated..
also on your router you have to forward port 3306 from WAN to the ip of the computer that holds mysql
Keep in mind, just opening port 3306 to the world means anyone snooping on any network near yours will have your login instantly.

If you must do this...

1) Secure your mysql instance with an SSL cert. https://LetsEncrypt.org provides these for free.

Steps are complex. Refer to your MySQL version docs for details.

2) Create a GRANT for some user, like admin for all IPs.

3) Verify you can do something simple like "SHOW DATABASES" on same machine where database lives as the admin user.

Note: At this point, you'll know your admin user GRANT is working.

4) Now get your admin user working on your local machine.

First step will be, as David Johnson suggested, ensuring port 3306 is open.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.