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 © 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 © 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 © 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 © 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文件登录的;