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

2023-08-28 23:03:05 209

正常解释

%代表任何客户机都可以连接
localhost代表只可以本机连接

一般情况能访问本地数据库的都是加了权限了，一般都是禁止别的机器访问本地的mysql端口的，如果允许也是要加上指定ip才可以访问，这样才能保证数据库不会被远程访问。

1前言

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

2两种连接方法

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

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

[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-hlocalhost
Enterpassword:
=========省略===========

mysql>status
/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapper

Connectionid:9
Currentdatabase:
Currentuser:test_user@localhost
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.7.21-logMySQLCommunityServer(GPL)
Protocolversion:10
Connection:LocalhostviaUNIXsocket

从Currentuser可以看到用户是xx@localhost,连接方式为LocalhostviaUNIXsocket

-h参数为IP

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

[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-h127.0.0.1
Enterpassword:
=========省略===========

mysql>status
--------------
/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapper

Connectionid:11
Currentdatabase:
Currentuser:test_user@127.0.0.1
SSL:CipherinuseisDHE-RSA-AES256-SHA
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.7.21-logMySQLCommunityServer(GPL)
Protocolversion:10
Connection:127.0.0.1viaTCP/IP
Servercharacterset:utf8

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

3不同版本的差别

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

3.1MySQL8.0

创建用户

mysql>selectversion();
+-----------+
|version()|
+-----------+
|8.0.11|
+-----------+
1rowinset(0.00sec)

mysql>createusertest_user@'%'identifiedby'test_user';
QueryOK,0rowsaffected(0.07sec)
使用localhost登录
[root@mysql-test-72~]#/usr/local/mysql80/bin/mysql-utest_user-p-hlocalhost
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis9
Serverversion:8.0.11MySQLCommunityServer-GPL
=========省略===========

mysql>status
--------------
/usr/local/mysql80/bin/mysqlVer8.0.11forlinux-glibc2.12onx86_64(MySQLCommunityServer-GPL)

Connectionid:9
Currentdatabase:
Currentuser:test_user@localhost
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:8.0.11MySQLCommunityServer-GPL
Protocolversion:10
Connection:LocalhostviaUNIXsocket
...

使用IP登录

[root@mysql-test-72~]#/usr/local/mysql80/bin/mysql-utest_user-p-h127.0.0.1
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis8
Serverversion:8.0.11MySQLCommunityServer-GPL
=========省略===========

mysql>status
--------------
/usr/local/mysql80/bin/mysqlVer8.0.11forlinux-glibc2.12onx86_64(MySQLCommunityServer-GPL)

Connectionid:8
Currentdatabase:
Currentuser:test_user@127.0.0.1
SSL:CipherinuseisDHE-RSA-AES128-GCM-SHA256
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:8.0.11MySQLCommunityServer-GPL
Protocolversion:10
Connection:127.0.0.1viaTCP/IP

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

3.2MySQL5.7

创建%用户

db83-3306>>createusertest_user@'%'identifiedby'test_user';
QueryOK,0rowsaffected(0.00sec)

使用localhost登录

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

mysql>status
/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapper

Connectionid:9
Currentdatabase:
Currentuser:test_user@localhost
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.7.21-logMySQLCommunityServer(GPL)
Protocolversion:10
Connection:LocalhostviaUNIXsocket
....

使用IP登录

[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-h127.0.0.1
Enterpassword:
=========省略===========

mysql>status
--------------
/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapper

Connectionid:11
Currentdatabase:
Currentuser:test_user@127.0.0.1
SSL:CipherinuseisDHE-RSA-AES256-SHA
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.7.21-logMySQLCommunityServer(GPL)
Protocolversion:10
Connection:127.0.0.1viaTCP/IP
Servercharacterset:utf8
...

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

3.3MySQL5.6

创建用户

db83-3306>>selectversion();
+------------+
|version()|
+------------+
|5.6.10-log|
+------------+
1rowinset(0.00sec)

db83-3306>>createusertest_user@'%'identifiedby'test_user';
QueryOK,0rowsaffected(0.00sec)

使用localhost登录

[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-hlocalhost
Enterpassword:
ERROR1045(28000):Accessdeniedforuser'test_user'@'localhost'(usingpassword:YES)

使用IP登录

[mysql@mysql-test-83~]$/usr/local/mysql57/bin/mysql-utest_user-p-h127.0.0.1
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis3
Serverversion:5.6.10-logMySQLCommunityServer(GPL)
=========省略===========

mysql>status
--------------
/usr/local/mysql57/bin/mysqlVer14.14Distrib5.7.21,forlinux-glibc2.12(x86_64)usingEditLinewrapper

Connectionid:3
Currentdatabase:
Currentuser:test_user@127.0.0.1
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.6.10-logMySQLCommunityServer(GPL)
Protocolversion:10
Connection:127.0.0.1viaTCP/IP
......
--------------

结果显示MySQL5.6的%不包括localhost

3.4MySQL5.1

创建用户

mysql>selectversion();
+-----------+
|version()|
+-----------+
|5.1.73|
+-----------+
1rowinset(0.00sec)

mysql>createusertest_user@'%'identifiedby'test_user';
QueryOK,0rowsaffected(0.00sec)

使用localhost登录

[root@chengqm~]#mysql-utest_user-p
Enterpassword:
ERROR1045(28000):Accessdeniedforuser'test_user'@'localhost'(usingpassword:YES)
使用IP登录
[root@chengqm~]#mysql-utest_user-p-h127.0.0.1
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis4901339
Serverversion:5.1.73Sourcedistribution
=========省略===========

mysql>status
--------------
mysqlVer14.14Distrib5.1.73,forredhat-linux-gnu(x86_64)usingreadline5.1

Connectionid:4901339
Currentdatabase:
Currentuser:test_user@127.0.0.1
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.1.73Sourcedistribution
Protocolversion:10
Connection:127.0.0.1viaTCP/IP

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

3.5MariaDB10.3

创建用户

db83-3306>>selectversion();
+---------------------+
|version()|
+---------------------+
|10.3.11-MariaDB-log|
+---------------------+
1rowinset(0.000sec)

db83-3306>>createusertest_user@'%'identifiedby'test_user';
QueryOK,0rowsaffected(0.001sec)

使用localhost登录

[mysql@mysql-test-83~]$/usr/local/mariadb/bin/mysql-utest_user-p-hlocalhost
Enterpassword:
ERROR1045(28000):Accessdeniedforuser'test_user'@'localhost'(usingpassword:YES)

使用IP登录

[mysql@mysql-test-83~]$/usr/local/mariadb/bin/mysql-utest_user-p-h127.0.0.1
Enterpassword:
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMariaDBconnectionidis12
Serverversion:10.3.11-MariaDB-logMariaDBServer
=========省略===========

MariaDB[(none)]>status
--------------
/usr/local/mariadb/bin/mysqlVer15.1Distrib10.3.11-MariaDB,forLinux(x86_64)usingreadline5.1

Connectionid:12
Currentdatabase:
Currentuser:test_user@127.0.0.1
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Server:MariaDB
Serverversion:10.3.11-MariaDB-logMariaDBServer
Protocolversion:10
Connection:127.0.0.1viaTCP/IP

结果显示MariaDB10.3的%不包括localhost

4结论

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

好了，这篇文章就介绍到了，希望大家以后多多支持毛票票。

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

1前言

2两种连接方法

-h参数为IP

3不同版本的差别

3.1MySQL8.0

3.2MySQL5.7

3.3MySQL5.6

3.4MySQL5.1

3.5MariaDB10.3

4结论

热门推荐

随机推荐