Rootop 服务器运维与web架构

在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)]> 

完成。