2021-05-19

mysql 8.0 重置root密码

问题描述：

装了MySQL，一段时间没用，忘记密码了：
想重置一下。
bing搜了一下，
大致步骤都是：
1.修改配置文件
vim /etc/my.inf
在mysqld下面加入
skip-grant-tables
然后重启mysqld 服务
service mysqld restart
2.修改密码
输入mysql无密码登录
use mysql

update user set authentication_string=password("Test#128") where user='root';

mysql> update user set authentication_string=password(‘testroot’) where user=‘root’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(‘testroot’) where user=‘root’’ at line 1
结果出现上面的错误提示，这里很奇怪，网上很多文章都是这么操作的，我全部复制（只修改密码）还是报错

使用alter修改也报错

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Test#128';

mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘Test#128’;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

---

解决方案：

[root@mars mysql]# vim /etc/my.cnf #增加skip-grant-tables
[root@mars mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@mars mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set authentication_string=password('tesT#123') where user ='root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Aiwei@123') where user ='root'' at line 1
#这里不知道为什么update一直失败
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'test#123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'tesT#123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@mars mysql]# vim /etc/my.cnf #注释skip-grant-tables
[root@mars mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@mars mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>