MySql 忘记密码(Mac 端)

1.找到自己的 mysql 安装目录, 进入 bin/

➜  ~ cd /usr/local/Cellar/mysql@5.7/5.7.31/bin
➜  bin ls | grep "safe"
mysqld_safe

2. 进入安全模式

➜  bin mysqld_safe --skip-grant-tables
2020-10-28T03:35:24.6NZ mysqld_safe Logging to '/usr/local/var/mysql/dooo.err'.
2020-10-28T03:35:24.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql

3. 上面页面会暂停，直接将下列命令输入, control + z 结束输入, 即可使用 123456 进行登陆

mysql -u root
update mysql.user set authentication_string=PASSWORD('123456') where User ='root';
flush privileges;
\q

如图:

有可能出现的问题:

### 4. 进入安全模式修改密码成功后, 有时出现重启 mysql 服务后再次登陆时仍失败的问题

解决方式, 新建用户, 然后授予最高权限, 再使用该用户, 对 root 进行授权即可

刷新权限表
flush privileges;

创建用户 abc 并授权
create user 'abc'@'%';
grant all privileges on *.* to 'abc'@'%' identified by '123456';
grant all privileges on *.* to 'abc'@'localhost' identified by '123456' with grant option;

然后使用 abc 登陆, 登陆后再对 root 账号授权
grant all privileges on *.* to root@"%" identified by "123456";
grant all privileges on *.* to root@"localhost" identified by "123456" with grant option;

刷新权限表
flush privileges;

完成之后查询 user 表:
mysql> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| %         | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | abc           | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | abc           | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------+-------------------------------------------+
6 rows in set (0.00 sec)

问题: 为什么同时授予 ‘%’ 与 ‘localhost’ ?

因为版本问题, 有时 ‘%’ 不包括 ‘localhost’. 参考: MySQL用户中的%到底包不包括localhost?

并且同一版本的小版本之间也会有类似问题, 不具体探究了, 就直接全部授权省事.