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 连接失败