MySQL新建用户中的%到底包不包括localhost?
正常解释
%代表任何客户机都可以连接
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)usingEditLinewrapperConnectionid: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)usingEditLinewrapperConnectionid: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)usingEditLinewrapperConnectionid: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)usingEditLinewrapperConnectionid: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)usingEditLinewrapperConnectionid: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.1Connectionid: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.1Connectionid: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 | 不包括 |
好了，这篇文章就介绍到了，希望大家以后多多支持毛票票。