mysql5.7密碼忘記解決方法

ENV：

[[email protected] ~]# uname -r
3.10.0-514.el7.x86_64
[[email protected] ~]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core) 
[[email protected] ~]# rpm -qa mysql
[[email protected] ~]# rpm -qa |grep mysql
mysql-community-common-5.7.26-1.el7.x86_64
mysql-community-client-5.7.26-1.el7.x86_64
mysql57-community-release-el7-11.noarch
mysql-community-server-5.7.26-1.el7.x86_64
mysql-community-libs-5.7.26-1.el7.x86_64
mysql-community-libs-compat-5.7.26-1.el7.x86_64
           

登陸時出錯：

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

密碼忘記了（和剛安裝後不知道密碼一樣）

網上有人說mysql的密碼是空密碼，其實在mysql5.7版本之後，密碼不再是空密碼了，

如果是剛安裝的，可以在mysql的日志檔案找到

grep 'temporary password' /var/log/mysqld.log
           

補充：如果找到mysql提供的密碼，可以使用

mysqladmin -u root -p 'mysql提供的密碼' password ‘自己的新密碼’ 

直接修改mysql的密碼，但這種方法存在安全隐患，畢竟密碼在指令行上顯示了，不建議但不反對。

如果是忘記，修改如下：

1.修改 /etc/my.cnf,加入 skip-grant-tables;

[[email protected] ~]# vim /etc/my.cnf
           

空白位置加入，并儲存退出；

[mysqld]
          
skip-name-resolve
skip-grant-tables
           

[[email protected] ~]# systemctl restart mysqld
           

2.空密碼直接進入mysql；

[[email protected] ~]# mysql -u root -p
Enter password: (這裡是空密碼，直接回車)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 
           

進入mysql庫；

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
           

這裡的mysql并不是沒變，資料庫位置是變化的；

3.修改密碼：UPDATE user SET authentication_string=PASSWORD('newpassword') where USER='root';

mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') where USER='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>
           

4改回/etc/my.cnf

注釋掉 ＃skip-grant-tables

[[email protected] ~]# vim /etc/my.cnf
           

[mysqld]
          
skip-name-resolve
#skip-grant-tables
           

[[email protected] ~]# systemctl restart mysqld
           

5.用新的密碼再進入mysql；

[[email protected] ~]# mysql -u root -p 
Enter password: (之前示範為newpassword)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26

Copyright (c) 2000, 2019, 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>
           

6.更改root密碼，更改root密碼： alter user 'root'@'localhost' identified by '密碼';

修改使用者密碼；

ALTER USER testuser IDENTIFIED BY '123456';
           

修改目前登入使用者

ALTER USER USER() IDENTIFIED BY '123456';
           

mysql> alter user user() identified by 'Linuxpassword!@#';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user user() identified by 'LINUX123password!@#';
Query OK, 0 rows affected (0.00 sec)

mysql> 
           

可以看出，密碼的複雜度有了很大的要求；

7.修改完成後就可以繼續操作mysql了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye