Modify MySQL 5.7.9 version of the root password method and some new changes to organize

MySQL version 5.7, the enhanced password authentication mechanism, the network said when installed in the/root/.mysql_secret file will generate a default password, which has been removed since the 5.7.6 version.

For if a default password is generated, there is a version on the web that reads as follows:

1, modify the configuration file, generally in/etc/my.cnf, in the [Mysqld] field under the Skip-grant-tables field, to ignore the permission validation;

2. Restart the MySQL server, the CentOS 7.0 Operation command is:

1	systemctl restart mysqld.service

3, use mysql-u root-p login data, password directly enter;

4, modify the MySQL database (name), the User table authentication_string field, modify the command as follows:

1 2 3

mysql> update mysql.user setauthentication_string=password(‘new_password‘) where user=‘root‘and Host =‘localhost‘;mysql> flush privileges;mysql> quit;

5, modify the configuration file, remove Skip-grant-tables, revert to the original appearance, and then re-MySQL server.

6. Use the password set in step 4th to log in to the MySQL server.

7, through set Password=password (' New_password '); command to reset the password;

The above method, in the early version of MySQL 5.7, is still very useful, but after the MySQL 5.7.6 version, the last point of resetting the password is always unable to take effect, will come out the following error.

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

At the same time, other operations cannot be performed.

This is because, starting with MySQL 5.7, the password has an expiration date concept, and because of security level issues, it needs to be set up in other ways. Another point to note is that since 5.7.6 began to discard the password () function and cannot set the old_password=1, this is really a more tortuous process of exploration.

Well, first of all, the MySQL 5.7.9 above the password setting method to tidy up.

Most of the content is the same as the above, the key is after the 4th step, write the value written in authentication_string (this is because the password () function is obsolete)

Use the following command instead of the 7th step, set Password=password (' New_password ') method.

1 2 3 4

mysql > alter user  ' Jeffrey ' @ ' localhost '      ->  Identified with mysql_native_password      ->  as  ' authentication_string field contents ' query ok , 0 rows affected  (0.01 sec)

In fact, the above command has a simplified version, the command is as follows

mysql> alter user user ()  identified by  ' News_password ' ;   error 1819  (HY000): your password does  Not satisfy the current policy requirements " TD class= "Code" >

1 Span style= "font-family:"microsoft yahei"; font-size:14px ">2 3

The above error message appears, which is related to the security level of the password, and check the security level command as follows

1 2 3 4 5 6 7 8 9 10 11

mysql> SHOW VARIABLES LIKE ‘validate_password%‘;+--------------------------------------+--------+| Variable_name                        | Value  |+--------------------------------------+--------+| 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      |+--------------------------------------+--------+

Via SET GLOBAL validate_password_policy= ' low '; command, after lowering the security level, can be used directly, the limit is must be 8 characters or more;

A more detailed description of the safety level is given below

• LOW The policy only tests the password length. The password must be at least 8 characters in length.

• MEDIUM Policy conditions the password must contain at least 1 numeric characters, 1 uppercase and lowercase characters, and 1 special (nonalphanumeric) characters.

• STRONG Policy situation the password substring length of 4 or longer does not match the word in the dictionary file if a person is specified.

Finally, to resolve the problem of password invalidation, add the following fields under the [Mysqld] field of the configuration file.

1 2	[mysqld]default_password_lifetime=0

You can also set it from the command line

1	ALTER USER ‘script‘@‘localhost‘PASSWORD EXPIRE NEVER

For more information about password expiration, refer to http://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

Well, hopefully the information above will help you all.

Modify MySQL 5.7.9 version of the root password method and some new changes to organize