MySQL ERROR 1045 (28000): Access

Mac 环境下, mysql版本

mysql  Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

安装完成后, 默认的是免密登陆, 依据如下方式更新密码后出现问题

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

UPDATE mysql.user SET authentication_string = '' WHERE user = 'root' AND host = 'localhost';

修正方法:

1. 停止mysql服务

# 不管有没有运行, 最好执行一下
mysql.server stop 

2. 无密码启动MySQL服务

mysqld_safe --skip-grant-tables --skip-networking &

3. 进入MySQL

mysql -uroot

4. 重新设置密码
   注意, 8.0.13版本以前形式的设置密码无效, 请按如下操作

# 设置空密码
UPDATE mysql.user SET authentication_string = '' WHERE user = 'root' AND host = 'localhost';
# 刷新
FLUSH PRIVILEGES;
SELECT host,user, plugin, authentication_string FROM mysql.user; 
# 以caching_sha2_password加密密码并设置
ALTER user 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '你的密码';
# 刷新
FLUSH PRIVILEGES;

注意: 有时候会遇到客户端链接不上, 报错可能如下(笔主是Navicat链接报错)

Failed to Connect to MySQL at 10.211.55.6:3306 with user root 
Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

可按下面步骤修改一下密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'; 
FLUSH PRIVILEGES;

5. 退出重新登陆

mysql -uroot -p
# ENTER后正常输入密码