Need assistance with accessing mysql database using node.js

Question

I am trying to run a simple node sample for access a mysql db and am getting the following error-Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'ubuntu.local' (using password: YES)

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '192.168.0.12',
  user     : 'root',
  password : 'password',
  database : 'app'
});

connection.connect();

connection.query('SELECT * from users', function(err, rows, fields) {
  if (err) throw err;

  console.log('The solution is: ', rows[0]);
});

connection.end();

The above is the code for accessing the mysql using node.js.Any suggestions as to what I may be doing wrong, thanks in advance.

Answer 1

I had a similar problem where I was getting the same error.

The answer for me ended up being that I was using a different port. By default, mysql expects a port of 3306, but I was using MAMP, where the MySQL server port was specified as 8889. As soon as I added a port definition to my database connection, it was fine:

var connection = mysql.createConnection({
    port: 8889,
    user: 'root',
    password : 'password',
    database : 'my_database'
});

NOTE: This is for Mac using MAMP, haven't tried it on ubuntu or Windows.

Answer 2

I was getting the exact same issue. I was not able to connect via the mysql module in nodejs, but was able to connect via the mysql client in the terminal (mysql -u root -p1111 -h localhost mydb)

It turns out, the mysql module converts "localhost" to 127.0.0.1. This would not work via the (mysql -u root -p1111 -h 127.0.0.1 mydb). See Issue 734

So I had to set a password for 127.0.0.1 in MySQL and it solved the problem.

Instructions

Connect to mysql via the console:

mysql -u root -p mysql

And Run

SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('Add-Your_password-here');

Source