[toc]

今天看到一篇文章，是测试 MySQL 8.0 修改密码并验证登录的文章。

其中有以下验证

image-20230930201407400

可以看到上面修改的是 root@'localhost' 用户的密码，而下面验证登录的是通过-h127.0.0.1 的方式登录。这很显然是有问题的，是不严谨的，为什么呢，因为在 MySQL 中 localhost 和 127.0.0.1 是有一点区别的。

下面说下我对 MySQL 中的localhost和 127.0.0.1 区别的理解。

当前数据库的用户信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> show grants for root@localhost\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.01 sec)

也就是目前数据库当中只有一个用户 root@localhost; 权限就是 all on *.* MySQL 8.0 通过 show grants for 看到的是一堆相信的权限信息，在之前的版本比如 5.7就是显示GRANT ALL PRIVILEGES ON *.* 

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.33    |
+-----------+
1 row in set (0.00 sec)

*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
2 rows in set (0.00 sec)

首先数据库中创建两个用户 root@localhost和[email protected] ，设置不同的密码

当然在安装完成 MySQL 8.0 之后会创建一个 root@localhost的用户，这里设置密码为rootlocal，创建 [email protected] 用户，密码设置为 root127 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> alter user root@localhost identified by 'rootlocal';
Query OK, 0 rows affected (0.02 sec)

mysql> create user root@127.0.0.1 identified by 'root127';
Query OK, 0 rows affected (0.02 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

验证 -h127.0.0.1 和 -hlocalhost 登录

-h127.0.0.1 登录

通过密码 root127 可以登录成功。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@liups bin]# ./mysql -uroot -proot127 -h127.0.0.1
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 5301
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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 current_user();
+----------------+
| current_user() |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)

-hlocalhost 登录

1
2
3
[root@liups bin]# ./mysql -uroot -prootlocal -hlocalhost
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)

提示ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) 也就是说他要通过 socket 来进行登录，但是默认的 /tmp/mysql.sock 不存在，次数据库的socket 在 /data/mysql/run/mysql.sock 因此登录失败。

1
2
3
4
5
6
7
mysql> show variables like 'socket';
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| socket        | /data/mysql/run/mysql.sock |
+---------------+----------------------------+
1 row in set (0.00 sec)

有两种方式解决：

通过-S 参数手动指定socket

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@liups bin]# ./mysql -uroot -prootlocal -hlocalhost -S /data/mysql/run/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 5306
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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 current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

手动创建软连

1
2
3
[root@liups bin]# ln -s  /data/mysql/run/mysql.sock /tmp/mysql.sock
[root@liups bin]# ls -l  /tmp/mysql.sock
lrwxrwxrwx 1 root root 26 Oct  6 22:45 /tmp/mysql.sock -> /data/mysql/run/mysql.sock

然后通过 -hlocalhost 登录成功。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@liups bin]#  ./mysql -uroot -prootlocal -hlocalhost
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 5394
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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 current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

可以看到不管是创建软连还是手动指定socket，current_user() 都是 root@localhost

检查-h127.0.01 和-hlocalhost 登录的连接方式

1
2
3
[root@liups bin]# ./mysql -uliups -h127.0.0.1 -ppassword -e'status'|grep Connection:
mysql: [Warning] Using a password on the command line interface can be insecure.
Connection:		127.0.0.1 via TCP/IP

可以看到 -h127.0.0.1 的连接方式是 ==TCP/IP==

1
2
3
[root@liups bin]# ./mysql -uroot -prootlocal -S /data/mysql/run/mysql.sock -e'status'|grep Connection:
mysql: [Warning] Using a password on the command line interface can be insecure.
Connection:		Localhost via UNIX socket

可以看到 -hlocalhost 的连接方式是 ==UNIX socket==

通过以上可以看到 -hlocalhost和-h127.0.0.1 的区别：

1、连接数据库的方式不一样： -h127.0.0.1 的连接方式是 ==TCP/IP==， -hlocalhost 的连接方式是 ==UNIX socket==

2、这是两个不同的用户，以上演示的操作过程这俩用户的密码都不一样，既然用户不一样，那肯定是可以给这两个用户授予不同的权限了，这里不再展示。

验证本环境修改密码并测试登录

那么在本次实验中通过如下修改密码，验证是否会成功呢。

image-20230930201407400 
1
2
3
4
5
6
7
8
mysql> alter user root@'localhost' identified by  'root1';
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
[root@liups bin]#  ./mysql -uroot -h127.0.0.1  -proot1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

本环境是无法登录的，那就来了灵魂拷问的一个问题，为什么他可以呢？

原文作者: liups.com

原文链接: http://www.liups.com/posts/e9b891be/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议