mysql 如何重置数据库用户密码

 忘记root密码如何重置密码

   

1 -->跳过授权表并禁止远程用户登录

mysqld_safe --skip-grant-tables --skip-networking &

2 重置用户密码信息

flush privileges;
alter user root@'localhost' identified by '123456';
quit

   下面命令行详细步骤

[root@zhang ~]# mysql -uroot -p'123'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

                                         #密码错误无法登录并忘记密码


[root@zhang ~]# systemctl stop  mysql   # 关闭数据库


[root@zhang ~]# ps -ef|grep mysql    # 查看数据库是否关闭
root       2459   2332  0 00:47 pts/3    00:00:00 grep --color=auto mysql
[root@zhang ~]# ss -ntl
State       Recv-Q Send-Q   Local Address:Port                  Peer Address:Port              
LISTEN      0      128                  *:22                               *:*                  
LISTEN      0      100          127.0.0.1:25                               *:*                  
LISTEN      0      128               [::]:22                            [::]:*                  
LISTEN      0      100              [::1]:25                            [::]:* 
         
                                           #查看数据库是否关闭
                 

[root@zhang ~]# mysqld_safe --skip-grant-tables --skip-networking & 
[1] 2463

                                          #跳过授权表并关闭tcp地址

[root@zhang ~]# 2023-07-02T16:50:07.924101Z mysqld_safe Logging to '/data/3306/data/zhang.err'.
2023-07-02T16:50:07.962025Z mysqld_safe Starting mysqld daemon with databases from /data/3306/data

[root@zhang ~]# mysql -uroot   #无密码登录


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> alter user root@'localhost' identified by '123456';
                         
                              #  重置用户密码


ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 
                               # 重置错误  无法执行此语句


mysql> flush privileges;   # 刷新后重新执行命令       
Query OK, 0 rows affected (0.01 sec)

mysql> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

                               # 执行成功

mysql> quit                    # 退出
Bye
[root@zhang ~]# systemctl stop  mysql
[root@zhang ~]# systemctl start  mysql

                               # 重启 

[root@zhang ~]# mysql -uroot -p'123456'     # 登录测试
 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 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> 
 
登录成功

[root@zhang ~]# mysql -uroot -p'123'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

                                         #密码错误无法登录并忘记密码

[root@zhang ~]# systemctl stop  mysql   # 关闭数据库

[root@zhang ~]# ps -ef|grep mysql    # 查看数据库是否关闭
root       2459   2332  0 00:47 pts/3    00:00:00 grep --color=auto mysql
[root@zhang ~]# ss -ntl
State       Recv-Q Send-Q   Local Address:Port                  Peer Address:Port              
LISTEN      0      128                  *:22                               *:*                  
LISTEN      0      100          127.0.0.1:25                               *:*                  
LISTEN      0      128               [::]:22                            [::]:*                  
LISTEN      0      100              [::1]:25                            [::]:* 
         
                                           #查看数据库是否关闭
                 

[root@zhang ~]# mysqld_safe --skip-grant-tables --skip-networking & 
[1] 2463

                                          #跳过授权表并关闭tcp地址

[root@zhang ~]# 2023-07-02T16:50:07.924101Z mysqld_safe Logging to '/data/3306/data/zhang.err'.
2023-07-02T16:50:07.962025Z mysqld_safe Starting mysqld daemon with databases from /data/3306/data

[root@zhang ~]# mysql -uroot   #无密码登录

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> alter user root@'localhost' identified by '123456';
                         
                              #  重置用户密码

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 
                               # 重置错误  无法执行此语句

mysql> flush privileges;   # 刷新后重新执行命令       
Query OK, 0 rows affected (0.01 sec)

mysql> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

                               # 执行成功

mysql> quit                    # 退出
Bye
[root@zhang ~]# systemctl stop  mysql
[root@zhang ~]# systemctl start  mysql

                               # 重启 

[root@zhang ~]# mysql -uroot -p'123456'     # 登录测试
 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 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> 
 
登录成功