MySQL 忘记 root密码 两种 处理方法
原文：MySQL 忘记root密码的两种处理方法 - 走看看 (zoukankan.com)
【背景】
由于各个原因，我遇到过不只一次我服务的客户忘记了MySQL的root密码；如果是普通用户还好，我们可以用root用户去改它的密码，要命
的是把root给丢了！
对于MySQL来说如果你忘记了root密码，但是你又想通过改密码的方式把root密码找回来的话，你就要作好重启的准备了。
【方法一: --skip_grant_tables --skip-networking 两次重启】
1): 第一步把MySQL给关掉
ps -ef | grep mysql mysql 939 1 0 16:39 ? 00:00:02 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf kill -9 939
2): 以 --skip-grant-tables --skip-networking 模式启动 mysqld
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
3): 进入mysql
如果是mysql-5.7的话密码的 hash 值保存在了 authentication_string 这个列里面，5.7之前的版本保存在 password 列里面
select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *91B73478B18B04D13F6926FAB5A6178250EAB697 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | monitor | 127.0.0.1 | *DAD735712BB263A8DA12A091AABC625FE99DD344 | | root | 127.0.0.1 | *91B73478B18B04D13F6926FAB5A6178250EAB697 | | backup | 127.0.0.1 | *2139A3EF5FE5A0229BE550AD5ED2947B07F43B93 | | backup | localhost | *2139A3EF5FE5A0229BE550AD5ED2947B07F43B93 | | appuser | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------------+-----------+-------------------------------------------+ 8 rows in set (0.01 sec)
采用直接更新密码 hash 值的方式来更新密码
update mysql.user set authentication_string = password('MTls0352') where user='root';
-- 更新密码为MTls0352 Query OK, 2 rows affected, 1 warning (1.01 sec) Rows matched: 2 Changed: 2 Warnings: 1 select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *597B32612905C92ABC495354FC276D24D0A541C1 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *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/bin/mysqld --defaults-file=/etc/my.cnf &
5): 用新的密码进入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 connection id is 2 Server 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 clear the 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.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/bin/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 connection id is 3 Server 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 clear the 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 第一种方法比较传统，第二种方法“角度刁钻” ，看起来步骤少一些，但是一个线上的MySQL实例不是以 “/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &” 这样命令的方式启动的， 如果要用 service ,systemctl 来管理，“方法二”最终也是要两次重启， 使用“alter user”语法，不关心密码到底保存在mysql.user表中的那个列。
