他们说，最好的密码是不需要记住的密码。 对于MySQL，这要归功于auth_socket插件及其适用于MariaDB的版本-unix_socket 。
这两个插件都不是新手，例如，在同一个博客中已对它们进行了很多讨论，例如，有关如何使用auth_socket插件在MySQL 5.7中更改密码的文章中。 但是，分析MariaDB 10.4的新增功能后，我发现unix_socket现在默认安装，并且是身份验证方法之一（“其中之一”，因为在MariaDB 10.4中，一个用户可以使用多个插件进行身份验证，在文档中对此进行了解释。 MariaDB 10.04中的“身份验证” ）。
正如我所说，这不是新闻，当您使用Debian团队支持的.deb软件包安装MySQL时，将创建具有root权限的用户以通过套接字进行身份验证。 对于MySQL和MariaDB都是如此。
root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org> Original-Maintainer: Debian MySQL Maintainers <<a href="mailto:pkg-mysql-maint@lists.alioth.debian.org">pkg-mysql-maint@lists.alioth.debian.org</a>>
使用用于MySQL的Debian软件包，root用户可以按照以下方式进行身份验证：
root@app:~# whoami root= root@app:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root'; +------+-----------+-------------+-----------------------+ | user | host | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket | | +------+-----------+-------------+-----------------------+ 1 row in set (0.01 sec)
MariaDB的.deb包也是如此：
10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 MariaDB [(none)]> show grants; +------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
官方Percona存储库中的.deb软件包还为auth-socket和Percona Server配置了根身份验证。 这是针对MySQL 8.0.16-7和Ubuntu 16.04的Percona Server的示例：
root@app:~# whoami root root@app:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312' Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='root'; +------+-----------+-------------+-----------------------+ | user | host | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket | | +------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec)
那么什么是魔术呢？ 该插件使用SO_PEERCRED套接字选项验证Linux用户是否与MySQL用户匹配-收集有关运行客户端程序的用户的信息。 因此，该插件只能在支持SO_PEERCRED选项的系统上使用，例如Linux。 SO_PEERCRED套接字选项使您可以识别与套接字相关的进程的uid。 之后，他已经获得与此uid关联的用户名。
这是用户“无业游民”的示例：
vagrant@mysql1:~$ whoami vagrant vagrant@mysql1:~$ mysql ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'
由于MySQL中没有无业游民的用户，因此我们被拒绝访问。 创建这样的用户，然后重试：
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.00 sec) vagrant@mysql1:~$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 45 Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 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)]> show grants; +---------------------------------------------------------------------------------+ | Grants for vagrant@localhost | +---------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
原来！
那么，非Debian发行版默认情况下不提供该发行版呢？ 让我们尝试在Percenta Server for MySQL 8上安装在CentOS 7上：
mysql> show variables like '%version%comment'; +-----------------+---------------------------------------------------+ | Variable_name | Value | +-----------------+---------------------------------------------------+ | version_comment | Percona Server (GPL), Release 7, Revision 613e312 | +-----------------+---------------------------------------------------+ 1 row in set (0.01 sec) mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded
mm 缺少了什么？ 插件未加载：
mysql> pager grep socket PAGER set to 'grep socket' mysql> show plugins; 47 rows in set (0.00 sec)
向该过程添加一个插件：
mysql> nopager PAGER set to stdout mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; Query OK, 0 rows affected (0.00 sec) mysql> pager grep socket; show plugins; PAGER set to 'grep socket' | auth_socket | ACTIVE | AUTHENTICATION | auth_socket.so | GPL | 48 rows in set (0.00 sec)
现在我们有了所需的一切。 让我们再尝试一次：
mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost'; Query OK, 0 rows affected (0.01 sec)
现在，您可以使用登录名“ percona”登录。
[percona@ip-192-168-1-111 ~]$ whoami percona [percona@ip-192-168-1-111 ~]$ mysql -upercona Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona'; +---------+-----------+-------------+-----------------------+ | user | host | plugin | authentication_string | +---------+-----------+-------------+-----------------------+ | percona | localhost | auth_socket | | +---------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec)
它再次起作用！
问题：是否可以使用相同的percona登录名登录，但使用其他用户登录？
[percona@ip-192-168-1-111 ~]$ logout [root@ip-192-168-1-111 ~]# mysql -upercona ERROR 1698 (28000): Access denied for user 'percona'@'localhost'
不，它不会解决。
结论
MySQL在多个方面都非常灵活，其中之一就是身份验证方法。 从这篇文章可以看出，基于操作系统的用户，无需密码即可获得访问权限。 这在某些情况下很有用，其中之一是当您从RDS / Aurora迁移到常规MySQL时，使用IAM数据库身份验证仍然可以访问，但没有密码。