ubuntu 18.04 ufw how to allow local 3306 connections to mysql?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

-s in this case is source address