MySQL forgot root password after modification

After MySQL forgets the root password, it can be modified using the following method.

1. Log on to the server where MySQL is located, manually kill the MySQL process

Kill ' Cat $mysql _data_dir/hostname.pid '

$mysql _data_dir /hostname.pid as the MySQL data directory, it records the process number of the MySQL service.

[[email protected] ~]# ps-ef |grep MySQL

Root 6602 1 0 21:39? 00:00:00/bin/sh./mysqld_safe--skip-grant-tables--user=mysql

MySQL 6754 6602 0 21:39? 00:00:01/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--plugin-dir=/usr/lib64/mysql/plugin--user=mysql --skip-grant-tables--log-error=/var/lib/mysql/rhel6.er

R--pid-file=/var/lib/mysql/rhel6.pidroot 6851 6830 0 21:47 pts/3 00:00:00 grep mysql

[Email protected] ~]# Cat/var/lib/mysql/rhel6.pid

6754

[[email protected] ~]# kill ' Cat/var/lib/mysql/rhel6.pid '

[[email protected] ~]# ps-ef |grep MySQL

Root 6859 6830 0 21:48 pts/3 00:00:00 grep mysql

2. Restart the MySQL service using the--skip-grant-tables option

--skip-grant-tables means skipping permission table authentication when starting the MySQL service. After booting, the root of the connection to MySQL will not require a password.

[Email protected] bin]#/mysqld_safe--skip_grant-tables--user=mysql &

[1] 6900

[Email protected] bin]# 161122 21:52:03 mysqld_safe Logging to '/var/lib/mysql/rhel6.err '.

161122 21:52:03 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql

[[email protected] bin]# ps-ef |grep MySQL

Root 6900 6830 0 21:52 pts/3 00:00:00/bin/sh./mysqld_safe--skip_grant-tables--user=mysql

MySQL 7052 6900 1 21:52 pts/3 00:00:00/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--plugin-dir=/usr /lib64/mysql/plugin--user=mysql--skip-grant-tables--log-error=/var/lib/mysql/rhel6.er

R--pid-file=/var/lib/mysql/rhel6.pidroot 7075 6830 0 21:52 pts/3 00:00:00 grep mysql

[Email protected] bin]# Mysql-uroot

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.6.34-log MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

3. Change the root password

Mysql> set Password=password (' 123456 ');

ERROR 1290 (HY000): The MySQL server is running with the--skip-grant-tables option so it cannot execute this statement

mysql> use MySQL;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

mysql> Update user Set Password=password (' 123456 ') where user= ' root ' and host= ' localhost ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

Because the--skip-grant-tables option was used to start, changing the password using the "Set Password" command failed to change the password successfully after updating the password field of the user table directly.

4. Refresh the Permissions table

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

5. When you re-login with root, you must enter a new password

[Email protected] bin]# Mysql-uroot

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

[Email protected] bin]# mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 4

Server Version:5.6.34-log MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

Reference: "In layman's MySQL"

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1875966

MySQL forgot root password after modification