Mysql5.7忘记密码找回

找回Mysql密码

当前版本为5.7.18，突然忘记密码只能将密码重置。

[xin@xin ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'liuxin'@'localhost' (using password: NO)

1、先停止Mysql服务，和进程

service mysqld stop

2、打开Mysql主配置文件，追加skip-grant-tables内容如下：

[xin@xin ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
socket=/usr/local/mysql/mysql.sock
log_error=/usr/local/mysql/data/mysql.err
skip-grant-tables

3、然后启动Mysql，用空密码的登录方式使用root登录。

# 启动mysql
service mysqld start
​
# 登录mysql
[xin@xin ~]# mysql  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 3  
Server version: 5.7.18 Source distribution  
  
  
Copyright (c) 2000, 2017, 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.

4、修改密码

MySQL> update MySQL.user set password=PASSWORD(‘新密码’) where User=’root’;
# 刷新表
MySQL>flush privileges;

这时候我们发现它报错了，后来才发现原来是mysql5.7数据库下已经没有password这个字段了，password字段改成了authentication_string这时候我们用authentication_string修改密码如下：

mysql> update mysql.user set authentication_string=password(‘123456′) where user=’root’;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
​
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询当前所有的Mysql用户

mysql> select Host,user from mysql.user;
+-----------+---------------+
| Host      | user          |
+-----------+---------------+
| localhost | root          |
| sirliu    | root          |
| 127.0.0.1 | root          |
| ::1       | root          |
| localhost |               |
| sirliu    |               |
| localhost | mysql.session |
| localhost | mysql.sys     |
| %         | liuxx         |
+-----------+---------------+

Mysql8.0 重置root密码

• 修改配置文件

  修改配置文件： vim /etc/my.cnf
  [mysqld] 下 添加skip-grant-tables 保存退出
  重启mysql服务： systemctl restart mysqld.service

• 重置密码为空

  登陆mysql: mysql -u root;
  选择数据库: use mysql;
  update user set authentication_string = '' where user ='root';
  exit/quit;(两者都是退出的意思，任选其一即可)

  
  

• 还原配置文件

  修改mysql配置文件： my.cnf
  删除步骤1添加的 skip-grant-tables 保存退出
  重启服务： systemctl restart mysqld.service

• 进入mysql修改密码

  登陆mysql: mysql -u root; # 密码为空 直接回车
  修改密码：ALTER USER 'root'@'%' IDENTIFIED BY 'yourpassword';
  刷新配置：flush privileges;