mysql 8.0 root 账户密码忘记重置方法

版本与环境：ubuntu 24.04 + mysql 8.0

1、修改配置文件跳过权限验证

sudo -s

cd /etc/mysql/mysql.conf.d/
cp mysql.cnf mysql.cnf.bak

cat <<EOF | tee mysql.cnf
skip-grant-tables
skip-networking
>EOF

2、重启mysql服务无密码登录

systemctl restart mysql
mysql -u root

3、生成双重 SHA1 哈希密码

SELECT SHA1(UNHEX(SHA1('123456@ub'))) AS hashed_password;
# 预期输出
+------------------------------------------+
| hashed_password                          |
+------------------------------------------+
| 6b0cf9808cb734ac7254f94dba7bd5be7b6f57b3 |
+------------------------------------------+

4、更新用户密码

USE mysql;

UPDATE user 
SET 
  plugin = 'mysql_native_password',
  authentication_string = '*6b0cf9808cb734ac7254f94dba7bd5be7b6f57b3'  -- 替换为你的哈希值
WHERE 
  user = 'root' AND host = 'localhost';

FLUSH PRIVILEGES;
exit

5、还原配置文件并重启服务

cp mysql.cnf.bak mysql.cnf
systemctl restart mysql

6、验证

mysql -u root -p

SELECT user, host, plugin, authentication_string 
FROM mysql.user 
WHERE user = 'root';

#应看到 plugin 为 mysql_native_password，且 authentication_string 非空。