【背景】
由于各种原因,我见过很多次忘记MySQL根密码的用户。 如果是普通用户还好,可以使用根用户更改密码。 很辛苦
我把路线弄丢了!
如果MySQL忘记了超级用户密码,但要通过更改密码来恢复超级用户密码,则需要准备重新启动。
【方法一: skip _ grant _ tables skip-networking二次重启】
1 ) :第一步关闭MySQL
ps -ef | grepmysql
mysql939 1 0 16:39? 0336000336002/usr/local/MySQL/br dmz/mysqld-- defaults-file=/etc/my.CNF kill-9,939
2 )在: skip-grant-tables skip-networking模式下启动mysqld
/usr/local/MySQL/br dmz/mysqld-- defaults-file=/etc/my.cn f-- skip-grant-tables-skip-networking
3 )进入3360MySQL
对于mysql-5.7,口令的散列值存储在名为authentication_string的列中,而5.7之前的版本存储在password列中
select user,host,authentic ation _ stringfrommysql.user; ------------- -请参阅
用户|身份验证_字符串
------------- -请参阅
| root|* 91b 73478 b 18 b04 d 13 f 6926 fab 5a 6178250 eab 697 |
| MySQL.session|* thisisnotavalidpasswordthatcanbeusedhere |
| MySQL.sys|* thisisnotavalidpasswordthatcanbeusedhere |
| monitor|127.0.0.1|* dad 735712 bb 263 a 8da 12a 091 aabc 625 Fe 99dd 344 |
| root|127.0.0.1|* 91b 73478 b 18 b04 d 13 f 6926 fab 5a 6178250 eab 697 |
| backup|127.0.0.1|* 2139 a3e F5 Fe 5a 0229 be 550 ad5ed 2947 b07f 43 b 93 |
| backup|* 2139 a3e F5 Fe 5a 0229 be 550 ad5ed 2947 b07f 43 b 93 |
| appuser|127.0.0.1|* 6bb 4837 EB 74329105 ee 4568 DDA7DC 67 ed2ca 2ad9|
------------- -请参阅
8行插入(0.01秒) )。
通过直接更新密码的哈希值来更新密码
update MySQL.usersetauthentication _ string=password (mtls 0352 ) ) whereuser=(root ); -将密码更新为MTls0352
查询确定,2 rows affected,1warning(1.01sec ) )。
行匹配:2更改:2警告:1
select user,host,authentic ation _ stringfrommysql.user; ------------- -请参阅
用户|身份验证_字符串
------------- -请参阅
| root|* 597 b 32612905 c 92ab c 495354 fc 276 d 24 d 0a 541 C1 |
| MySQL.session|* thisisnotavalidpasswordthatcanbeusedhere |
| mysql.sys | lo
calhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || monitor | 127.0.0.1 | *DAD735712BB263A8DA12A091AABC625FE99DD344 |
| root | 127.0.0.1 | *597B32612905C92ABC495354FC276D24D0A541C1 |
| backup | 127.0.0.1 | *2139A3EF5FE5A0229BE550AD5ED2947B07F43B93 |
| backup | localhost | *2139A3EF5FE5A0229BE550AD5ED2947B07F43B93 |
| appuser | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
8 rows in set (0.00 sec)
4): 重启mysqld
pkill mysql/usr/local/mysql/brdmz/mysqld --defaults-file=/etc/my.cnf &
5): 用新的密码进入MySQL
mysql -uroot -pMTls0352
mysql:[Warning] Using a password onthe command line interface can be insecure.
Welcometo the MySQL monitor. Commands end with ; org.
Your MySQL connection idis 2Server version:5.7.23-logMySQL Community Server (GPL)
Copyright (c)2000, 2018, Oracle and/or its affiliates. Allrights reserved.
Oracleis a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarksoftheir respective
owners.
Type‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the currentinput statement.
mysql>show grants;+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.01 sec)
【方法二:init-file + 一次重启】
1): 创建用于修改root密码的sql文件
touch /tmp/change_password.sql
内容如下
alter user root@‘127.0.0.1‘ identified by ‘mtls0352‘;alter user root@‘localhost‘ identified by ‘mtls0352‘;
2): 关闭mysql服务
pkill mysqld # 我的主机上只有一个mysql服务所以用pkill mysqld 没有问题,如果你是单机多实例请用 kill $MYSQLPID
3): 代入修改密码的init-file来启动MySQL服务
/usr/local/mysql/brdmz/mysqld --init-file=/tmp/change_password.sql &
4): 用新密码登录MySQL
mysql -uroot -pmtls0352
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 connectionid is 3Server version:5.7.23-log MySQL Community Server (GPL)
Copyright (c)2000, 2018, Oracle and/or its affiliates. All rights reserved.
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 clearthe current input statement.
mysql>show grants;+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
5): 删除修改密码的sql文件
rm /tmp/change_password.sql
【我的评介】
a: 第一种方法比较传统,第二种方法“角度刁钻” 但是两个都能解决问题;并且第二种方法看起来步骤又少一些,但是这并不只是问题的全部
通常一个线上的MySQL实例并不是以“/usr/local/mysql/brdmz/mysqld --defaults-file=/etc/my.cnf &” 这样直接下命令的方式启动的,它们
通常和service ,systemctl 一起用的;所以这种情况下针对“方法二”还是要用service 或 systemctl把数据拉起来的,不然就不能用它们来管理
了,所以“方法二”最终也是要两次重启的。
b: 方法二能使用“alter user”语法,dba就可以不用care密码到底保存在mysql.user表中的那个列。
两种方法各有好处,所以重点还是要把一个方法搞的出神cdxn!
【学习交流】
-----------------------------http://www.sqlpy.com-------------------------------------------------
-----------------------------http://www.sqlpy.com-------------------------------------------------