注:原因为MySql 8.0.11换了新的身份验证插件(caching_sha2_password), 原来的身份验证插件为(mysql_native_password)。而客户端工具找不到新的身份验证插件(caching_sha2_password),将mysql用户使用的登录密码加密规则还原成mysql_native_password,即可登陆成功。
1、无密码本机登录
在/etc/my.cnf添加下面配置
skip-grant-tables(注意:开启该权限,就无法远程登录mysql,3306端口会被关闭)
重启mysqld即可无密码登录
2、查看用户身份验证类型
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin |
+------+-----------------------+
| root | caching_sha2_password |
+------+-----------------------+
1 row in set (0.00 sec)
3、第一次修改报错
mysql> alter user 'root'@'%' identified with mysql_native_password by 'Adm#0412';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
4、执行flush privilege:
mysql> flush privileges;
5、第二次修改报错:
mysql> alter user 'root'@'%' identified with mysql_native_password by '*****';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
6、执行下面sql
mysql>update user set authentication_string='' where user='root';
mysql>flush privileges;
7、第三次修改密码
mysql>alter user 'root'@'%' identified with mysql_native_password by '****';
8、修改后再次查看用户身份验证类型
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.00 sec)
9、在配置文件/etc/my.cnf添加下面配置(同时注释skip-grant-tables)
default_authentication_plugin=mysql_native_password
#skip-grant-tables
10、重启mysqld
systemctl restart mysqld
至此就完成root用户密码修改,且可以正常登录。