MySQL空用户导致登录失败

1.MySQL报错如下

# mysql -uzabbix -p --socket=/data/mysql/mysql3306/mysql.sock
Enter password: 
ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: YES)

从登录报错来看是密码不对,实际上密码是正确的；

2.root登录查看

root可以正常登录

# mysql -uroot -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> select user,host from mysql.user;
+----------------------------+-----------+
| user                       | host      |
+----------------------------+-----------+
|                            | %         |
| mysql_innodb_cluster_103   | %         |
| mysql_router2_s0d7idkh2avj | %         |
| repl                       | %         |
| routertest                 | %         |
| zabbix                     | %         |
|                            | localhost |
| mysql.infoschema           | localhost |
| mysql.session              | localhost |
| mysql.sys                  | localhost |
| root                       | localhost |
+----------------------------+-----------+
11 rows in set (0.00 sec)

说明：
1.查看到存在用户zabbix@'%'，说明socket和远程登录都应该可以登录成功，但是现在登录报错；
2.查看到存在user为空的两个用户 ''@'%' 和 ''@'localhost' ，怀疑是user为空的用户引起的；

3.删除user为空用户

# mysql -uroot -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> drop user ''@'localhost';
mysql> drop user ''@'%';

--zabbix登录
# mysql -uzabbix -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| zabbix@%       |
+----------------+
1 row in set (0.01 sec)

zabbix用户登录成功；

4.分别创建两个空用户验证

创建’’@’%'用户验证

# mysql -uroot -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> create user ''@'%' identified by '123';

--zabbix登录
# mysql -uzabbix -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| zabbix@%       |
+----------------+
1 row in set (0.00 sec)

zabbix用户登录成功

创建’’@'localhost’用户验证

# mysql -uroot -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> drop user ''@'%';
mysql> create user ''@'localhost' identified by '123';

--zabbix登录
# mysql -uzabbix -p --socket=/data/mysql/mysql3306/mysql.sock
Enter password: 
ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: YES)

zabbix用户socket方式登录失败，说明是''@'localhost'用户导致zabbix本地socket登录失败

5.指定IP方式登录验证

5.1 -h127.0.0.1方式登录

参数 skip_name_resolve=OFF

# mysql -uzabbix -p -h127.0.0.1
Enter password: 
ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: YES)

说明：
zabbix用户-h127.0.0.1方式登录失败，这个是因为 127.0.0.1被反解析为localhost导致和socket方式登录相同的报错。

参数 skip_name_resolve=ON

# mysql -uzabbix -p -h127.0.0.1
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| zabbix@%       |
+----------------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          33
Current database:
Current user:           zabbix@127.0.0.1
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.31 MySQL Community Server - GPL
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 31 sec

Threads: 12  Questions: 1073  Slow queries: 0  Opens: 244  Flush tables: 3  Open tables: 161  Queries per second avg: 34.612
--------------

使用TCP/IP方式登录成功

5.2. 指定物理IP登录

# mysql -uzabbix -p -h172.20.23.4
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| zabbix@%       |
+----------------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          54
Current database:
Current user:           zabbix@172.20.23.4
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.31 MySQL Community Server - GPL
Protocol version:       10
Connection:             172.20.23.4 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 2 min 14 sec

Threads: 12  Questions: 1333  Slow queries: 0  Opens: 311  Flush tables: 3  Open tables: 228  Queries per second avg: 9.947
--------------

指定物理IP使用TCP/IP方式登录成功

6.修改用户’’@‘localhost’的密码为’zabbix’@’%'用户的密码

# mysql -uroot -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> alter user ''@'localhost' identified by 'zabbix';

--zabbix登录验证
# mysql -uzabbix -p --socket=/data/mysql/mysql3306/mysql.sock
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          248
Current database:
Current user:           zabbix@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.31 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /data/mysql/mysql3306/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 20 min 33 sec

Threads: 12  Questions: 4088  Slow queries: 0  Opens: 466  Flush tables: 3  Open tables: 383  Queries per second avg: 3.315
--------------

登录成功，但是查看current_user()用户是 @localhost，说明是匹配的mysql.user中的''@'localhost'用户

6.登录报错总结

1.MySQL登录时，首先验证mysql.user表中的host列，从而匹配了host='localhost'的主机
2.通过host='localhosot'的主机去找user='zabbix'的用户不存在，但是''空用户可以匹配任何用户，所以匹配到''@'localhost'的用户
3.用户''@'localhost'的密码和用户'zabbix'@'%'用户的密码不一样所以登录报错

7.其他说明

1.删除root@'localhost'用户并创建root@'%'用户，使用-uroot --socket=/data/mysql/mysql3306/mysql.sock登录同样会去匹配 ''@'localhost' 用户从而登录报错
2.创建zabbix@'localhost'用户后，使用-uzabbix --socket=/data/mysql/mysql3306/mysql.sock登录成功