MySQL remote connection refused

Question

I'm trying to connect my MySql running on my VPS (let's say hostname db.example.com).

The issue is: on db.example.com, I can connect mysql locally (from the db server itself), but I can't do remotely (from my dev machine).

How should I check the settings?

• MySQL server: ubuntu 18.04
• dev machine: macOS catalina

I can ssh to db.example.com and checked by running following commands.

on the server db.example.com(ubuntu 18.04):

$ mysql -u satrex -p
> success

mysql> select user, host from mysql.user where user = 'satrex';
+--------+------+
| user   | host |
+--------+------+
| satrex | %    |
+--------+------+

$ sudo ufw status
>Status: active
To                         Action      From
--                         ------      ----
3306                       ALLOW       Anywhere
Apache Full                ALLOW       Anywhere
3306 (v6)                  ALLOW       Anywhere (v6)
Apache Full (v6)           ALLOW       Anywhere (v6)

$ sudo lsof -i:3306
> COMMAND PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  597 mysql   43u  IPv4  16358      0t0  TCP *:mysql (LISTEN)

$ sudo cat /etc/mysql/mysql.conf.d/mysqld.cnf 
>
[mysqld]
port            = 3306
....
# bind-address                = 127.0.0.1
bind-address            = 0.0.0.0

then I tried to connect from dev machine.

on dev machine (macOS catalina)

I can connect to the server via telnet.

$ telnet db.example.com 80
> Trying 160.16.xxx.xxx ...
Connected to db.example.com.

But refused on port 3306.

$ telnet db.example.com 3306
> Trying 160.16.xxx.xxx ...
telnet: connect to address 160.16.xxx.xxx: Connection refused
telnet: Unable to connect to remote host

Same as mysql client, either.

$ mysql --host=db.example.com --user=satrex --password
> Enter password:
> ERROR 2003 (HY000): Can't connect to MySQL server on 'db.example.com:3306' (61)

I'm wondering that I'm missing fundamental settings to get connected. It should be in front of configuring MySQL itself.

Now db.myself is listening the port 80 as ufw shows, on the other hand, the port 3306, ufw shows as listening but no luck.

Answer 1

First you need to check your Security group if it EC2 Instance is allowed to accessible 0.0.0.0 (Globally)

Then on your SQL EC2 instance check bind address sudo grep -R bind /etc/mysql if it looks like this then we need to modify something

go to this path /etc/mysql/mysql.conf.d and modify this file sudo vim mysqld.cnf

bind address should be bind-address = 0.0.0.0

finally its time to restart mysql service

sudo systemctl restart mysql.service

For me this solution is working if it works for you just vote up thanks if you still find something difficult look into this question