1

If I run this command:

mysql -u root -p -h 127.0.0.1 -P 3306

with the correct password (yes, I have the correct password) it says access denied

I run the exact same thing, just disable ufw first sudo ufw disable

then the above command, and it works. But: now ufw is disabled :S

What I really want to do is connect via mysql workbench using ssh but without disabling ufw altogether, and without allowing none ssh access to my mysql installation?

these are the current settings that work for me with mysql-workbench, but only if ufw is disabled. As soon as I enable ufw, this stops working. And I dont want to just open 3306 ports to everyone either using ufw. I want to close everything except ssh, and let me connect via ssh to the 3306 port of mysql.

enter image description here

  • Is all this on your locahost/dev machine? Cause if it is, this connection method that you use appears to create an SSH tunnel from what I understand. But if it's all localhost, why do that? Why not just have MySQL listen only on 127.0.0.1. External connections to 3306 will then fail... And you don't even need a firewall for that. – fevangelou May 31 '19 at 8:42
  • Can you add the exact error message of the above mysql command? When you disable ufw, is it on your local machine or on the remote machine? The mysql command runs on the local or remote machine? – RalfFriedl May 31 '19 at 9:18
5
+50

To allow connection via loopback adapter only on port 3306 you can run the following:

sudo ufw allow from 127.0.0.1 to 127.0.0.1 port 3306 proto tcp
| improve this answer | |
0

iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT

-s in this case is source address

| improve this answer | |
  • 2
    An answer that involved the questioner's preferred firewall management system would be more helpful. – womble Apr 26 '19 at 0:11
0

I discovered this problem and a fix just the other day... so you're in luck! This is NOT a firewall issue, but an authentication plugin issue.

The default configuration does not allow "root" to log into the local mysql instance unless it is using file sockets. Check the authentication plugin by logging into the local machine and doing mysql -u root -p (without specifying the host) and doing the following:

use mysql;
select user, plugin from user;

If you notice, the user root@localhost is using the auth_socket plugin instead of the mysql_native_password plugin. Simply changing it to use the mysql_native_password plugin, and restarting will fix your problem:

use mysql;
update user set plugin='mysql_native_password' where user='root';
exit;
sudo service mysql restart

At this point, you can crack open the mysql workbench, and get some work done.

| improve this answer | |
  • You might not need to restart mysql, and instead simply do a flush privileges; or some such, but I didn't bother with my environments. It was just easier for me to restart mysql. – TheCompWiz Jun 3 '19 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.