Detailed explanation of mysql5.7 password forgetting solution

ENV:

[root@centos7 ~]# uname -r
3.10.0-514.el7.x86_64
[root@centos7 ~]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core) 
[root@centos7 ~]# rpm -qa mysql
[root@centos7 ~]# 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

Error logging in:

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

I forgot my password (just like I didn't know my password after installation)

Some people on the Internet say that the password of mysql is an empty password. In fact, after mysql version 5.7, the password is no longer an empty password.

If it is newly installed, it can be found in the log file of mysql

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

Add: If you find the password provided by mysql, you can use

mysqladmin-u root-p 'Password supplied by mysql' password 'New Password of Your Own'

Modify the password of mysql directly, but this method has security risks. After all, the password is displayed on the command line, which is not recommended but not opposed.

If it is forgotten, modify it as follows:

1. Amend/etc/my. cnf by adding skip-grant-tables;

[root@centos7 ~]# vim /etc/my.cnf

Add blank space and save exit;

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

[root@centos7 ~]# systemctl restart mysqld

2. The empty password directly enters mysql;;

[root@centos7 ~]# mysql -u root -p
Enter password: ( Here is an empty password, enter directly )
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>

Enter the mysql library;

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>

The mysql here is not unchanged, the database location is changed;

3. Change the password: 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 Change back to/etc/my. cnf

Comment out # skip-grant-tables

[root@centos7 ~]# vim /etc/my.cnf

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

0

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

1

5. Re-enter mysql with a new password;

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

2

6. Change root password, change root password: alter user 'root' @ 'localhost' identified by 'password';

Modify the user password;

ALTER USER testuser IDENTIFIED BY '123456';

Modify the currently logged-in user

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

4

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

5

It can be seen that the complexity of passwords has great requirements;

7. You can continue to operate mysql after the modification is completed

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

6