解决Mariadb10.1.37不需要密码就可以登录

发布于 2020-04-05  450 次阅读


在 Ubuntu 下 apt 装了 Mariadb 后,改掉 root 密码后,发现不需要密码就可以进入控制台。
刚开始以为密码修改失败了。后来发现是 Mariadb 认证插件的原因。

官方解释:https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

mysql 库 user 表中默认有一条 root 用户记录,这条记录 plugin 字段(用户认证)默认值是 unix_socket(在 centos7 yum 安装的为 mariadb-server-5.5.60 版本,默认这个字段是空的),如果是这种认证方式,则通过系统凭据认证,就跳过数据库的密码认证了。
所以出现不需要密码就可以登录。

# 改回数据库密码认证

root@simon-PC:/etc/mysql# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user,plugin from mysql.user;
+------+-------------+
| user | plugin      |
+------+-------------+
| root | unix_socket |
+------+-------------+
1 rows in set (0.00 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set plugin = 'mysql_native_password' where user = 'root';
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select user,plugin from mysql.user;
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 rows in set (0.00 sec)

MariaDB [mysql]> exit
Bye

# 重启 MariaDB 数据库

root@simon-PC:/etc/mysql# systemctl restart mysql
root@simon-PC:/etc/mysql# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

上面提示没有带密码,拒绝登录了。

root@simon-PC:/etc/mysql# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 

完成。


或许明日太阳西下倦鸟已归时