MySql8.x 修改密码

1、常用方案（不适合8.x） 1.1 修改配置

修改配置文件/etc/my.cnf

增加如下内容：

# 无密码登录
skip-grant-tables

1.2重启mysql服务

systemctl restart mysqld

1.3 修改mysql密码

在输入密码时，直接回车。

# mysql -u root -p
Enter password:   <直接回车>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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> 

修改update user set password=PASSWORD('123456') where user='root'

mysql> use mysql
mysql> update user set password=PASSWORD('123456') 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 '('123456') where user='root'' at line 1
mysql>

出现了语法错误之后，查看user表中字段。发现表中没有password 字段，所以update user set password 会出错，查找资料发现mysql 8.0.2之后数据库Mysql修改密码，密码规则发生变化，改变了user表。

2、解决方案（适合8.x）

恢复配置文件/etc/my.cnf默认配置，然后重启mysqld服务

（1）通过/var/log/mysqld.log查询密码

在mysqld.log中查询以下记录：

A temporary password is generated for root@localhost:XXXXXXXX

2022-10-11T07:22:24.592233Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: T7w4:nawmL(m

（2）登录mysql

（3）修改mysql密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql#135';
Query OK, 0 rows affected (0.02 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> 

成功

（编辑：鹰潭站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!