How do I establish MYSQL connection to AWS ec2 instance from local machine?

Question

I'm finished with my project and I'm trying to deploy it to AWS. I have an ec2 instance as my webserver with the following configuration details:

• NodeJS using port 5000
• PM2 (keeping server alive at all times)
• NGINX as web server reading from my build file
• MySQL within ec2 instance as my database. (using port 3306)

My problem is I'm having trouble establishing a connection from my local machine to my AWS ec2 instance that has the MYSQL db inside of it. I opened up MYSQL workbench and I can connect to it just fine there but when I try and establish a connection string to the DB from node.js it gives me an error.

I was able to successfully connect to the DB within MYSQL workbench but how can I connect to it now from nodejs connection string?

What I already tried was the following:

1) In AWS security group opening up TCP Rule for all incoming traffic at port 5000
2) In AWS security group opening up MYSQL/Aurora Rule for all incoming traffic at port 3306
3) Granting all privileges on . to user and flushing and restarting mysql server.

Error it gives me in the console.

`{ Error: connect ECONNREFUSED 14.54.xxx.xx:3306
    at Object._errnoException (util.js:1019:11)
    at _exceptionWithHostPort (util.js:1041:20)
    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1175:14)
--------------------
  code: 'ECONNREFUSED',
  errno: 'ECONNREFUSED',
  syscall: 'connect',
  address: '14.54.xxx.xxx',
  port: 3306,
  fatal: true }`

Here is my code trying to establish the connection:

```var mysql = require("mysql");

// Local Works just fine

// var connection = mysql.createConnection({
//   host: "localhost",
//   user: "root",
//   password: "xxx",
//   database: "devdb",
//   charset: "utf8mb4"
// });

// Production Connection to AWS MYSQL instance (stuck on)

var connection = mysql.createConnection({
  host: "14.54.xxx.xxx",
  port: "3306",
  user: "jordan",
  password: "xxx",
  database: "productiondb"
  charset: "utf8mb4"
});

// Test the db connection

connection.connect(function(err) {
  if (err) {
    console.log(err);
  } else {
    console.log("Connected!");
  }
});

module.exports = connection;
```

I expect to be able to successfully connect to the db instance from my NodeJS

Funke

Answer 1

Actually I think I just figured it out.

So the default mysql configuration file has the ip binded to 127.0.0.1. However, I changed the binding to my ec2 public ip and also changed the default "mysql" to "jordan" and I saved the configuration file, restarted the mysql service and it now works.

Thank you for the suggestions I'll write this down in my documentation to check for in the future.

Funke

could you please share how you found and edited the mysql configuration file? I have the exact same problem and it's driving me crazy!! none of the other solutions I've found have worked, I think this may be my solution. – Mukul

Answer 2

Make sure again, I think your security groups have something wrong, maybe your server listening internally so It's happening. Go your EC2 security group and select Inbound and add rules as type=mysql, proto=tcp, port range=3306, source=0.0.0.0/0,::/0 (allow all)

Undervalue

Answer 3

There are a couple of reasons due to which this might be happening -

• Configure MySQL database

   #start MySQL server sudo service mysqld start
   #run configuration sudo mysql_secure_installation

In the prompt, follow the following steps:

Enter current password for the root account: press Enter key
Set root password? Y
New password: yourpassword
Re-enter new password: yourpassword
Remove anonymous users? Y
Disallow root login remotely? n
Remove test database and access to it? Y
Reload privilege tables now? Y

• If you are using RDS then you will have to provide NAT access to the VPC which holds your database. For more info please refer here

Caplan