1. 问题描述
本地使用docker-compose启动了一个mysql服务, 默认使用image tag 是latest, yaml文件如下:
## 本地开发所需资源文件version: '3'services: ### mysql (https://hub.docker.com/_/mysql/) db_mysql: container_name: mysql environment: MYSQL_ROOT_PASSWORD: 1qaz TZ: Asia/Shanghai image: mysql ports: - 3306:3306 restart: always
nodejs 程序使用typeorm和如下配置链接本地mysql:
DB_TYPE=mysqlDB_HOST=localhostDB_PORT=3306DB_USERNAME=rootDB_PASSWORD=1qazDB_DATABASE=testDB_SYNC=true
启动程序报告错误:
{ Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client at Handshake.Sequence._packetToError (/usr/src/app/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)... ... 问题定义: mysql client 链接 mysql server 出现权限验证方面错误
2. 问题调查
-
: 该方案实际执行sql脚本, 但是不work.
-
: 该方案也是执行sql修改root用户权限, github上很多尝试后好用, 采纳该方案, 结果是work, 到此问题解决.
-
: 该方案通过开启insecureAuth, 感觉不太安全并且问题在上一步得到解决没有采纳.
根本原因: mysql的client驱动当前仅支持 mysql_native_password/mysql_old_password 鉴权模式, 而5.7后默认的鉴权模式是: plugin, mysql提供两个插件:
- sha256_password: Implements basic SHA-256 authentication
- caching_sha2_password: Implements SHA-256 authentication (like sha256_password), but uses caching on the server side for better performance and has additional features for wider applicability
mysql8.0默认用的是: caching_sha2_password而不是mysql_native_password, 我们的解决方案则是修改root用户鉴权模式从caching_sha2_password为传统的mysql_native_password.
通过查询用户信息:
mysql> select Host,User,plugin,authentication_string from user where User='root'\G*************************** 1. row *************************** Host: localhost User: root plugin: auth_socketauthentication_string: 1 row in set (0.00 sec)
本质上应该使得client driver支持auth_socket.
3. 解决方案
方案一: 链接到mysql server, 执行如下命令:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
方案二: 设置docker启动命令, 采用native password
db_mysql: container_name: mysql environment: MYSQL_ROOT_PASSWORD: 1qaz TZ: Asia/Shanghai image: mysql command: --default-authentication-plugin=mysql_native_password ports: - 3306:3306 restart: always
重启nodejs服务没有错误了, 问题解决!