mysql 8.0以上的版本忘记密码，重置密码终极解决方案

今天忘记了linux centos 上 mysql的 root 密码
按照ChatGpt 给的方法尝试了多次，均告失败，看来 AI 对于程序员来说也不是万能的。
ChatGpt给的方法是先越权登录

sudo systemctl stop mysqld
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
sudo systemctl start mysqld
mysql -u root
USE mysql;

使用ALTER命令或者直接 update user 表的authentication_string，但是越权登录后，ALTER命令不能使用，报错

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

GPT又给出了直接update user表中authentication_string字段的方案

UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='root' AND Host='localhost';
UPDATE user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('new_password'))))) WHERE User='root' AND Host='localhost';
UPDATE user SET authentication_string=PASSWORD('new_password'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

但由于 mysql8.0以上版本取消了PASSWORD()函数，还有就是加密算法 sha1 还是 sha2的问题，以及plugin插件不存在等原因，要么修改不成功，要么修改成功了，登录验证不过去

mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql> UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('new_password') WHERE User = 'root' AND Host = 'localhost'' at line 1

正确有效的解决方法还是自己搜出来的，如下：

# 越权登录后先使用update把user表中对应root用户的authentication_string字段置为空
update user set authentication_string = "" where user = "root";

# 这样就可以使用空密码正常登录了，取消越权登录，
sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl restart mysqld

# 空密码正常登录
mysql -u root -p
输入空密码，登录成功

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

# 密码修改成功，重新登录
mysql -u root -p
# 输入新密码，成功登录
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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>