网上大部分说查看并修改mysql.user表中plugin字段中类型。
SELECT host,user,plugin FROM mysql.user;
+———–+————-+———————–+
| Host | User | plugin |
+———–+————-+———————–+
| localhost | mariadb.sys | mysql_native_password |
| localhost | root | mysql_native_password |
| localhost | mysql | mysql_native_password |
| % | admin | mysql_native_password |
+———–+————-+———————–+
4 rows in set (0.001 sec)
但在msyql8和mariadb10以后mysql.user 表已不起作用了，真正的表是 mysql.global_priv 表，查询后我们发现root用户是unix_socker类型，这就解释了为啥总能直接登陆的问题
SELECT * FROM mysql.global_priv;
+———–+————-+——————————————————————————————————————————————————————————+
| Host | User | Priv |
+———–+————-+——————————————————————————————————————————————————————————+
| localhost | mariadb.sys | {“access”:0,”plugin”:”mysql_native_password”,”authentication_string”:””,”account_locked”:true,”password_last_changed”:0} |
| localhost | root | {“access”:1073741823,”plugin”:”unix_socker”,”authentication_string”:”9BB091BAC7B5D8BA7D97E9F27E9DA6BD8F672AA0″,”auth_or”:[{}],”password_last_changed”:1646905522} | | localhost | mysql | {“access”:1073741823,”plugin”:”mysql_native_password”,”authentication_string”:”invalid”,”auth_or”:[{},{“plugin”:”unix_socket”}]} | | % | admin | {“access”:0,”plugin”:”mysql_native_password”,”authentication_string”:”A8434702DCF014A5059FBCDC14A055CDE2D4540E”,”password_last_changed”:1646905696} |
+———–+————-+——————————————————————————————————————————————————————————+
4 rows in set (0.000 sec)
而解决方法很简单，只要输入如下命令即可
ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD(“要设置的密码”)