Testing a Remote MySQL Connection
If you are seeing a different error “Access denied for user (using password: YES)”, please see: MySQL Rejecting Correct Password
To test a remote MySQL connection in Linux terminal, simply replace username
and hostname_or_ip
with your own.
mysql -u username -h hostname_or_ip -p
Or via Command Line in Windows:
telnet 192.16.8.1.22 3306
If you are getting an error when trying to connect to your remote MySQL server
ERROR 2003 (HY000): Can't connect to MySQL server on '192.16.8.1.22' (111)
or in Windows Telnet
Connecting To 192.16.8.1.22...Could not open connection to the host, on port 3306: Connect failed
1. Edit MySQL config
You may need to comment out bind-address
in the MySQL config file mysqld.cnf
.
Open mysqld.cnf
with nano editor.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Press CTRL
+ W
and search for bind-address
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
The above line is telling MySQL to only accept local connections (127.0.0.1 / localhost).
If you know the IP that you are trying to remotely connect from, you should enter it here to restrict remote connections to that IP.
If you have to allow all IP addresses to connect, you can comment out the line by adding #
before it so it looks like:
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
Note: It is not good security practice to leave your MySQL server accessible to any IP, so consider this a temporary measure. You can also set the host value for your MySQL user to only accept connections from a specific IP. Please see: Understanding MySQL Users and Hosts
Restart the MySQL service for changes to take effect.
sudo service mysql restart
2. Check Firewall
If you still can’t connect, check if there is a firewall configured on your server. The most common firewall for Ubuntu server is ufw
.
Check if ufw
is enabled:
sudo ufw status
If it’s enabled, you should see some rules:
To Action From
-- ------ ----
OpenSSH ALLOW Anywhere
Apache Full ALLOW Anywhere
OpenSSH (v6) ALLOW Anywhere (v6)
Apache Full (v6) ALLOW Anywhere (v6)
If MySQL is not listed, add a rule for it.
sudo ufw allow mysql
There should be a rule for MySQL:
To Action From
-- ------ ----
OpenSSH ALLOW Anywhere
Apache Full ALLOW Anywhere
3306 ALLOW Anywhere
OpenSSH (v6) ALLOW Anywhere (v6)
Apache Full (v6) ALLOW Anywhere (v6)
3306 (v6) ALLOW Anywhere (v6)
Restart the ufw
service:
sudo service ufw restart
Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.
I was able to connected to mysql server remotely and then in all of a sudden it stopped working after updates and upgrade. Using this instruction to check the firewall and add rules to firewall helps me back connected again. Thank you very very much
thanks. work for me
Hi there,
thanks a lot for this tuto to solve.
In my case it did not work :/
I have tried many things already
My user has all privileges enabled
SSH is enabled
UFW is installed and running
Ports are set with allow in from anywhere on nginx http, mysql and even server firewall
I have restarted each time after settings actions
mysql.cnfd is also set with bind address 0.0.0.0
I’m not sure so far if there is still one or few things I could do to solve this lool
May I have a few tips or help please?
Again thanks for all
I’m still getting the same error, after # out the bind-address line in every version of mysql.cnf or my.cnf I can find. Confirmed via use status 3306 is set to ALLOW. Kind of perplexed here. I can connect with MySQL workbench from inside my network, but not from outside. Any other thoughts? Using MySql8 and Ubuntu server 20.04.
ufw save me!
Thanks
Awesome! It’s work.
Thank you!
Implemented the above steps and the problem was resolved immediately. Thank you so much for the guidance.
Thanks. Many “How-To’s” on the web don’t mention the default IP binding thing in MySQL.
Thanks for all…
Works Fine!!!
Still get this
Can’t connect to MySQLServer on ip adress(10600)
it’s working now ,thanks your share
muy bueno gracias
¡A ti!