NodeJS/mySQL -用户'root'@'localhost'被拒绝的ER_ECCESS_DENIED_ERROR访问(使用密码:YES)

I am attempting to connect to mySQL through a NodeJS file, but I receive the following error:

我正在尝试通过NodeJS文件连接到mySQL，但是我收到了以下错误:

{ Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)
    at Handshake.Sequence._packetToError (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/sequences/Sequence.js:30:14)
    at Handshake.ErrorPacket (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/sequences/Handshake.js:67:18)
    at Protocol._parsePacket (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:197:24)
    at Parser.write (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Parser.js:62:12)
    at Protocol.write (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:37:16)
    at Socket.ondata (_stream_readable.js:555:20)
    at emitOne (events.js:101:20)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------
    at Protocol._enqueue (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:110:26)
    at Protocol.handshake (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/protocol/Protocol.js:42:41)
    at Connection.connect (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/Connection.js:81:18)
    at Connection._implyConnect (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/Connection.js:222:10)
    at Connection.query (/home/matthew/Node/mySqlTest/node_modules/mysql/lib/Connection.js:137:8)
    at Object.<anonymous> (/home/matthew/Node/mySqlTest/index.js:11:12)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
  code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlState: '28000',
  fatal: true }

The weird thing is that I can connect fine through the terminal by running mysql -u root -p. I only get this error when running my javascript. I have been all over Google and *, but still have not found a solution that works. I am using MySQL 5.7.16 on Ubuntu 16.04.1 on a VIRTUAL MACHINE. Not sure if a VM makes a difference here. My Javascript code is below:

奇怪的是，我可以通过运行mysql -u root -p很好地连接到终端。我只在运行javascript时才会得到这个错误。我已经遍历了谷歌和*，但仍然没有找到一个有效的解决方案。我在一台虚拟机上使用了ubuntu16.04.1的MySQL 5.7.16。不确定VM在这里是否有影响。我的Javascript代码如下:

'use strict';                                                                                                                                      

var mysql = require('mysql');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password'
});

connection.query(
    'SELECT "foo" AS first_field, "bar" AS second_field',
    function(err, results, fields) {
        console.log(err);
        console.log(results);
        connection.end();
    }
);

I have tried using 'locahost' as well as '127.0.0.1' in my javascript. I have a 'root' user for both 'localhost' and '127.0.0.1' in mySql.user table and I am able to see this by executing SELECT user, host FROM mysql.user WHERE user='root';

我尝试过在javascript中使用“locahost”和“127.0.0.1”。在mySql中，“localhost”和“127.0.0.1”都有一个“root”用户。用户表，我可以通过执行SELECT user, host FROM mysql来看到这一点。用户在用户=“根”;

I have added privileges to 'root' user by executing this:

我在“root”用户中添加了一些特权:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password';

I ran the above on 127.0.0.1 as well. I have also tried this:

我在127.0.0.1上运行了上面的代码。我也尝试过:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

I have attempted to reset the root password like this: https://help.ubuntu.com/community/MysqlPasswordReset

我尝试重置根密码如下:https://help.ubuntu.com/community/MysqlPasswordReset

I have run FLUSH PRIVILEGES after each attempt. I've stopped and restarted mySQL. I have uninstalled mySQL completely and reinstalled.

每次尝试后我都有运行刷新特权。我已经停止并重启mySQL。我已经完全卸载并重新安装了mySQL。

All to no avail. I receive the access denied error every time I try to run the javascript, but I have absolutely no issues when I connect to mySQL via the terminal.

但这一切都无济于事。每次运行javascript时，我都会收到拒绝访问错误，但通过终端连接到mySQL时，绝对没有问题。

Any ideas?

什么好主意吗?

9 个解决方案

#1

Using recent MySQL version in package.json solved the problem.

使用最新的MySQL版本。json解决了这个问题。

I was using version 2.0.0. I changed the version to 2.10.2.

我使用的是2.0.0版本。我把版本改成了2.10.2。

#2

I have the same problem, I solved it by changing the password to empty string.

我也有同样的问题，我把密码改成了空字符串。

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

#3

//surprisingly this works.

/ /令人惊讶的是这个工作。

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: ""
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

#4

Try adding a port field:

尝试添加一个端口字段:

var connection = mysql.createConnection({
   host: 'localhost',
   user: 'root',
   password: 'password',
   port: 3307

});

});

#5

I have faced this issue by giving the full user name in the user section when I changed the 'root'@'localhost' to 'root' It is working fine now.

当我将“root”@“localhost”更改为“root”时，我在user部分中给出了完整的用户名，这就解决了这个问题。

var mysql = require('mysql');

 var con = mysql.createConnection({
  host: hostname,
  user: "root",
  password: "rootPassword"
 });

 con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
 });

#6

I had a similar problem. I was running mysql in a Docker container and had the same error when trying to connect to it from my node app.

我也有类似的问题。我在Docker容器中运行mysql，在尝试从我的节点应用程序连接到它时也有同样的错误。

It appeared, that I had run the Docker container without exposing the port, hence it was 3306 inside the container, but would not have been accessible through localhost:3306. Why I got ER_ACCESS_DENIED_ERROR error was because I actually had some other mysql server running on the port 3306, with different username and password.

看起来，我运行Docker容器时没有暴露端口，因此容器中有3306，但是通过localhost:3306无法访问。我出现ER_ACCESS_DENIED_ERROR错误的原因是我实际上在端口3306上运行了其他mysql服务器，它们的用户名和密码不同。

To see if or what you have running on the specific port type:

查看是否或运行在特定端口类型上的内容:

ps axu | grep 3306

Since I already had something on port 3306, to make the server accessible to my app I changed a port to 3307 and run my docker mysql container with the command:

由于我在端口3306上已经有了一些东西，为了让服务器可以访问我的应用，我将端口改为3307，并使用以下命令运行docker mysql容器:

docker run --name=<name> -e MYSQL_ROOT_PASSWORD=<password> -p 3307:3306 -d mysql

After starting mysql client inside Docker with command:

用命令在Docker内部启动mysql客户端后:

docker exec -it <name> mysql -u root -p

And after creating a database to connect to, I was able to connect to my mysql db from my node app with these lines:

在创建一个要连接的数据库后，我可以通过我的节点应用程序用以下几行连接到我的mysql db:

 const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'database',
    port: 3307
 });

 connection.connect();

Hopefully helps someone new to docker and mysql :)

希望能帮助docker和mysql新手)

#7

I had the same error from nodejs script, so i removed password parameter and now it magically works fine

我在nodejs脚本中有相同的错误，所以我删除了密码参数，现在它神奇地工作得很好

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root'
});

#8

I had the same problem and changing password of database user worked for me. Follow these steps :

我遇到了同样的问题，修改了数据库用户的密码。遵循以下步骤:

1. Open MySQL Workbench

   打开MySQL工作台

2. Open Local instance MySQL57 using old password

   使用旧密码打开本地实例MySQL57

3. Go to Server>Users and Privileges
4. 转到服务器>用户和特权
5. Change password, and login to MySQL again. OR Create a newuser and set privileges. (If changing password do not work.)
6. 更改密码，再次登录到MySQL。或者创建一个新用户并设置特权。(如果更改密码无效)

#9

uninstall mysql,and uninstall mysql related service(eg. mysqld.exe xampp).then,reinstall mysql.

卸载mysql，卸载与mysql相关的服务(如。mysqld。exe xampp)。然后,重新安装mysql。