本文为安装 MySQL 8.0.26 的一部分(mp.weixin.qq.com/s/011y6eCny… )，因很多人会忘记 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>
还有一种办法,只能去修改 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之路
墨天轮：www.modb.pro/u/4347
CSDN ：blog.csdn.net/JiekeXu
腾讯云：cloud.tencent.com/developer/u…
————————————————————————----———
