Linux MySQL8.0.26 忘记 root 密码重置处理办法

原创 JiekeXu All China Database Union 2021-09-24

1908

此为文中一部分（https://mp.weixin.qq.com/s/011y6eCny_p96SLAGRxTEg），因很多人会忘记 MySQL root 密码，故单独拿出来在分享一下。

如果忘记 root 密码，则需要跳过权限表重置

在配置文件中添加如下一行，重启 MySQL 登录则不需要 root 密码。

vim /mysql/conf/my3306.cnf

skip-grant-tables

[mysql@jiekexu-test conf]$ ps -ef | grep mysqld  
mysql 13100 62624 0 15:11 pts/5 00:00:00 /bin/sh /mysql/app/mysql8.0.26/bin/mysqld\_safe --defaults-file=/mysql/conf/my3306.cnf  
mysql 14816 13100 9 15:27 pts/5 00:00:01 /mysql/app/mysql8.0.26/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf --basedir=/mysql/data/mysql3306 --datadir=/mysql/data/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/mysql/data/mysql3306/errlog/err3306.log --pid-file=/mysql/data/mysql3306/pid/mysqld.pid --socket=/mysql/data/mysql3306/socket/mysql.sock --port=3306  
[mysql@jiekexu-test conf]$ kill 14816

复制

重启 MySQL

[mysql@jiekexu-test conf]$ mysqld\_safe --defaults-file=/mysql/conf/my3306.cnf &  
[1] 13100  
[mysql@jiekexu-test conf]$ 2021-09-24T07:11:30.280687Z mysqld\_safe Logging to ‘/mysql/data/mysql3306/errlog/err3306.log’.  
2021-09-24T07:11:30.308423Z mysqld\_safe Starting mysqld daemon with databases from /mysql/data/mysql3306/data

[mysql@jiekexu-test conf]$  
[mysql@jiekexu-test conf]$ mysql -uroot  
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)  
[mysql@jiekexu-test conf]$ mysql -uroot -S /mysql/data/mysql3306/socket/mysql.sock  
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 © 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 语句。ERROR 1290 (HY000)
  
mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘root123’;  
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.00 sec)

mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘root123’;  
Query OK, 0 rows affected (0.02 sec)  
mysql> ALTER USER ‘root’@’%’ IDENTIFIED BY ‘root123’;  
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;  
Query OK, 0 rows affected (0.00 sec)  
mysql> shutdown;  
Query OK, 0 rows affected (0.00 sec)
复制

参数文件中注释掉 #skip-grant-tables 然后启动，可正常登录。

[mysql@jiekexu-test conf]$ mysqld_safe --defaults-file=/mysql/conf/my3306.cnf &  
[1\] 24737  
[mysql@jiekexu-test conf]$ 2021-09-24T08:35:30.943299Z mysqld_safe Logging to ‘/mysql/data/mysql3306/errlog/err3306.log’.  
2021-09-24T08:35:30.967091Z mysqld_safe Starting mysqld daemon with databases from /mysql/data/mysql3306/data

[mysql@jiekexu-test conf]$  
[mysql@jiekexu-test conf]$ mysql -uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock  
Enter password:  
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 © 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> exit  
Bye  
[mysql@jiekexu-test conf]$ mysql -h 192.168.75.135-uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock  
Enter password:  
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 9  
Server version: 8.0.26 MySQL Community Server - GPL

Copyright © 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.

–这里禁止修改 authentication_string 字段  
mysql> select user,authentication_string from mysql.user;  
±-----------------±-----------------------------------------------------------------------+  
| user | authentication_string |  
±-----------------±-----------------------------------------------------------------------+  
| root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |  
| mysql.infoschema | $A005005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |  
| mysql.session | $A005005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |  
| mysql.sys | $A005005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |  
| root | $A005005005M_F>KVC1’3G#n~u6/VHFq2vMJX.z6I1ZW7Fr62UWwKGAs2SVTjfBNFrxs4 |  
±-----------------±-----------------------------------------------------------------------+  
5 rows in set (0.00 sec)

mysql>

复制

图片.png

还有一种办法,只能去修改 mysql 的 user 表，将加密字段authentication_string 置空，然后使用空密码登录，但不能修改 authentication_string 为其他值，使用密码登录。

use mysql;  
mysql> update user set authentication_string=’’ where user=‘root’;  
Query OK, 2 rows affected (0.00 sec)  
Rows matched: 2 Changed: 2 Warnings: 0

mysql> shutdown;  
Query OK, 0 rows affected (0.00 sec)

-- update user set authentication_string=‘root’ where user=‘root’; --亲测这种修改方法不生效,无法登录。

[mysql@jiekexu-test conf]$ mysql -uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock  
Enter password:  
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 © 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> select user,authentication_string from mysql.user;  
±-----------------±-----------------------------------------------------------------------+  
| user | authentication_string |  
±-----------------±-----------------------------------------------------------------------+  
| root | |  
| mysql.infoschema | $A005005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |  
| mysql.session | $A005005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |  
| mysql.sys | $A005005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |  
| root | |  
±-----------------±-----------------------------------------------------------------------+  
5 rows in set (0.00 sec)
复制

——————————————————————–—--————

公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107

————————————————————————----———
图片.png