Mysql 密码忘记如何修改密码

Linux服务器，把mysql数据库密码忘记了。

[root@VM_0_13_centos ~]# mysql -uroot -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
密码忘了登陆不了MySQL了。

1、首先停止mysql服务进程：

service mysqld stop 

然后编辑mysql的配置文件my.cnf（目的是跳过密码验证）

 vim /etc/my.cnf 

在最后面添加一段代码

 skip-grant-tables   ##忽略mysql权限问题，直接登录

然后保存 :wq!退出

2.重启service mysqld start 服务进程

[root@VM_0_13_centos my.cnf.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22 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 database
 

使用mysql表，然后进行修改mysql的root密码：

mysql> use mysql; ##使用mysql数据库
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 mysql> update user set password=password("123456") where user="root";##更新密码（）
 7 Query OK, 4 rows affected (0.00 sec)
 8 Rows matched: 4  Changed: 4  Warnings: 0
 9 
10 mysql> flush privileges;##刷新权限
11 Query OK, 0 rows affected (0.00 sec)

 

注意：我在更新的时候发现user表提示没有password 这一栏，原来是我这个版本这个栏位更新成新名字：authentication_string

update user set authentication_string=password("123456")  where user="root"
    -> ;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 1
 

3.切回配置文件中将添加的那一行删除就可以了。vim /etc/my.cnf

4.重启数据库服务。

[root@VM_0_13_centos etc]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 

此时，可以发现已经不能登录了。

 

[root@VM_0_13_centos etc]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.22 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>