Windows7 上连接 MySQL报错问题处理

错误信息

在 win7上面连接 MySQL 报错:ERROR 1045 (28000): Access denied for user '-root'@'localhost' (using password: YES).

环境如下:

  • win7 64位系统(32位也应该没有关系把)
  • mysql-8.0.13

报错图示:

报错图示

正确的处理办法

  1. 先检查下 MySQL 的服务是否启动,如果启动就停止,使用命令 net stop mysql
  2. 新开一个 Cmd 界面,进入 MySQL 安装路径下的 bin 下面,执行语句:
mysqld --console --skip-grant-tables --shared-memory
  1. 新开一个Cmd 界面,进入 MySQL 安装路径下的 bin 下面,使用无密码登录
mysql -u root

图示:
图示
4. 启动了之后,就查看下面的操作:

启动服务的窗口界面:

//开启MySQL
C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 ...
MySQL 服务已经启动成功。

//登陆报错
C:\WINDOWS\system32>mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

//关闭MySQL
C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

//无密码启动MySQL服务
C:\WINDOWS\system32>mysqld --console --skip-grant-tables --shared-memory
2018-11-22T14:06:27.964267Z 0 [System] [MY-010116] [Server] 
D:\mysql-8.0.13-winx64\bin\mysqld.exe (mysqld 8.0.13) starting as process 2972
2018-11-22T14:06:30.981556Z 0 [Warning] [MY-010068] [Server] 
CA certificate ca.pem is self signed.
2018-11-22T14:06:31.006330Z 0 [System] [MY-010931] [Server] 
D:\mysql-8.0.13-winx64\bin\mysqld.exe: ready for connections. 
Version: '8.0.13'  socket: ''  port: 0  MySQL Community Server - GPL.
2018-11-22T14:06:31.209604Z 0 [Warning] [MY-011311] [Server] 
Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'

设置的 cmd 窗口

//无密码【登陆】(密码处直接enter)
C:\WINDOWS\system32>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

//刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

//查询
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password |                                                                        |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

//---------------------------------------------设置加密的密码---------------------------------------------
//以caching_sha2_password加密密码并设置
mysql> ALTER user 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'admin';
Query OK, 0 rows affected (0.17 sec)

//刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

//查询
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | caching_sha2_password | $A$005$r/r8"We_EpPb9584lw2cALUsOsvkB/hHg1qUqocVxDMMkFQ8RyQcXASZoff5 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

关闭以上的两个窗口,再开一个新的窗口

//登陆成功
C:\WINDOWS\system32>mysql -uroot -padmin
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 'admin';
Query OK, 0 rows affected (0.12 sec)

//刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

//查询
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | mysql_native_password | *4ACFE3202A5FF5CF467898FC58AAB1D615029441                              |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

完成以上操作就搞定了。控制台部分的内容转载自:https://blog.csdn.net/sinat_31057219/article/details/84402896

最后就可以使用Navicat连接上去了:
使用Navicat连接

说明:

1. 在 MySQL 8.0 以上 skip-grant-tables参数已经废弃了,无法使用。
2. 在 MySQL 8.0 .13 中使用免安装版,初始化的密码是不能登录的,需要重新设置。

错误的处理办法以及说明(建议了解,不要去尝试)

在网上有好多博文说,在 my.ini 文件上面增加 skip-grant-tables,然后重新启动MySQL 服务,我去尝试了,根本服务就启动不了

尝试连接

修改my.ini
修改my.ini

启动服务
启动服务就是各种报错啊。然后去掉 skip-grant-tables 就可以启动。内心简直是。。。
启动服务

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wayfreem

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值