Error: connect ECONNREFUSED & Open MySQL Port

Challenge

We need to connect to MySQL from an external location.

Problem

It does not allow access.

You may have an error that says:

Error: ER_HOST_NOT_PRIVILEGED: Host 'xx.xx.xxx.xxx' is not allowed to connect to this MySQL server
or
Error: connect ECONNREFUSED
or
Error: connect ETIMEDOUT

Solution

1. Make sure your VPC Security groups have the right privileges, we'll go into detail below.
2. Is the server listening internally only?
3. You have the right IP address of your server and you are using the right user account.

Regarding the security groups, I'll use AWS to illustrate the answer.

You will get an error like this one: Error: connect ETIMEDOUT if you don't have the right inbound access enabled. I will let ALL access granted for the sake of troubleshooting in the example.
To fix the error, go to your Security Group of your EC2 and select Inbound, then Add a new rule:

• Type: MySQL/Aurora
• Protocol: TCP
• Port Range: 3306 (this one is the default, if you have it on a different one select that one.)
• Source: 0.0.0.0/0, ::/0 (this will allow access from any IP, not too secured tho...)

Now click Save, give it 2 minutes and try to connnect again. Below is an image of how it looks.

Is the server listening internally only? This is a very important question, we can see what IP address is MySQL listening on by typing:

sudo netstat -plutn | grep 3306

If 127.0.0.1, then you will have to configure it to listen on all interfaces instead (127.0.0.1 and your droplet's IP address). Edit /etc/mysql/mysql.conf.d/mysqld.cnf and set bind-address to 0.0.0.0:

bind-address = 0.0.0.0

it might be that the configuration file is in /etc/mysql/my.cnf, check there if you can't find it above.

Then, restart MySQL: sudo service mysql restart

Next: Error: ER_HOST_NOT_PRIVILEGED means that the username that you are using doesn't have the right access or doesn't exist. Here is how to fix that:

Enter into MySQL:
mysql -u root -p

and execute the following query (substite username and password with yours

This creates a new username:
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

This gives full access to that username:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

Now reload the privileges:
FLUSH PRIVILEGES;

now on your DB config make the changes, for example:

host: 'yourServerIP',
user: 'username',
password: '123456',
database: 'mySchema'

That should fix that error.

Regarding Error: connect ECONNREFUSED as soon as I fixed those two above, that one disapeard.

Another one is: Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@...

That happened when you tried to log in with root. I won't get into detail since loging in with root for your app is not a good idea in the first place.

I hope this helps you,
Arturo