mysql5.7忘记密码无法登录macos

vlambda
2022-02-24

mysql5.7忘记密码无法登录macos

1.mysql无法访问

$ mysqlERROR 1045 (28000): Access denied for user 'xxx'@'localhost' (using password: NO)

2.修改配置文件跳过验证码

$ vim /etc/my.cnf在最后一行加入skip-grant-tables

3.修改完重启mysql

$ mysql.server restartShutting down MySQL.. SUCCESS!

4.再次尝试登录mysql

$ mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.37 Homebrew
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

5.切换到mysql库

mysql> show databases;mysql> use mysql;mysql> show tables;

6.更改root用户密码

mysql> select * from user;mysql> update user set Password=password('123456') where User='root';ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

我安装的是mysql5.7,原来是mysql数据库下已经没有password这个字段了，password字段改成了authentication_string

mysql> select host,user,authentication_string from user;mysql> update user set authentication_string=password('123456') where User='root';Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select authentication_string from user;

7.恢复配置文件

$ vim /etc/my.cnf最后一行删除skip-grant-tables

8.重启mysql

9.重新登录mysql

$ mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.37 Homebrew
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>