重置MySQL数据库root密码

安装MySQL数据库：

# 安装软件
[root@localhost ~]# yum -y install mysql-server mysql
# 查看版本
[root@localhost ~]# mysql --version
mysql  Ver 8.0.32 for Linux on x86_64 (Source distribution)
# 开启 mysql 服务
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# netstat -utnlp | grep 3306
tcp6       0      0 :::33060                :::*                    LISTEN      17847/mysqld        
tcp6       0      0 :::3306                 :::*                    LISTEN      17847/mysqld 
# 设置密码
[root@localhost ~]# mysqladmin -hlocalhost -uroot -p password "123456"
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
# 登录数据库
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye

当我们忘记MySQL数据库的root密码时，我们可以通过配置文件去重置root密码：

[root@localhost ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
skip-grant-tables  #手动添加
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

# 重启服务
[root@localhost ~]# systemctl restart mysqld

# 无密码登录
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, 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="" where user = "root"; //修改密码
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> exit  // 断开链接
Bye

[root@localhost ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
#skip-grant-tables # 注释掉，或者删掉
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

# 重启服务
[root@localhost ~]# systemctl restart mysqld

# 重置密码
[root@localhost ~]# 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.32 Source distribution

Copyright (c) 2000, 2023, 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> alter user root@"localhost" identified by "xiantan";
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye