mysql和mariadb无密码可登录解决

网上大部分说查看并修改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("要设置的密码")