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

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

在操作MySQL时，我们经常会遇到一个问题：有时只创建了%的账号，可以通过localhost连接，有时候却不可以，这让人很是困惑。网上搜索也找不到满意的答案，因此，本文就来详细分析一下MySQL用户中的百分号%是否包含localhost。

1. 两种连接方式

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

-h参数为localhost

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

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -u test_user -p -h localhost

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可以看到用户是test_user@localhost，连接方式为Localhost via UNIX socket。

-h参数为IP

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

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysql -u test_user -p -h 127.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可以看到用户是test_user@127.0.0.1，连接方式为TCP/IP。

2. 不同版本的差别

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

2.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)

登录后，复制使用localhost登录

[root@mysql-test-72 ~]# /usr/local/mysql80/bin/mysql -u test_user -p -h localhost

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 -u test_user -p -h 127.0.0.1

Enter password:

=========省略===========

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

Server characterset: utf8

...

登录后，复制结果显示8.0版本的MySQL，%包括localhost。

2.2 MySQL 5.7创建%用户

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 -u test_user -p -h localhost

=========省略===========

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 -u test_user -p -h 127.0.0.1

Enter password:

=========省略===========

mysql> status

--------------

/usr/local/mysql57/bin/mysql Ver 14.14 Distrib

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。