Disable password less authentication and set a root pass on MySQL 5.7

950 total views, 4 views today

Login to the MySQL using below command

# mysql -uroot

use the mysql database

mysql> use mysql;
mysql> SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

Change the password policy to LOW

mysql> SET GLOBAL validate_password_policy=LOW;

Now again show the password policy

mysql> SHOW VARIABLES LIKE 'validate_password%';

Your MySQL password policy has been changed. Now you have to set a root password.

To show MySQL current password structure: 

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Change the root password authentication method to native_password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Again show the MySQL current password structure, then you will see what is changed:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

You are all done!!

Leave a Reply

Your email address will not be published. Required fields are marked *