MySQL8忘记密码解决方案-无废话篇

韩公子的Linux大集市 2025-02-12

1、MySQL配置文件mysqld配置块写入如下配置

[mysqld]
# 2025/02/04
skip-grant-tables

2、启动MySQL进入，执行如下语句修改即可

--进入MySQ数据库服务器
[root@DevOps83 login_project]# opt/mysql/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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.


-- 使用use语句进入MySQL数据库
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed


--刷新权限啥意思？！
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
--还刷新权限，啥意思？！
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-- 此处应是查询数据库的基本信息，和刷权限没关系

3、修改已经存在的用户密码

--使用alte语修改用户密码，前提是必须是存在的用户
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456.com';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456.com';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> 
mysql> 
--查修改后的用户基本信息
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | 123456.com                                                             |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

4、修改用户密码为空密码

mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>  
    -> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5、再次查询用户信息

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

6、修改用户密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456.com';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | $A$005$C8Rywt z	@NnrDYrhVszGCOvCQocBoe/VxWzUDsELkGblq06yHXC9yC |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.01 sec)

7、退出系统

mysql> exit
Bye

8、注释MySQL中的配置文件中mysqld段的skip语句，重新启动MySQL，确认密码是否修改成功

[root@DevOps83 login_project]# opt/mysql/bin/mysql -uroot -p'123456.com'
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 9
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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> 
mysql> \s
--------------
/opt/mysql/bin/mysql  Ver 8.4.0 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id:		9
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.4.0 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/data/mysql/3306/data/mysql.sock
Binary data as:		Hexadecimal
Uptime:			8 sec
Threads: 2  Questions: 7  Slow queries: 0  Opens: 136  Flush tables: 3  Open tables: 55  Queries per second avg: 0.875

9、登录MySQL服务器，建库建表

-- 2025/02/04 Web登录页面的测试，建库建表
mysql> create user 'hanyw'@'%' identified by '123456.com';
Query OK, 0 rows affected (0.03 sec)
mysql> create database php_demo01;
Query OK, 1 row affected (0.00 sec)
mysql> grant all privileges on php_demo01.* to 'hanyw'@'%' all privileges;
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 'all privileges' at line 1
mysql> 
mysql> grant all privileges on php_demo01.* to 'hanyw'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

10、输入修改后的用户密码信息登录MySQL数据库

[root@DevOps83 login_project]# /opt/mysql/bin/mysql -uhanyw -p'123456.com'
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 11
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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> ^DBye
[root@DevOps83 login_project]# /opt/mysql/bin/mysql -h 10.16.5.128 -uhanyw -p'123456.com'
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 12
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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> select user();
+-------------------+
| user()            |
+-------------------+
| hanyw@10.16.5.128 |
+-------------------+
1 row in set (0.00 sec)
mysql> exit
Bye

11、思考【授权引发的小问题】

授权的是hanyw用户，为何hanyw@localhost 和hanyw@%都可以登录MySQL数据库呢？

12、附送：建库建用户表的语句

CREATE DATABASE db01;
USE db01;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL
);
INSERT INTO users (username, password) VALUES ('testuser001', '123456.com');

文章转载自韩公子的Linux大集市，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL8忘记密码解决方案-无废话篇

评论