mysql8.0以上修改密码-干货版

踩了好多坑，操蛋的
修改mysql8.0密码

1.修改my.cnf 配置文件，跳过认证

systemctl stop mysql
vim /etc/mysql/my.cnf
#[mysqld]
#skip-grant-tables

1
2
3
4
5

2.使用空密码进入mysql

root@zabbix-server01:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.31-0ubuntu0.20.04.1 (Ubuntu)
1
2
3
4

切换到Mysql库，先清空root密码；也就是mysql库里user表中的authentication_string字段。然后可以使用查询语句查看字段结果

mysql> use mysql;
Database changed

mysql> update 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.01 sec)

mysql> select host, user, authentication_string, plugin from mysql.user;'
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             |                                                                        | mysql_native_password |
| localhost | debian-sys-maint | $A$005$"6snYEqNAIOhS7 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBIxATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISxNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$0sEVERBRBEUSED | caching_sha2_password |
| localhost | zabbix           | $A$005$>iSQ^@fF=U;c+dcyRupGe5e98ZkMak7Wu9gb/S6V8ca3LgXyY2ZE47C71 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.01 sec)

由此可见 root目前是空密码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

修改密码为root，并重新查询表中字段信息。此时密码字段authentication_string不再是空，并且是密文显示

mysql> alter user'root'@'%' IDENTIFIED BY '1qaz!@#$%';
Query OK, 0 rows affected (0.01 sec)

mysql> select host, user, authentication_string, plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *5DCB816934A07666C3904E315BBC9ACC60868D1D                              | mysql_native_password |
| localhost | debian-sys-maint | $A$00sOhS7 | caching_sha2_password |
| localhost | mysql.infoschema | $AsUSED | caching_sha2_password |
| localhost | mysql.session    | $A$sUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$0sUSED | caching_sha2_password |
| localhost | zabbix           | $A$0s7C71 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)

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

mysql> quit;
Bye
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.删掉my.cnf跳过认证，启动mysql，使用修改后的密码进行登录。

root@zabbix-server01:~# systemctl restart mysql.service
root@zabbix-server01:~# systemctl daemon-reload
root@zabbix-server01:~# systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2023-03-08 16:20:52 CST; 20s ago
   Main PID: 1340756 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 11854)
     Memory: 368.0M
     CGroup: /system.slice/mysql.service
             └─1340756 /usr/sbin/mysqld


root@zabbix-server01:~# mysql -u root -p
Enter password:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql数据库更新成功

修改root认证模式为 mysql_native_password

alter user query identified with mysql_native_password by '密码'
1

https://blog.csdn.net/dbdd_cf/article/details/93734336?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522168593354116800213094857%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=168593354116800213094857&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduend~default-2-93734336-null-null.142^v88control_2,239^v2insert_chatgpt&utm_term=mysql%20%E8%BF%9C%E7%A8%8B&spm=1018.2226.3001.4187