node mysql 连接失败解决方案

通过node-mysql连接数据库时,mysql 版本为8.0.16

{ Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
    at Protocol._parsePacket (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/Connection.js:91:28)
    at Socket.<anonymous> (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/Connection.js:525:10)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    --------------------
    at Protocol._enqueue (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/Users/naruto/Desktop/mycode/blog/mysql-test/node_modules/mysql/lib/Connection.js:119:18)
    at Object.<anonymous> (/Users/naruto/Desktop/mycode/blog/mysql-test/index.js:12:5)
    at Module._compile (internal/modules/cjs/loader.js:776:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:787:10)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)
    at Function.Module.runMain (internal/modules/cjs/loader.js:829:12)
  code: 'ER_NOT_SUPPORTED_AUTH_MODE',
  errno: 1251,
  sqlMessage:
   'Client does not support authentication protocol requested by server; consider upgrading MySQL client',
  sqlState: '08004',
  fatal: true }

通过Google查询该问题之后，是因为node中的mysql模块不支持mysql 8中的caching_sha2_password默认加密方式，所以在node-mysql中链接会有上述异常；

解决方法是从新修改用户root的密码，并指定mysql模块能够支持的加密方式：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

但此方式可能会有策略性的问题：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

别慌，稳住，这种问题通过踩坑也是可以解决的，可以修改validate_password_policy来解决

【原因】

原来MySQL5.6.6版本之后增加了密码强度验证插件validate_password，相关参数设置的较为严格。
使用了该插件会检查设置的密码是否符合当前设置的强度规则，若不满足则拒绝设置。影响的语句和函数有：create user,grant,set password,password(),old password。

【解决】

• 查看mysql全局参数配置

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
// 插件用于验证密码强度的字典文件路径
| validate_password.dictionary_file    |        |
// 密码最小长度，参数默认为 8
| validate_password.length             | 8      |
// 密码至少要包含的小写字母个数和大写字母个数
| validate_password.mixed_case_count   | 1      |
// 密码至少要包含的数字个数
| validate_password.number_count       | 1      |
// 密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG
| validate_password.policy             | MEDIUM |
// 密码至少要包含的特殊字符数
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

• 修改mysql参数配置，这里我只修改了两个参数（如果修改后还是连接异常，可尝试修改多个配置参数

mysql> SET GLOBAL validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

查看修改后

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 8     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

显示指定了使用“mysql_native_password”的加密方式。使用这种加密方式是为了让node-mysql支持此加密方式

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'qwer1234?';
Query OK, 0 rows affected (0.00 sec)

这时在运用node中脚本，我的是index.js，node index.js

mysql-test git:(master) ✗ node index.js
[ RowDataPacket { id: 1, username: 'zhangsan', password: '123', realname: '张三' },
  RowDataPacket { id: 3, username: 'lisi', password: '123', realname: '李四2' } ]

至此，以解决node mysql 连接失败

转载于:https://www.cnblogs.com/narutoNinja/p/11190759.html