mysql - 无法使用 MySQL(8.0.15) 验证我的 Node js(v10.15.3) 应用程序、npm mysql (v2.17.1) (ER_ACCESS_DENIED_ERROR)

Question

我正在运行一个简单的 Nodejs(v10.15.3) 应用程序尝试访问 MySQL 服务器 (8.0.15)。 npm mysql 包版本是 v2.17.1。脚本如下所示，

var mysql = require('mysql');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'test',
    password: '2222',
    database: 'database',
    debug: true          
});

connection.connect(function(err) {
    if (err) {
        console.log(err.stack);
        throw err;            
    }

    console.log("Connected!");
});

奇怪的是我能够使用相同的凭据从 MySQL 客户端登录。我几乎尝试了其他论坛中提到的所有方法。我尝试过的一些事情，

1. 为了进行用户测试，我已将密码插件类型从 caching_sha2_password 更改为 mysql_native_password。
2. 更新了用户的密码并刷新了权限。

附加整个堆栈跟踪。

<-- HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '8.0.13',
  threadId: 71,
  scrambleBuff1: <Buffer 16 5c 10 12 68 49 73 68>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 50175,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 41 06 27 5a 0c 4c 4c 30 2f 72 0d 7e>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> (71) ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'test',
  scrambleBuff:
   <Buffer fa ee eb f2 89 a4 f0 91 a9 d1 aa f2 77 08 76 bb ec a2 51 8b>,
  database: 'simplify_meetup',
  protocol41: true }

<-- (71) ErrorPacket {
  fieldCount: 255,
  errno: 1045,
  sqlStateMarker: '#',
  sqlState: '28000',
  message:
   'Access denied for user \'test\'@\'localhost\' (using password: YES)' }

Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'test'@'localhost' (using password: YES)
    at Handshake.Sequence._packetToError (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Handshake.ErrorPacket (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
    at Protocol._parsePacket (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (D:\Development\simplify-meetup\node_modules\mysql\lib\Connection.js:91:28)
    at Socket.<anonymous> (D:\Development\simplify-meetup\node_modules\mysql\lib\Connection.js:525:10)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    --------------------
    at Protocol._enqueue (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Protocol.handshake (D:\Development\simplify-meetup\node_modules\mysql\lib\protocol\Protocol.js:51:23)
    at Connection.connect (D:\Development\simplify-meetup\node_modules\mysql\lib\Connection.js:119:18)
    at D:\Development\simplify-meetup\routes\EventReminderAPI.js:20:16
    at Layer.handle [as handle_request] (D:\Development\simplify-meetup\node_modules\express\lib\router\layer.js:95:5)
    at next (D:\Development\simplify-meetup\node_modules\express\lib\router\route.js:137:13)
    at Route.dispatch (D:\Development\simplify-meetup\node_modules\express\lib\router\route.js:112:3)
    at Layer.handle [as handle_request] (D:\Development\simplify-meetup\node_modules\express\lib\router\layer.js:95:5)
    at D:\Development\simplify-meetup\node_modules\express\lib\router\index.js:281:22
    at Function.process_params (D:\Development\simplify-meetup\node_modules\express\lib\router\index.js:335:12)

Answer 1

明白了。如果您安装了 MySQL 8.0.4 及更高版本，您将可以选择选择最新的身份验证方法或支持旧的身份验证方法。如果选择第一个选项，MySQL 服务器仅支持 caching_sha2_password。另一个旧选项支持 mysql_native_password。

由于当前 npm mysql 客户端包不支持最新的身份验证方法，而我在 MySQL 服务器上选择了最新的身份验证方法，因此我遇到了这个问题。

选项屏幕如下所示。更改选项后，它的效果非常好。

感谢@ruiquelhas 指导我解决潜在问题:)