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

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.