CODEWITHSUNDEEP
mysqlamazon-web-servicesubuntu
Gromit
Gromit

Reputation: 121

MYSQL refuses remote connection with error "ERROR 2003 (HY000)" and TCP error 111

I have installed MySQL 8.0.25 on Ubuntu 20.04 LTS (ARM) instance on AWS.
I can access it using 127.0.0.1 address locally, but can't access it remotely from another instance.

"Bind-address" was commented out originally, I uncommented it and changed to "0.0.0.0" (mysql service was restarted) - didn't help, so I commented it back.
"Skip-networking" is not in the cnf file.

I changed the port to 3307 just to be sure that I'm looking at the right cnf, and now MYSQL does listen to port 3307: enter image description here

locally I can connect using port 3307, but not remotely: enter image description here

Here are the iptables: enter image description here

I know that firewall works well, because if I remove port 3307 from the rules, the error is different: enter image description here

As you can see, TCP error 111 ("Connection Refused") became error 113 ("No Route to Host").

Telnet connection is refused with the same error:
enter image description here

I've rebooted the MySQL instance - no change.

Why would MYSQL refuse remote connection, if "bind-address" is commented out, and firewall is open?

Upvotes: 0

Views: 540

Answers (1)

Gromit
Gromit

Reputation: 121

@stdunbar's comment pointed me in the right direction.

The problem happened because I followed the MYSQL installation instructions at https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04 , and ran the security script while configuring MYSQL:

sudo mysql_secure_installation

For some reason, this script causes MYSQL to ignore the bind-address setting in the cnf file, while it still uses the port setting - very confusing!

When I installed MYSQL without running the security script, the "local address" of the MYSQL daemon in the netstat -lnp | grep mysql command changed from 127.0.0.1: enter image description here

to 0:0:0:0 : enter image description here

After this, MYSQL started to accept remote connections. Of course, I still had to create a remote user:

CREATE USER 'root'@'remotehostname' IDENTIFIED WITH caching_sha2_password BY 'newpassword';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'remotehostname' WITH GRANT OPTION;

Upvotes: 1

Related Questions