MySQL8 忘记密码

太尴尬了，好久没用MySQL已经忘记了用户密码，那么在Windows环境下遗忘了该怎么办呢？跟着小铃铛来看看。

解决方案

第一步：首先停止MySQL服务

PS D:\program_23\mysql-8.0.33-winx64\bin> net stop mysql
mysql 服务正在停止.
mysql 服务已成功停止。

PS：如果不先停止服务，会报错data文件夹被占用，无法修改

PS D:\program_23\mysql-8.0.33-winx64\bin> mysqld --console --skip-grant-tables --shared-memory
2025-03-05T01:29:49.096226Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2025-03-05T01:29:49.096246Z 0 [System] [MY-010116] [Server] D:\program_23\mysql-8.0.33-winx64\bin\mysqld.exe (mysqld 8.0.33) starting as process 8300
2025-03-05T01:29:49.106785Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-03-05T01:29:49.107833Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2025-03-05T01:29:49.107902Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2025-03-05T01:29:49.107947Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2025-03-05T01:29:49.108235Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2025-03-05T01:29:49.108278Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-03-05T01:29:49.108621Z 0 [System] [MY-010910] [Server] D:\program_23\mysql-8.0.33-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.33)  MySQL Community Server - GPL.

第二步：设置跳过密码

在 MySQL 8.0 版本中，--skip-grant-tables 的行为有所变化，可能导致无法直接跳过权限验证。可以尝试以下替代方法：

PS D:\program_23\mysql-8.0.33-winx64\bin> mysqld --console --skip-grant-tables --shared-memory
2025-03-05T01:31:52.968303Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2025-03-05T01:31:52.968330Z 0 [System] [MY-010116] [Server] D:\program_23\mysql-8.0.33-winx64\bin\mysqld.exe (mysqld 8.0.33) starting as process 19244
2025-03-05T01:31:52.978485Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-03-05T01:31:53.354902Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-03-05T01:31:53.709753Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2025-03-05T01:31:53.825343Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2025-03-05T01:31:53.825578Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2025-03-05T01:31:53.867428Z 0 [System] [MY-010931] [Server] D:\program_23\mysql-8.0.33-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.33'  socket: ''  port: 0  MySQL Community Server - GPL.

第三步：在另一个命令行界面访问MySQL

第四步：修改密码

由于当前处于--skip-grant-tables格式下，ALTER USER命令不能正常使用

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password.';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

可以使用：

SET PASSWORD FOR 'root'@'%' = 'new_password.';FLUSH PRIVILEGES;