Linux MySQL root password forgotten solution

When using the MySQL database, if you have not logged into MySQL for a long time due to some reasons, or if the work handover is not completed well, you may forget the database root login password. How can you solve this problem?

1. Change the my.cnf configuration file

1. Edit the /etc/my.cnf configuration file using the command: vim /etc/my.cnf or vi /etc/my.cnf or nano /etc/my.cnf

2. Add skip-grant-tables under [mysqld], then save and exit

3. Restart the mysql service: service mysqld restart

2. Change the root username

1. After restarting, execute the mysql command to enter the mysql command line

2. Change the root user password

MySQL> UPDATE mysql.user SET Password=PASSWORD('new password') where USER='root';
MySQL> flush privileges;
MySQL> exit

Note: The above is for versions before 5.7. Versions after 5.7 do not have the Password field, and the password field is changed to authentication_string

mysql> update mysql.user set authentication_string=password('root123456') where user='root'; #Password changed successfully
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; #Effective immediately
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

n>mysql -u ******* -p #Login successfully with this user.
Enter password: ********
…………………………
mysql>

Note: For versions later than 5.7, the password cannot be too simple, such as 123456. Otherwise, the following error will be reported: ERROR 1819 (HY000): Your password does not satisfy the current policy requirements.

At this point, either set the password to be more complex or modify the configuration:

This is actually related to the value of validate_password_policy.

validate_password_policy has the following values:

The default value is 1, which means MEDIUM, so the password you set at the beginning must meet the length and must contain numbers, lowercase or uppercase letters, and special characters.
Sometimes, just for my own testing, I don’t want to set a complicated password. For example, I just want to set the root password to 123456.
Two global parameters must be modified:

First, modify the value of the validate_password_policy parameter mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
1
2
The validate_password_length parameter defaults to 8, which we change to 1

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
1
2
4. After completion, execute the password change statement again to succeed mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

3. Finally, comment out skip-grant-tables in /etc/my.cnf, and then restart mysql, that is: service mysqld restart

OK, now we can use the new root password to log in to MySQL.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.