MySQL修改root密码

最新推荐文章于 2025-11-17 22:08:16 发布

原创最新推荐文章于 2025-11-17 22:08:16 发布 · 2.1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文详细介绍了在MySQL 5.7版本中修改root密码的四种方法，包括mysqladmin、ALTER USER、UPDATE mysql.user以及SET PASSWORD命令，并特别指出在MySQL 8.0之后第三种方法已失效。此外，还提供了在忘记root密码时如何通过编辑my.cnf文件实现免密登录并重置密码的步骤。

部署运行你感兴趣的模型镜像

MySQL修改root密码

方法一
方法二
方法三（此方法在MySQL8.0后失效，后期具体更改root密码查看官网的官方文档）
方法四
遗忘root密码

下方全部演示均是在不同虚拟机完成

方法一

[root@localhost ~]# mysqladmin -uroot -p"*+/.j9AB).SR" password		//冒号中为原始密码
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
New password: 		//输入新密码，此处写的是MySql@123
Confirm new password: 		//确认新密码（再次输入新密码），此处写的是MySql@123
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

//测试登录
[root@localhost ~]# mysql -uroot -pMySql@123	//一般不建议此种写法，容易泄露密码，此处仅为了演示
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37 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> 
//登录成功

方法二

mysql> ALTER USER root@localhost identified by 'MySql@123';		//进入MySQL后进行配置，末尾“MySql@123”为密码
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

//测试登录
[root@localhost ~]# mysql -uroot -pMySql@123		//一般不建议此种写法，容易泄露密码，此处仅为了演示
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.37 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> 
//登录成功

方法三（此方法在MySQL8.0后失效，后期具体更改root密码查看官网的官方文档）

mysql> select user,host,authentication_string from mysql.user;		//查看用户密码对应
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6311658AA054458F319CF5563D0E486799BB2871 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> update mysql.user set authentication_string=password('MySql@123') 
    -> where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 1
//通过update命令更新密码

mysql> flush privileges;	//刷新权限，这一步极其重要，不刷新权限无法登录
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

//测试登录
[root@localhost ~]# mysql -uroot -pMySql@123		//一般不建议此种写法，容易泄露密码，此处仅为了演示
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 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> 
//登录成功

方法四

mysql> set password for root@localhost = password('MySql@123');		//进入MySQL后进行配置，末尾“MySql@123”为密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

//测试登录
[root@localhost ~]# mysql -uroot -pMySql@123		//一般不建议此种写法，容易泄露密码，此处仅为了演示
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.37 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> 
//登录成功

遗忘root密码

[root@localhost ~]# systemctl stop mysqld		//关闭MySQL服务
[root@localhost ~]# vim /etc/my.cnf		//修改MySQL主配置文件

[root@localhost ~]# cat /etc/my.cnf		//查看MySQL主配置文件修改内容（往[mysqld]末尾添加skip-grant-tables语句，进行免密登录）
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid 

skip-grant-tables		//末尾加上此条语句，进行免密登录MySQL

[root@localhost ~]# systemctl start mysqld		//重新启动MySQL服务

[root@localhost ~]# mysql -uroot		//免密登录
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37 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 mysql.user set authentication_string=password('MySql@123')
    -> where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;	//记得刷新权限，不然无法登录
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

//测试登录
[root@localhost ~]# mysql -uroot -pMySql@123		//一般不建议此种写法，容易泄露密码，此处仅为了演示
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.37 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>
//登录成功

//注：最后记得把主配置文件中的免密登录语句删掉，事先关闭MySQL服务，删掉语句后再重启MySQL服务