MySQL 5.7Forgetting root password reset record

Summary: MySQL 5.7Setting root password method update mysql.user set authentication_string=PASSWORD ('* *') where user='root' and host='localhost'

Operating system:

[root@localhost ~] # cat / etc/redhat-release

CentOS Linux release 7.1.1503

MysQL version 5.7

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.9 |

+-+

1 row in set (0.00 sec)

Install the yum repo source for MySQL:

[root@localhost ~] # rpm-ivh http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm

Install MySQL

[root@localhost ~] # yum-y install mysql-community-server mysql-community-client

Modify mysql configuration file to skip password verification

[root@localhost ~] # vim / etc/my.cnf

[mysqld]

Skip-grant-tables

Skip-networking

Restart MySQL:

[root@localhost ~] # systemctl mysqld restart

Anonymous login to MySQL to set the root password:

[root@localhost] # mysql-uroot-p

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

Your MySQL connection id is 2

Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 > update mysql.user set PASSWORD=PASSWORD ('test') where user='root' and host='localhost'

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

Reminder: since 5. 7, authentication_string no longer uses the password field to store passwords, so there is an error.

Mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' test'

ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

View the mysql.user password field:

Mysql > desc mysql.user

Mysql > update mysql.user set authentication_string=PASSWORD ('test') where user='root' and host='localhost'

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

Rows matched: 1 Changed: 1 Warnings: 1

Mysql > flush privileges

Query OK, 0 rows affected (0.06 sec)

Mysql > select host,user,password from mysql.user

Mysql > exit

Bye

Cancel Skip password Authentication:

[root@localhost ~] # vim / etc/my.cnf

[mysqld]

# skip-grant-tables

# skip-networking

Restart MySQL:

[root@localhost ~] # systemctl mysqld restart

You must use alter user to reset the password:

Mysql > show databases

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

Reset the new password:

Mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' *'

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Password requires complexity requirements:

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

+-+ +

6 rows in set (0.00 sec)

Mysql > set password=password ('xxxx')

Query OK, 0 rows affected (0.00 sec)