MySQL 5.7 detailed tutorial on modifying the password after forgetting the root password, mysqlroot

Source: Internet
Author: User

MySQL 5.7 detailed tutorial on modifying the password after forgetting the root password, mysqlroot

Preface

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 -pEnter 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 mysqlroot  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=mysqlmysql 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.sockroot  23237 21825 0 14:22 pts/0 00:00:00 grep -i mysql[root@mytestlnx02 ~]# service mysqld stopStopping 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.ERROR 1054 (42S22): Unknown column 'password' in 'field list'Check the table structure of the user table and find 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 startStarting mysqld: [ OK ][root@mytestlnx02 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. 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 namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> 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. Then, restart the MySQL service and run the following command:set password=password('newpassword');Then, the problem is fixed.

[root@mytestlnx02 ~]# service mysqld startStarting mysqld: [ OK ][root@mytestlnx02 ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. 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)

After querying the relevant information, we found that MySQL 5.7 has some new security features.

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 mysqlmysql-community-client-5.7.18-1.el6.i686mysql-community-libs-5.7.18-1.el6.i686perl-DBD-MySQL-4.013-3.el6.x86_64mysql-community-server-5.7.18-1.el6.i686mysql-community-common-5.7.18-1.el6.i686mysql-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.log2017-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)

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.