In MySQL 5.7, how do I modify the root password ?, Mysqlroot

In MySQL 5.7, how do I modify the root password ?, Mysqlroot

For a long time, MySQL's application and learning environments are both MySQL 5.6 and earlier versions, and they have not paid attention to the changes and new features of the new version of MySQL 5.7. Today, when a group of people forgot the root password, they found that the previous method did not work. The details are as follows:

The scenario is as follows:

Operating System: Red Hat Enterprise Linux Server release 6.6 (Santiago)

Database Version: 5.7.18 MySQL Community Server (GPL)

If you forget the password, the following error message is returned when you enter the wrong password:

 

[root@mytestlnx02 ~]# mysql -u root -p

Enter password: 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@mytestlnx02 ~]#

Check whether the MySQL service is started. If yes, disable the MySQL service.

 

[root@mytestlnx02 ~]# ps -ef | grep -i mysql

root     22972     1  0 14:18 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql    23166 22972  0 14:18 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

root     23237 21825  0 14:22 pts/0    00:00:00 grep -i mysql

[root@mytestlnx02 ~]# service mysqld stop

Stopping mysqld:  [  OK  ]

[root@mytestlnx02 ~]# 

 

 

Find the MySQL my. cnf configuration file and add the following information in/etc/my. cnf (some versions are/etc/mysql/my. cnf:

[Mysqld]

Skip-grant-tables

Start MySQL, enter MySQL, and change the root password. During the operation, ERROR 1054 (42S22): Unknown column 'Password' in 'field list' is encountered ', after checking the table structure of the user table, we found that the user table has no Password field in MySQL 5.7. The encrypted user password is stored in the authentication_string field. The procedure is as follows:

 

[root@mytestlnx02 ~]# service mysqld start

Starting mysqld:  [  OK  ]

[root@mytestlnx02 ~]# mysql -u root 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> 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> update user set password=PASSWORD('Kd8k&dfdl023')

    -> where user='root';

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql> update mysql.user set authentication_string=password('Kd8k&dfdl023') where user='root';

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Comment out or delete the added line "skip-grant-tables" in the my. cnf file. After the MySQL service is restarted, run the command set password = password ('newpassword.

[root@mytestlnx02 ~]# service mysqld start

Starting mysqld:  [  OK  ]

[root@mytestlnx02 ~]# mysql -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.18

Copyright (c) 2000, 2017, 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> use mysql;

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

mysql> set password=password('Kd8k&dfdl023');

Query OK, 0 rows affected, 1 warning (0.00 sec)

Later I checked the relevant information and found that MySQL 5.7 has some new security features (refer to MySQL 5.7 new feature serialization (III ))

1. the plugin field of mysql. user cannot be blank. The default value is mysql_native_password instead of mysql_old_password. The old password format is no longer supported;

2. added the Password Expiration mechanism. You need to change the password after expiration. Otherwise, the password may be disabled or enter the sandbox mode. Whether to enable the password expiration is controlled by the default_password_lifetime parameter.

 

mysql> show variables like 'default_password_lifetime';

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| default_password_lifetime | 0     |

+---------------------------+-------+

1 row in set (0.00 sec)

mysql>

3: added the password security level and Password Complexity settings. The parameters are as follows:

mysql> show variables like 'validate_password%';

+--------------------------------------+--------+

| Variable_name                        | Value  |

+--------------------------------------+--------+

| validate_password_check_user_name    | OFF    |

| 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      |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

 

 

4. when mysql_install_db is used for initialization, a random password is automatically generated by default. The random password is stored in/var/log/mysqld. log, and do not create except root @ localhost and mysql. other accounts outside sys @ localhost do not create the test database;

[root@mytestlnx02 mysql]# yum localinstall mysql-community-{server,client,common,libs}-* 

[root@mytestlnx02 mysql]# rpm -qa | grep -i mysql

mysql-community-client-5.7.18-1.el6.i686

mysql-community-libs-5.7.18-1.el6.i686

perl-DBD-MySQL-4.013-3.el6.x86_64

mysql-community-server-5.7.18-1.el6.i686

mysql-community-common-5.7.18-1.el6.i686

mysql-community-libs-compat-5.7.18-1.el6.i686

[root@mytestlnx02 mysql]# service mysqld start

Initializing MySQL database:  [  OK  ]

Installing validate password plugin:  [  OK  ]

Starting mysqld:  [  OK  ]

[root@mytestlnx02 mysql]# 

[root@mytestlnx02 mysql]# grep 'temporary password' /var/log/mysqld.log

2017-05-05T06:10:57.802143Z 1 [Note] A temporary password is generated for root@localhost: w99s(m-q_ML:

mysql> select user ,host from user;

+-----------+-----------+

| user      | host      |

+-----------+-----------+

| mysql.sys | localhost |

| root      | localhost |

+-----------+-----------+

2 rows in set (0.00 sec)

References:

Http://imysql.com/2015/07/08/mysql-57-new-feature-part-3.shtml