MySQL 8.0的坑

刘哲贤 5月前 ⋅ 78 阅读

概述

今天我用homebrew安装Mysql8.0.21,安装完成之后,用Workbench和navicat连接数据库都失败了,并且都报caching_sha2_password相关的错误,经过查资料,原因是Mysql8.0的默认认证方式改用sha2了,但是Workbench和Sequel Pro里面都没有sha2的插件,所以报错了。我把解决方法记录下来,供以后开发时参考,相信对其他人也有用。

解决办法

网上流行的解决方案是把sha2认证改回以前的认证方式,方法如下: 1.在Mysql安装目录下 bin文件夹内进入cmd (默认位置:C:\Program Files\MySQL\MySQL Server 8.0\bin),然后输入 mysqld --skip -grant -tables 不要关闭窗口,进行下一步操作。

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld --skip -grant -tables
2020-09-26T04:04:58.321346Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.21) starting as process 10572
2020-09-26T04:04:58.323950Z 0 [Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test
2020-09-26T04:04:58.324095Z 0 [Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test
2020-09-26T04:04:58.324255Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to 'C:\Program Files\MySQL\MySQL Server 8.0\data\' (OS errno: 2 - No such file or directory)
2020-09-26T04:04:58.336788Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-09-26T04:04:58.336923Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.

C:\Program Files\MySQL\MySQL Server 8.0\bin>

2.再在上面的文件夹内打开一个cmd窗口,进入Mysql,alter user 'root'@'localhost' identified with mysql_native_password by '你的密码'; 一定要有那个分号,然后执行成功即可。

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

全部评论: 0

    我有话说: