分析MySQL用户中的百分号%是否包含localhost?

198 阅读 0 评论 131 点赞

我是靠谱客的博主淡淡哈密瓜，这篇文章主要介绍分析MySQL用户中的百分号%是否包含localhost?，现在分享给大家，希望可以做个参考。

MySQL用户中的%到底包不包括localhost?

1 前言

操作MySQL的时候发现，有时只建了%的账号，可以通过localhost连接，有时候却不可以，网上搜索也找不到满意的答案，干脆手动测试一波

推荐学习：《mysql视频教程》

2 两种连接方法

这里说的两种连接方法指是执行mysql命令时，-h参数填的是localhost还是IP, 两种连接方式的区别如下

-h 参数为 localhost

当-h参数为localhost的时候，实际上是使用socket连接的（默认连接方式）, 实例如下

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
Enter password: 
========= 省略 ===========

mysql> status
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
Enter password: 
========= 省略 ===========

mysql> status
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
登录后复制

从Current user可以看到用户是xx@localhost, 连接方式为Localhost via UNIX socket

-h 参数为 IP

当-h参数为IP的时候，实际上是使用TCP连接的, 实例如下

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        11
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf81
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        11
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
登录后复制

从Current user可以看到用户是xx@127.0.0.1, 连接方式为TCP/IP

3 不同版本的差别

测试方法就是看能不能连接，如果不想看测试过程可以拉到最后看结论

3.1 MySQL 8.0

创建用户

复制代码mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

mysql> create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.07 sec)1
2
3
4
5
6
7
8
9
10
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

mysql> create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.07 sec)
登录后复制

使用 localhost 登录

复制代码[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -utest_user -p -hlocalhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql80/bin/mysql  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.11 MySQL Community Server - GPL
Protocol version:    10
Connection:        Localhost via UNIX socket
...1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -utest_user -p -hlocalhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql80/bin/mysql  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.11 MySQL Community Server - GPL
Protocol version:    10
Connection:        Localhost via UNIX socket
...
登录后复制

使用 IP 登录

复制代码[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql80/bin/mysql  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:        8
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.11 MySQL Community Server - GPL
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql80/bin/mysql  Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:        8
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.11 MySQL Community Server - GPL
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
登录后复制

结果显示8.0版本的MySQL, % 包括localhost

3.2 MySQL 5.7

创建 % 用户

复制代码db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)1
2
db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)
登录后复制

使用 localhost 登录

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
========= 省略 ===========

mysql> status
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
....1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
========= 省略 ===========

mysql> status
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        9
Current database:    
Current user:        test_user@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
....
登录后复制

使用 IP 登录

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        11
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
...1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        11
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
...
登录后复制

结果显示5.7版本的MySQL, % 包括localhost

3.3 MySQL 5.6

创建用户

复制代码db83-3306>>select version();
+------------+
| version()  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)1
2
3
4
5
6
7
8
9
10
db83-3306>>select version();
+------------+
| version()  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)
登录后复制

使用 localhost 登录

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)1
2
3
[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -hlocalhost
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)
登录后复制

使用 IP 登录

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        3
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.10-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
......
--------------1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)
========= 省略 ===========

mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:        3
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.10-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
......
--------------
登录后复制

结果显示MySQL 5.6的%不包括localhost

3.4 MySQL 5.1

创建用户

复制代码mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

mysql> create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)1
2
3
4
5
6
7
8
9
10
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

mysql> create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)
登录后复制

使用 localhost 登录

复制代码[root@chengqm ~]# mysql -utest_user -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)1
2
3
[root@chengqm ~]# mysql -utest_user -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)
登录后复制

使用 IP 登录

复制代码[root@chengqm ~]# mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4901339
Server version: 5.1.73 Source distribution
========= 省略 ===========

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:        4901339
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.1.73 Source distribution
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@chengqm ~]# mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4901339
Server version: 5.1.73 Source distribution
========= 省略 ===========

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:        4901339
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.1.73 Source distribution
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
登录后复制

结果显示 5.1 版本的%不包括localhost

3.5 MariaDB 10.3

创建用户

复制代码db83-3306>>select version();
+---------------------+
| version()           |
+---------------------+
| 10.3.11-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.001 sec)1
2
3
4
5
6
7
8
9
10
db83-3306>>select version();
+---------------------+
| version()           |
+---------------------+
| 10.3.11-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)

db83-3306>>create user test_user@'%' identified by 'test_user';
Query OK, 0 rows affected (0.001 sec)
登录后复制

使用 localhost 登录

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mariadb/bin/mysql -utest_user -p -hlocalhost
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)1
2
3
[mysql@mysql-test-83 ~]$ /usr/local/mariadb/bin/mysql -utest_user -p -hlocalhost
Enter password: 
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)
登录后复制

使用 IP 登录

复制代码[mysql@mysql-test-83 ~]$ /usr/local/mariadb/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.3.11-MariaDB-log MariaDB Server
========= 省略 ===========

MariaDB [(none)]> status
--------------
/usr/local/mariadb/bin/mysql  Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:        12
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.3.11-MariaDB-log MariaDB Server
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[mysql@mysql-test-83 ~]$ /usr/local/mariadb/bin/mysql -utest_user -p -h127.0.0.1
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.3.11-MariaDB-log MariaDB Server
========= 省略 ===========

MariaDB [(none)]> status
--------------
/usr/local/mariadb/bin/mysql  Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:        12
Current database:    
Current user:        test_user@127.0.0.1
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.3.11-MariaDB-log MariaDB Server
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
登录后复制

结果显示MariaDB 10.3的%不包括localhost

4 结论

版本	用户中的`%`是否包括`localhost`
MySQL8.0	包括
MySQL5.7	包括
MySQL5.6	不包括
MySQL5.1	不包括
MariaDB 10.3	不包括

以上就是分析MySQL用户中的百分号%是否包含localhost?的详细内容，更多请关注靠谱客其它相关文章！

最后

以上就是淡淡哈密瓜最近收集整理的关于分析MySQL用户中的百分号%是否包含localhost?的全部内容，更多相关分析MySQL用户中内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：mysql
浏览次数：198 次浏览
发布日期：2022-10-31 23:22:01
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujo_18_f2_14__7__18_5.html

分析MySQL用户中的百分号%是否包含localhost?

1 前言

2 两种连接方法

-h 参数为 localhost

-h 参数为 IP

3 不同版本的差别

3.1 MySQL 8.0

创建用户

使用 localhost 登录

使用 IP 登录

3.2 MySQL 5.7

创建 % 用户

使用 localhost 登录

使用 IP 登录

3.3 MySQL 5.6

创建用户

使用 localhost 登录

使用 IP 登录

3.4 MySQL 5.1

创建用户

使用 localhost 登录

使用 IP 登录

3.5 MariaDB 10.3

创建用户

使用 localhost 登录

使用 IP 登录

4 结论

最后

评论列表共有 0 条评论

发表评论取消回复

分析MySQL用户中的百分号%是否包含localhost?

1 前言

2 两种连接方法

-h 参数为 localhost

-h 参数为 IP

3 不同版本的差别

3.1 MySQL 8.0

创建用户

使用 localhost 登录

使用 IP 登录

3.2 MySQL 5.7

创建 % 用户

使用 localhost 登录

使用 IP 登录

3.3 MySQL 5.6

创建用户

使用 localhost 登录

使用 IP 登录

3.4 MySQL 5.1

创建用户

使用 localhost 登录

使用 IP 登录

3.5 MariaDB 10.3

创建用户

使用 localhost 登录

使用 IP 登录

4 结论

最后

微信扫一扫：分享

相关文章

评论列表共有 0 条评论

发表评论 取消回复

微信扫一扫：分享

发表评论取消回复