今天忘记了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>