mysql使用者本地登入localhost和127.0.0.1區別

今天某個透過ansible給某個機器安裝上mysql_exporter，然後無法採集到mysql的資訊，登入到mysql伺服器，發現是自動建立的zabbix_monitor@127.0.0.1賬號，無法登入，然後做了如下的測試：

mysql -uzabbix_monitor -p'**********' -h127.0.0.1 ------無法登入

mysql -uzabbix_monitor -p'******' -S /tmp1/mysql.sock-----無法登入

mysql -uroot -p'1234' ------可以登入

root可以登入上，我當時就認為埠是預設的3306，然後就沒從這方面想，最後確認是埠3307的問題!

一、MySQL本地登入方式：

方式1：指定ip+埠

mysql -uzabbix_monitor -p'rwg*******Y!' -h127.0.0.1 -P3307

方式2：透過sock

mysql -uzabbix_monitor -p'rwg*******Y!' -S /tmp1/mysql.sock

二、localhost和127.0.0.1在msyql5.7中：

mysql只有zabbix_monitor@localhost這類的賬號可以使用sock檔案登入，zabbix_monitor@127.0.0.1這類的不可以透過本地sock檔案登入;

結論1：針對zabbix_monitor@127.0.0.1這個賬號，本地只可以可以透過 方式1登入

方式1登入正常：

[root@localhost ~]# mysql -uzabbix_monitor -p'**********' -h127.0.0.1 -P3307

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 596

Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

透過方式2報錯：

mysql> show variables like 'socket';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| socket | /tmp/mysql.sock |

+---------------+-----------------+

1 row in set (0.00 sec)

mysql> exit

Bye

[root@localhost ~]# mysql -uzabbix_monitor -p'**********' -S /tmp/mysql.sock

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'zabbix_monitor'@'localhost' (using password: YES)

You have new mail in /var/spool/mail/root

結論2：針對liuwenhe@localhost這類的賬號，可以透過sock檔案的方式登入，也可以透過ip+port的方式登入

mysql> select user ,host from mysql.user where user='liuwenhe';

+----------+-----------+

| user | host |

+----------+-----------+

| liuwenhe | localhost |

+----------+-----------+

1 row in set (0.00 sec)

方式1可以登入：

[root@testdb1:/root]# mysql -uliuwenhe -p'*********' -hlocalhost -P3306

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 73

Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

方式2 可以登入：

[root@testdb1:/root]# mysql -uliuwenhe -p'********' -S /data/mysql/data/mysql.sock

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 76

Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

注意：如果你在配置檔案中[client]下配置了socket，那麼它會去你指定的路徑下找，如果你沒有配置，它預設會找/tmp/mysql.sock這個sock檔案;

如果你的sock檔案不在預設的/tmp下，並且配置檔案也沒有指定真實的sock路徑;那麼此時登入就會報錯：

[root@testdb1:/root]# mysql -uliuwenhe -p'*********'

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

如果你的sock檔案不在預設的/tmp下，你可以在配置檔案配置好sock檔案的真實路徑!如下所示：

cat /etc/my.cnf

[client]

port = 3307

socket = /data/mysql/data/mysql.sock

然後如下所示就可以登入了：

[root@testdb1:/root]# mysql -uliuwenhe -p'**********'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 82

Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

總結：

1、針對liuwenhe@localhost這類的賬號，可以透過sock檔案的方式登入，也可以透過ip+port的方式登入;

2、針對zabbix_monitor@127.0.0.1這個賬號，本地只可以可以透過 方式1sock檔案登入;

3、mysql -uroot -p可以登入，不能說明其埠是3306，因為它可能是透過本地sock檔案登入的;