MySQL – Can’t set / reset password for root user properly (CentOS)

MySQL

I have installed MySQL 5.7.2 in CentOS 6.9. In other systems(Debian, Arch) when I installed mysql it prompted for root password at the installation process but in this case it didn't prompted for a password. So I tried to log in to mysql shell using blank password. But mysql reported Access Denied message.

Then I tried to set new password in mysql safe mode.

$ service mysqld stop
$ mysql_safe --skip-grant-tables &
$ mysql -u root
mysql > USE mysql;
mysql > UPDATE user SET authentication_string = PASSWORD('mypass') WHERE User='root';
mysql > FLUSH PRIVILEGES;

(Some people instructed to use password column. But I didn't find this column in user table. So I used authentication_string column)

It worked perfectly. I can log in as root in mysql shell using my new password.

Then I installed phpmyadmin. When I tried to log in to phpmyadmin with my mysql credentials it reported following error:

1862 – Your password has expired. To log in you must change it using a client that supports expired passwords.

mysqli_real_connect(): (HY000/1862): Your password has expired. To log in you must change it using a client that supports expired passwords.

To solve this I logged in to mysql shell and tried this:

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

It reported following error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

So I tried following in the mysql shell

mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypass';

But It reported following error:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Then I have tried to reset my password using mysql_secure_installation. It also reporting same kind of error:

… Failed! Error: Your password does not satisfy the current policy requirements

Now I am stuck. What is the problem? How can I solve this issue? Thanks in advance!

Best Answer

Your password does not satisfy the current policy requirements.

Assuming that the validate_password plugin is installed, it implements three levels of password checking: LOW, MEDIUM, and STRONG. The default is MEDIUM;

Medium is defined as:

MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character.

Use a password that satisfies these requirements, set validate_password_policy to LOW or disable the password validation plugin.