MySQL 8.0.13 密码问题 ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)

本文详细介绍了当MySQL密码丢失时,如何通过跳过权限表启动MySQL服务,无密码登录,并设置新的密码。包括设置空密码、加密密码以及修改加密方式的过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

先开一个cmd窗口A:

//开启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窗口B:

//无密码【登陆】(密码处直接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)

关闭A/B cmd窗口,再开一个cmd 窗口C

//登陆成功
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>

其他常用cmd命令

//---------------------------------------常用语句------------------------------------------------------------
//修改加密规则
mysql> UPDATE mysql.user SET plugin='mysql_native_password' WHERE user='root';

//查询
mysql> select host,user,plugin,authentication_string from mysql.user;

//对密码进行加密
mysql> update mysql.user set password=password('admin') where user='root' and host='localhost';  

//刷新
flush privileges;

//退出
mysql> exit

注意:

mysql> use mysql;  
mysql> update user set password=password('123') where user='root' and host='localhost';  
//等价于
mysql> update mysql.user set password=password('123') where user='root' and host='localhost';  
C:\WINDOWS\system32>mysql -uroot -padmin 
//等价于
C:\WINDOWS\system32>mysql -u root -p admin
<think>嗯,用户遇到了MySQL连接问题,错误代码是2003,提示无法连接到本地服务器。首先,我得回忆一下这个错误的常见原因。通常,2003错误意味着MySQL服务没有运行,或者端口被阻止,或者配置问题。 用户是在Windows系统下运行命令的,所以应该检查MySQL服务是否启动。步骤可能是先打开服务管理器,找到MySQL服务,确认其状态是否为“正在运行”。如果没有启动,尝试手动启动它。如果启动失败,可能需要查看错误日志,看看有没有更详细的信息。 另外,防火墙设置也可能导致这个问题。虽然本地连接一般不会触发防火墙,但有时候安全软件可能会阻止MySQL的端口(默认是3306)。需要检查防火墙规则,确保3306端口是开放的。 还有可能是MySQL的配置文件my.ini或my.cnf中的bind-address设置成了127.0.0.1以外的地址,或者skip-networking被启用,这会导致无法通过TCP/IP连接。需要检查这些配置项,确保MySQL允许本地连接。 如果用户更改过root密码,或者密码错误,也可能出现连接问题。但错误代码2003更多是服务未运行或网络问题,而不是认证失败。不过,如果服务在运行,但密码错误,可能会有不同的错误代码,比如1045,所以这里可能不需要考虑密码问题。 还有可能MySQL安装有问题,或者数据目录损坏,导致服务无法启动。这时候需要查看MySQL的错误日志,通常位于数据目录下的.err文件,里面会有具体的错误信息,帮助诊断问题。 总结一下,可能的解决步骤是: 1. 检查MySQL服务是否运行。 2. 检查端口3306是否被监听。 3. 检查防火墙设置。 4. 查看MySQL配置文件。 5. 检查错误日志获取详细信息。</think>遇到MySQL连接错误2003(HY000),通常由以下原因引起,请按顺序排查: $\textcolor{red}{【步骤1:检查MySQL服务状态】}$ 1. 按 $\boxed{Win+R}$ 输入 `services.msc` 2. 在服务列表中找到 `MySQL` 或 `MariaDB` 服务 3. 检查服务状态应为 $\textcolor{green}{正在运行}$ • 若未运行:右键选择 $\textcolor{blue}{启动}$ • 启动失败:需查看错误日志(见步骤4) $\textcolor{red}{【步骤2:验证端口监听】}$ 在CMD执行: ```shell netstat -ano | findstr :3306 ``` 应有类似响应: ```shell TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING 1234 ``` 若无输出,说明: ① MySQL未启动 ② 配置文件修改了端口 $\textcolor{red}{【步骤3:检查配置文件my.ini】}$ 定位文件(通常位于): ```shell C:\ProgramData\MySQL\MySQL Server 8.0\my.ini ``` 确认关键参数: ```ini [mysqld] port=3306 bind-address=0.0.0.0 # 或127.0.0.1 skip-networking=OFF # 必须关闭 ``` $\textcolor{red}{【步骤4:查看错误日志】}$ 日志路径通常为: ```shell C:\ProgramData\MySQL\MySQL Server 8.0\Data\<主机名>.err ``` 常见错误示例: ```log 2023-10-01T12:00:00.123456Z 0 [ERROR] InnoDB: File .\ibdata1 can't be opened → 数据文件损坏,需修复 ``` $\textcolor{red}{【终极解决方案】}$ 若服务无法启动且无重要数据: 1. 以管理员运行CMD: ```shell mysqld --remove mysqld --install net start mysql ``` 2. 仍失败则需重新安装MySQL $\boxed{\text{提示}}$:若首次安装后出现此问题,可能是安装过程中未勾选"Launch MySQL Server after Install"选项导致服务未自动启动。
评论 31
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值