Windows 重新设置Mysql密码

1. 任务

如果忘记了MySQL的root密码，需要重新设置。

2. 步骤

（1）停止MySQL服务

C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

（2）不启动grant-tables授权表下启动MySQL

C:\WINDOWS\system32>mysqld --defaults-file="D:\Software\mysql-5.7.31-winx64\my.ini" --console --skip-grant-tables

响应：

2021-03-03T06:00:59.254523Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-03-03T06:00:59.276819Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-03-03T06:00:59.277912Z 0 [Note] IPv6 is available.
2021-03-03T06:00:59.278245Z 0 [Note]   - '::' resolves to '::';
2021-03-03T06:00:59.278631Z 0 [Note] Server socket created on IP: '::'.
2021-03-03T06:00:59.497951Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210303 14:00:59
2021-03-03T06:01:01.028222Z 0 [Note] mysqld: ready for connections.

（3）另起一个cmd，修改MySQL密码

C:\WINDOWS\system32>mysql -u root -p

需要输入密码，直接Enter输入即可。

（4）设置新的密码

mysql> use mysql;
Database changed

mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';

（如果这步碰到错误：ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

则执行：

mysql> describe user;

会有表:

| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+

然后执行：

mysql> update user set authentication_string=password('123456') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

）

（5）退出MySQL

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.29 sec)

mysql> quit
Bye

（6）验证是否修改密码成功

C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

用新密码登录：

C:\WINDOWS\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31

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

体验Mysql的功能：

mysql> SELECT DATABASE();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

提示错误，需要我们再次修改密码：

mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

成功！

---

参考：

1. Windows下忘记MySQL root密码解决方法;
2. MySQL user DB does not have password columns - Installing MySQL on OSX
   ;
3. Reset MySQL root password using ALTER USER statement after install on Mac